.NET - Queries uitvoeren op Office data met LINQPad

After I developed the OutlookProvider and OneNoteProvider classes (see my article Querying Outlook and OneNote with LINQ) I was trying to execute some queries on Office data with LINQPad. LINQPad is a fantastic and free LINQ expressions testing tool from Joseph Albahari. (http://www.linqpad.net). Because it was not possible to refer to COM assemblies from the GAC, my queries failed on execution. But then Joe did a great job by solving this issue and releasing version 1.13 of LINQPad. So here I will describe the steps how to utilize LINQPad to visualize and execute queries on Outlook and OneNote data:

Configure LINQPad

1) Go to Query >> Advanced Properties (F4)

2) Tabsheet Additional References. Add a reference to the Microsoft.Office.Interop.Outlook assembly which is stored in the Global Assembly Cache (GAC).

3) Add a reference to my ScipBe.Common.Office assembly by using the Browse button in the Add Custom Assembly Reference dialog.

4) Tabsheet Additional Namespace Imports. Add the namespaces ScipBe.Common.Office and Microsoft.Office.Interop.Outlook.

5) Press the Set as default for new queries button and close the window.

6) Set Type to C# Statement(s)

Execute LINQ to Outlook and LINQ to OneNote queries

7) Now you can start creating LINQ queries on the collections of the OutlookProvider and OneNoteProvider classes. Create an instance, define a query and use the Dump extension method to output the results. Use Execute (F5) to visualize these results in the HTML window.

Because LINQPad is not able to reflect the properties of a COM object, you have to explicity list the properties of the Outlook items in your select clause. This is not needed when using the OneNoteProvider class.

When you have a AutoCompletion license for LINQPad you will notice that the autocompletion features will not work for the items. Thefore add a LET statement and typecast the item to MailItem, ContactItem, AppointmentItem, ...

Outlook - Mails

 OutlookProvider outlookProvider = new OutlookProvider();
 
var query = from mail in outlookProvider.SentMailItems
            let m = (mail as MailItem)
            where m.SentOn > DateTime.Now.AddDays(-31)
            orderby m.SentOn descending
            group m by m.SentOn.Date into g
            select new
            {
              SentOn = g.First().SentOn.Date,
              Count = g.Count()
            };
 
query.Dump("Statistics sent mails last month");

OneNote - Pages

OneNoteProvider oneNoteProvider = new OneNoteProvider();
 
var query = from page in oneNoteProvider.PageItems
            where page.LastModified > DateTime.Now.AddDays(-14)
            select page;
 
query.Dump("Last changed pages");