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.ApplicationDim 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 emailDim contacts = From contact In contactfolder.Items.OfType(Of Outlook.ContactItem)() Select contactDim emailswithcompany = From email In emails Join contact In contacts _ On email.SenderEmailAddress Equals contact.Email1Address _ Select email, contact.CompanyNameFor Each emailwithco In emailswithcompany Debug.Print(String.Format("{0} from {1}: {2}", _ emailwithco.email.SenderName, emailwithco.CompanyName, emailwithco.email.Subject))Next
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 awlinqDataContextDim custSalesPerson = From cust In awdc.AWCustomers Select cust.CompanyName, cust.SalesPersonDim emailswithcompanysp = From emailco In emailswithcompany _ Join cust In custSalesPerson _ On cust.CompanyName Equals emailco.CompanyName _ Select emailco, cust.SalesPersonFor 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!
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.ApplicationDim 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 emailDim contacts = From contact In contactfolder.Items.OfType(Of Outlook.ContactItem)() Select contactDim awdc As New awlinqDataContextDim 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 emailcontactspDebug.Print(String.Format("{0} from {1}: {2}" & NewLine & "SalesPerson:{3}", _ ecsp.email.SenderName, _ ecsp.CompanyName, _ ecsp.email.Subject, _ ecsp.SalesPerson))Next
See my speaking schedule for more events
User Group Leader
Hosted by:
Powered by: newtelligence dasBlog 2.0.7226.0
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.
© Copyright 2008, Julie Lerman
E-mail