Jaa

Query your Outlook Inbox with LINQ to DASL

  • Artikkeli

Quick, tell me what the following code does:

Outlook.Folder folder = (Outlook.Folder) this.Application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox);

string subject = "VSTO";

string filter = @"@SQL=(""urn:schemas:httpmail:subject"" LIKE '%" + subject.Replace("'", "''") + @"%' AND ""urn:schemas:httpmail:date"" <= '" + (DateTime.Now - new TimeSpan(7, 0, 0, 0)).ToString("g") + @"')";

Outlook.Items restrictedItems = folder.Items.Restrict(filter);

foreach (Outlook.MailItem item in restrictedItems)

{

System.Diagnostics.Debug.WriteLine(String.Format("Body: {0}", item.Body));

}

If you guessed "queries my Outlook inbox for mail with VSTO in the subject over 1 week old" you'd be correct.  Not very pretty, is it?  Well, that's the DAV Searching and Locating (DASL) query language for you.  DASL is one of the ways to return a filtered view of items in an Outlook folder.  The syntax is a variant of SQL, but instead of tables and columns you specify DASL properties which roughly correspond to properties on the various Outlook item interfaces.  The previous example generates a query that looks something like:

 @SQL=("urn:schemas:httpmail:subject" LIKE '%VSTO%' AND "urn:schemas:httpmail:date" <= '2/11/2008 3:20 PM') 

Filtering items using DASL is more efficient than, say, iterating over every item in the collection to identify matching elements.  However, DASL is not without its rough edges.  For example:

  • Since queries are simple strings, there is no strong-typing of query elements.  Furthermore, there are formatting and escaping rules that must be followed when converting types to their string equivalents.
  • As the complexity of the query grows, so does the complexity of building a valid query string.  Imagine creating a query string to search for items with "VSTO" and "Word", but not "VSTO" and "Excel" in the subject, where the item is at least 2 weeks old, but not more than 6 months.  Would you like to maintain such a query?
  • There is no definitive, published mapping between DASL properties and their corresponding Outlook item interface property (if there even is one).  Furthermore, while some DASL properties are shared across all Outlook item types, many others are specific to a single type and there is no easy way to identify which are which.  Currently, the best way to identify a DASL property is to create a filtered view in Outlook and look at the DASL query that it produces.

The Office Interop API Extensions, one of the VSTO Power Tools to be released in the very near future, is not just targeted at Office developers using C#.  The extensions also include a simple LINQ to DASL implementation that allow you to write queries against the Outlook item collections in the same way you would any other LINQ provider.  The LINQ expression is evaulated at runtime, the equivalent DASL query string generated and passed to Outlook.  We can now rewrite the query as follows:

Outlook.

Folder folder = (Outlook.Folder) this.Application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox);

string subject = "VSTO";

var results =

from item in folder.Items.AsQueryable<Mail>()

where item.Subject.Contains(subject) && item.Date <= DateTime.Now - new TimeSpan(7, 0, 0, 0)

select item.Body;

foreach (var result in results)

{

System.Diagnostics.Debug.WriteLine(String.Format("Body: {0}", result));

}

Notice that there are no strings anywhere in the query; no DASL properties to remember and no value formatting to worry about.  Everything is strongly-typed.  Also notice that we can create a projection on the filtered results.  If you only care about the mail bodies, why bother returning the rest of the data?

You might be wondering about the Mail type in the example.  One of the problems with pairing LINQ with Outlook is that the item interfaces are all distinct.  They do not inherit from a common base interface despite them sharing many common properties.  Therefore, you would not be able to create a query that applies to all items.  Furthermore, there are properties on these interfaces which do not have exact DASL equivalents (and vice-versa).  This also limits LINQ from using the interfaces directly.  Finally, because the interfaces are defined within the Office PIAs and not under our control, there is no effective way to create a static mapping between DASL properties and their equivalent properties on these interfaces. 

The LINQ to DASL implementation instead contains a hierarchy of types that parallel the Outlook item interfaces but which form a proper hierarchy, allowing the creation of generic queries.  These types also expose strongly-typed properties which are attributed with their equivalent DASL properties, used to generate the proper query strings.  Finally, the LINQ to DASL implementation of IQueryProvider and IQueryable<T> is conveniently exposed to the developer via the AsQueryable<T>() extension method on the Items interface.

The initial release of the Office Interop API Extensions does not contain mappings for all known DASL properties.  In the event that your favorite property is missing, however, there is a mechanism to extend the built-in types to allow strongly-typed queries of any DASL property.  There is also a mechanism for extending the built-in types to allow strongly-typed queries of custom user properties of Outlook items.  I'll have more details about that in a later post.

Comments

  • Anonymous
    February 18, 2008
    PingBack from http://www.biosensorab.org/2008/02/18/query-your-outlook-inbox-with-linq-to-dasl/

  • Anonymous
    February 19, 2008
    In my last post I talked about LINQ to DASL, a LINQ provider that converts query expressions into their

  • Anonymous
    February 19, 2008
    Phillip this looks amazing -- but where can we get LINQ to DASL?

  • Anonymous
    February 19, 2008
    (or perhaps more correctly, where can we find it when it is released?) :)

  • Anonymous
    February 19, 2008
    I don't have a URL for you at the moment, but you can rest assured that as soon as the VSTO Power Tools (of which the Office Interop API Extensions is a part) are released, I'll have a link to them on my blog.

  • Anonymous
    February 20, 2008
    In an earlier post I discussed LINQ to DASL, part of the Office Interop API Extensions, which is one

  • Anonymous
    February 20, 2008
    In an earlier post I discussed LINQ to DASL, part of the Office Interop API Extensions, which is one

  • Anonymous
    February 20, 2008
    Thank you Phillip - reading this series with eager anticipation for the bits

  • Anonymous
    February 21, 2008
    The Office Interop API Extensions are now available on MSDN: http://www.microsoft.com/downloads/details.aspx?FamilyId=46B6BF86-E35D-4870-B214-4D7B72B02BF9&displaylang=en

  • Anonymous
    March 08, 2008
    The comment has been removed

  • Anonymous
    March 10, 2008
    Hi Deraldo, My guess is that you're getting back more than just MailItem types in your query, which is causing the projection of Mail => Mail.Body (with an implicit call to MailItem.Body) in your select statement to fail.  LINQ to DASL is a generic querying mechanism and so does not do any filtering of the returned data itself.  It doesn't know, for example, that a query using the Mail type generally expects that all the items returned by Outlook should be of type MailItem.  The reason for that is to enable LINQ to DASL to work with classes completely different than Mail, Appointment, etc. So long as the properties on any query class are properly marked up, LINQ to DASL can use it to query Outlook.  This means that your queries must be written such that they will match only very specific item types, or that you use the more generic OutlookItem which works with any Outlook item to be returned.   To query items of a specific type from a collection with mixed types, try using the MessageClass property (defined on OutlookItem) in your query to restrict the types of items returned by Outlook.  In the future we might have a more declaritive way to specify the types of Outlook items the query expects.  Until then, this is probably the best approach. -Phil

  • Anonymous
    June 16, 2008
    folder.Items.AsQueryable<Mail>() Any idea what the VB.NET form of this is?  I have been learning LINQ and trying samples in VB.NET.  It is extremely frustrating that minor syntax differences between vb.net and c# waste lots of time for me.

  • Anonymous
    June 30, 2008
    Hi Kevin, The equivalent VB syntax would be: folder.Items.AsQueryable(Of Mail)() One more thing: you will want to use AndAlso and OrElse in any conditional statements in your query, as our expression parser doesn't currently comprehend the VB And or Or. -Phil

  • Anonymous
    September 02, 2009
    I'm trying to do a couple of things:

  1. Get the start  and end date of the current calendar view
  2. filter that view by those appointments that have reminders set
  3. OnClick on button remove the reminders. Any suggestions?