Using LINQ to query Outlook emails joined with SQL data

I watched part 2 of Jim Wooley’s ASP.NET Podcast show on LINQ and was really impressed with the creativity of his examples. Having dug deeply in order to write LINQ in Action along with Fabrice Marguerie and Steve Eichert, he’s way past the how-to basics and able to see the bigger picture of leveraging LINQ.

In his demo, he starts with some simple querying of the file system – a good demonstration of using linq against objects, but by the time he gets to the end of the demo, he is using JOIN to build queries that combine file system info with data pulled from the database.

I knew I wanted to do something like that but I couldn’t just copy him, no matter how flattering. So I thought about it for a while… what data is on my computer that I might want to extend with some database data? Then I thought of Outlook.

Thankfully, John Goalby had already written some posts on querying Outlook data with LINQ. So I was well on my way!

I created a few new email accounts for some employees of companies in AdventureWorksLT and sent emails to myself with their accounts. Then I created contact records for them in Outlook in my own account, making sure that I typed in the company names to match the database. Now I had some test data.

First I tested out a query where I joined MailItems from my inbox with ContactItems from my contact. (Note that I did this in VB since John’s examples are in C#, so this gives a little more sample code for people to discover.)

Dim ol As Outlook._Application = New Outlook.Application
Dim inbox = ol.ActiveExplorer().Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)

Dim contactfolder As Outlook.MAPIFolder = ol.ActiveExplorer.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts)

Dim emails = 
From email In inbox.Items.OfType(Of Outlook.MailItem)() Select email
Dim contacts = From contact In contactfolder.Items.OfType(Of Outlook.ContactItem)() Select contact

Dim
emailswithcompany = From email In emails Join contact In contacts _
    On email.SenderEmailAddress Equals contact.Email1Address _
    Select email, contact.CompanyName

For Each emailwithco In emailswithcompany
    Debug.Print(String.Format(“{0} from {1}: {2}”, _
       emailwithco.email.SenderName, emailwithco.CompanyName, emailwithco.email.Subject))
Next

This worked fine. I got a list of the sender and subject from the emails and company names from the contact records.

  • Katherine Harding from Sharp Bikes: Order 40 Shifters
  • John Harding from Sharp Bikes: modification to recent order
  • Keith Harris from Progressive Sports: vendor appreciation party

Then I queried the database and did a JOIN with the above results. It was funny to see how the types and subtypes kept growing as I built this up in layers. It’s nice to have things organized, but if I were starting from scratch, I might do this a bit differently so that my resulting types aren’t so complex*.

Dim awdc As New awlinqDataContext
Dim custSalesPerson = From cust In awdc.AWCustomers Select cust.CompanyName, cust.SalesPerson
Dim emailswithcompanysp = From emailco In emailswithcompany _
   Join cust In custSalesPerson _
   On cust.CompanyName Equals emailco.CompanyName _
   Select emailco, cust.SalesPerson

For Each emailwithcosalesp In emailswithcompanysp 
  Debug.Print(String.Format(“{0} from {1}: {2}” & NewLine & “SalesPerson:{3}”, _
    emailwithcosalesp.emailco.email.SenderName, _
    emailwithcosalesp.emailco.CompanyName, _
    emailwithcosalesp.emailco.email.Subject, _
    emailwithcosalesp.SalesPerson))
Next

And voila!

  • Katherine Harding from Sharp Bikes: Order 40 Shifters
    SalesPerson:adventure-works\josé1
  • John Harding from Sharp Bikes: modification to recent order
    SalesPerson:adventure-works\josé1
  • Keith Harris from Progressive Sports: vendor appreciation party
    SalesPerson:adventure-works\david8

Now I could write an app that can distribute email to the correct sales people when they come into a general mail box! Well, I supposed I could have done it prior to having LINQ (or maybe in Exchange which I know nothing about), just with a lot more effort!

*I couldn’t resist streamlining the final solution.

Dim ol As Outlook._Application = New Outlook.Application
Dim inbox = ol.ActiveExplorer().Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)
Dim contactfolder As Outlook.MAPIFolder = ol.ActiveExplorer.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts)
Dim emails = From email In inbox.Items.OfType(Of Outlook.MailItem)() Select email
Dim contacts = From contact In contactfolder.Items.OfType(Of Outlook.ContactItem)() Select contact
Dim awdc As New awlinqDataContext
Dim custSalesPerson = From cust In awdc.AWCustomers Select cust.CompanyName, cust.SalesPerson

‘now query across emails, contacts and custSalesPerson in one query

Dim emailcontactsp = From email In emails Join contact In contacts _
  On email.SenderEmailAddress Equals contact.Email1Address _
  Join custsalesp In custSalesPerson On contact.CompanyName Equals custsalesp.CompanyName _
  Select email, contact.CompanyName, custsalesp.SalesPerson

For Each ecsp In emailcontactsp
Debug.Print(String.Format(“{0} from {1}: {2}” & NewLine & “SalesPerson:{3}”, _
   ecsp.email.SenderName, _
   ecsp.CompanyName, _
   ecsp.email.Subject, _
   ecsp.SalesPerson))
Next

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

2 thoughts on “Using LINQ to query Outlook emails joined with SQL data

  1. One thing to watch out for with heterogeneous joins. The entire join is performed on the client rather than on the server. Thus all records from your table will be returned and a LINQ to Objects filter will be applied on them. As an alternative, you may want to pre fetch the client portions and pass them to the server in an IN clause by using the new Contains extension method that came with Beta 2. See devauthority.com/…/69922.aspx for a sample joining an ASP.NET CheckedListBox.Selected items with the database for an example.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.