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
- 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!
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.
excellent advice. thanks Jim!!