I received an email over the weekend asking why the following LINQ to DASL query threw an exception:

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

var appointments =

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

where item.Categories.Contains("Personal Appointments") && item.Item.Start.Date >= DateTime.Now - new TimeSpan(30, 0, 0, 0)

select item.Item;

foreach (var appointment in appointments)

{

MessageBox.Show(appointment.Start.ToString());

}

The query looks simple enough—return all personal appointments for the last 30 days—but when the foreach loop executes a MissingPropertyAttributeException is thrown stating "The property Date on class DateTime does not have an attached OutlookItemUserPropertyAttribute". The problem here is that (as the exception indicates) the Appointment.Item.Start.Date property does not have an OutlookItemProperty or OutlookItemUserProperty attached. These attributes are used by LINQ to DASL to map properties defined on .NET classes to DASL properties defined by Outlook. Why doesn't this attribute exist? Appointment.Item is of type Microsoft.Office.Interop.Outlook.AppointmentItem. This type is part of the Outlook object model and defined by the Outlook PIA. Unfortunately, since we have no control over the Office PIAs, we can't markup the types with our LINQ to DASL attributes. This means that we can't directly query properties on Outlook items. Instead, we query properties on a proxy class (i.e. Appointment) that we do have control over.

But wait a minute…the Appointment class doesn't have a Start property! Yes, unfortunately we weren't able to map every known DASL property to its Outlook item equivalent for this initial release (only so many hours in the day and all that). We did, however, provide a way for you to add such properties yourself.

internal class MyAppointment : Appointment

{

[OutlookItemProperty("urn:schemas:calendar:dtstart")]

public DateTime Start { get { return Item.Start; } }

[OutlookItemProperty("urn:schemas:calendar:dtend")]

public DateTime End { get { return Item.End; } }

}

The MyAppointment class above derives from the existing Appointment class and adds two new properties, Start and End. These properties simply defer to the inner Item's Start and End properties. Each property has an OutlookItemPropertyAttribute attached that maps the property to its corresponding DASL property, which can be found using the handy SQL tab of Outlook's custom filter dialog. Next, the query can be revised as follows:

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

var appointments =

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

where item.Categories.Contains("Personal Appointments") && item.Start >= DateTime.Now - new TimeSpan(30, 0, 0, 0)

select item.Item;

foreach (var appointment in appointments)

{

MessageBox.Show(appointment.Start.ToString());

}

Note that the AsQueryable<T>() extension method now uses the new MyAppointment type and that its Start property is used instead of Item.Start.Date. Run the query again and Outlook should return a collection of appointments instead of an exception (presuming you have any appointments which match the query).

Now that the Office Interop API Extensions have been released, I thought I would post a complete walkthrough of a simple LINQ to DASL application. Let's start with my fictitious Outlook calendar:

This calendar shows that I have four appointments today. The appointments have been categorized as either "Work" (blue) or "Personal" (green). Suppose I would like to create an Outlook add-in that displays my personal appointments on startup. I will first create a new C#-based Outlook 2007 Add-in project in Visual Studio 2008.

Next I'll add a reference to one of the Outlook extension assemblies, which were installed as part of the Office Interop API Extensions. I'll select version 12.0.0.0 of the assembly because I'm using Outlook 2007. Version 11.0.0.0 of the assembly would be used with Outlook 2003.

Before they can be used, I have to tell the compiler to look for the extensions during build. I'll do that through a set of using statements at the beginning of my source file.

Two using statements are required:

• using Microsoft.Office.Interop.Outlook.Extensions;

  This statement brings in the Items.AsQueryable<T>() extension method that I'll use in my LINQ expression.

• using Microsoft.Office.Interop.Outlook.Extensions.Linq;

  This statement brings in the LINQ to DASL types that form the basis for my LINQ expression.

With that, I can then write my LINQ expression in the startup event handler of the add-in:

private void ThisAddIn_Startup(object sender, System.EventArgs e)

{

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

var appointments =

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

where item.Categories.Contains("Personal")

select item.Item;

var builder = new StringBuilder();

builder.AppendLine("Personal Appointments:");

builder.AppendLine();

foreach (var appointment in appointments)

{

builder.AppendLine(appointment.Subject);

}

MessageBox.Show(builder.ToString());

}

Let's look at this query more closely. The first clause "from item in folder.Items.AsQueryable<Appointment>()" uses the new Items.AsQueryable<T> extension method. This extension method simply returns a new instance of the ItemsSource<T> class, which implements the LINQ interfaces IQueryProvider and IQueryable. I know this folder contains only appointments, so I specify the Appointment class for the generic type. The Appointment class is the LINQ to DASL class associated with the AppointmentItem interface in Outlook. If the folder contained a mixture of Outlook item types (such as both appointments and meetings), I would either need to use the more generic OutlookItem class or use the MessageClass property in my query to restrict the types of the items returned.

The second clause "where item.Categories.Contains("Personal")" is the "meat" of the query. This is the expression translated into a DASL query string and passed to Outlook. Outlook then returns a collection of Items matching the query string. In this case, I want Outlook to return only items where the categories property contains the string "Personal". The where clause can contain a number of different types of expressions:

• The typical set of comparisons (==, !=, <, <=, >=, >, &&, ||)
• Negation (!)
• Method calls on properties using String.Contains(), String.StartsWith(), and String.EndsWith()
• Expressions involving user properties (e.g. item.UserProperties["Foo"].Value)

The last clause "select item.Item" specifies what items to return from the query. LINQ to DASL will wrap each item returned by Outlook with an instance of the type specified in the AsQueryable<T>() extension method. That instance can be returned as-is, or a projection on that instance can be returned instead. I want the original AppointmentItem instance returned by Outlook so I specify a simple projection that returns the Item property on the Appointment class. The select clause also determines the ultimate type of the returned data, IEnumerable<Outlook.AppointmentItem> in this case. This is what I iterate over in my foreach loop.

Finally, I can hit 'F5' and see the results.

Hopefully this helps people get started with LINQ to DASL. (If it doesn't, please let me know what else I can cover to make things more clear.) This sample can be found on Code Gallery here.

One of the disadvantages of C# compared with VB is its lack of support for parameterized properties. Instead, parameterized properties in C# are exposed as normal method calls prefixed with "get_" and "set_". This is particularly apparent when working with the Office object model as it exposes many such properties, the majority being indexers on collection interfaces. To make matters worse, some collection interfaces have array indexers rather than indexer properties, which make indexing inconsistent between collection types. Since many of the parameters are optional or accept varying types, we lose many of the strong typing benefits of the C# language. The end result is inconsistent, inelegant, and error prone code.

Let's take the Documents collection in Word for example. That interface defines the Item property with a single variant (i.e. object) argument. According to the MSDN documentation, that argument value should be an integer or a string. Retrieving a document by integer index looks something like:

object index = 1;

Word.Document doc = Application.Documents.get_Item(ref index);

MessageBox.Show("Name: " + doc.Name);

Notice the use of 1 instead of 0; remember that the Office object model uses 1-based indexing. Also note that we have to pass the index by reference, requiring an extra object on the stack. This is a particular quirk of the Word object model, but one that makes for even more inelegant code.

Retrieving a document by name looks something like:

object index = "WordDocument1.docx";

Word.Document doc = Application.Documents.get_Item(ref index);

MessageBox.Show("Name: " + doc.Name);

Let's clean up these examples using the Office Interop API Extensions, one of the recently released VSTO Power Tools. For collections, the libraries expose a set of common Item() extension methods with strongly-typed arguments. With those we can rewrite our examples:

Word.Document doc = Application.Documents.Item(1);

MessageBox.Show("Name: " + doc.Name);

And:

Word.Document doc = Application.Documents.Item("WordDocument1.docx");

MessageBox.Show("Name: " + doc.Name);

Unfortunately, not all of the collections could be extended in the initial release of the VSTO Power Tools. You will find the Word and Excel object models to be most complete, with key collections extended across the rest of the Office suite. (If there are particular collections which you think should be extended in the future, please let us know so that we can prioritize them appropriately.)

Indexers are not the only parameterized properties which were extended, however. Let's take another example from Excel:

Excel.Worksheet sheet = InnerObject;

Excel.Range range1 = sheet.get_Range("A1:B1", System.Type.Missing);

Excel.Range range2 = sheet.get_Range("A1", "B2");

In this example, we're retrieving two ranges from an Excel Worksheet. The Range property (as defined by the COM interface) accepts two parameters, both of which are of variant type and the second of which is also marked optional. Since C# doesn't support optional parameters, we have to pass System.Type.Missing to indicate the absence of the second value. Since both values are variant types, we do not have any compile-time type checking. Again, we can use the extensions provided by the Office Interop API Extensions to improve the code:

Excel.Worksheet sheet = InnerObject;

Excel.Range range1 = sheet.Range("A1:B1");

Excel.Range range2 = sheet.Range("A1", "B2");

Not only have we eliminated the ugly "get_" and the need to specify System.Type.Missing, our arguments are conveniently strongly-typed. These may seem like little things, but over the long term I think the improved readability and additional compile-time support will make for a better Office development experience for the C# developer.

As announced in Andrew Whitechapel's post, version 1.0 of the VSTO Power Tools have been released!  One of those tools is the Office Interop API Extensions, a set of libraries which extend the Office object model to simplify development on the Office platform.  This past week I've blogged about the capabilities of these extensions and now you have the chance to try them out yourselves!  Please let us know what works, what doesn't, what could be improved, and what you would like to see in the future.

The VSTO Power Tools v1.0.0.0 is a collection of three packages found here.  The Office Interop API Extensions is packaged as VSTO_PTExtLibs.exe.  Please note that, despite the prerequisites listed on the MSDN download page, the Office Interop API Extensions can be used with both Office 2007 and Office 2003, though .NET 3.5 is required in both cases.  Also, these extensions do not have a dependency on VSTO; they can be used within any application which automates Office.

In an earlier post I discussed LINQ to DASL, part of the Office Interop API Extensions, which is one of the forthcoming VSTO Power Tools.  LINQ to DASL allows you to write LINQ expressions against Outlook item collections.  I also mentioned that many known DASL properties were not mapped to their Outlook item equivalents in this initial release.  So how do you write LINQ expressions using DASL properties that weren't included?  The answer is: by extending the base LINQ to DASL classes.  Let's start with a non-LINQ example:

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

var topics = new List<string>();

foreach (object item in folder.Items)

{

Outlook.MailItem mailItem = item as Outlook.MailItem;

if (mailItem != null)

{

if (mailItem.ConversationTopic.Contains("VSTO"))

{

topics.Add(mailItem.ConversationTopic);

}

}

}

foreach (var topic in topics)

{

System.Diagnostics.Debug.WriteLine(String.Format("Conversation Topic: {0}", topic));

}

The example shows our first attempt to find all mail items with a conversation index containing the acronym "VSTO".  It simply iterates over each item in the folder, checks whether it's a mail item, then checks whether it has a matching conversation topic.  Iterating over each and every item in the folder is not very efficient.  We can have Outlook perform the filtration in its own native (and presumably more efficient) way.  We do this by creating a DASL query string and passing it to the Items.Restrict() method.  Here is the updated example:

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

string filter = @"@SQL=(""urn:schemas:httpmail:thread-topic"" LIKE '%VSTO%')";

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

var topics = new List<string>();

foreach (object item in items)

{

Outlook.MailItem mailItem = item as Outlook.MailItem;

if (mailItem != null)

{

topics.Add(mailItem.ConversationTopic);

}

}

foreach (var topic in topics)

{

System.Diagnostics.Debug.WriteLine(String.Format("Conversation Topic: {0}", topic));

}

The collection returned by Items.Restrict() is the subset of items matching the filter.  It may be more efficient, but it certainly doesn't simplify the code.  It would be nice if we could use a strongly-typed LINQ expression.  Unfortunately, the Mail class in the initial LINQ to DASL implementation does not have a property which maps to the "urn:schemas:httpmail:thread-topic" DASL property.  We can extend the Mail class to do so, however, as shown below:

internal class MyMail : Mail

{

[OutlookItemProperty("urn:schemas:httpmail:thread-topic")]

public string ConversationTopic

{ get { return Item.ConversationTopic; } }

}

We create a new class, MyMail, which inherits from the existing LINQ to DASL Mail class.  We then add a ConversationTopic property which simply delegates to the ConversationTopic property on the Outlook.MailItem reference held by the Mail class.  The key piece is the OutlookItemPropertyAttribute attached to the property.  This attribute provides the mapping between the property as used in a LINQ expression and the DASL property in the query string.  With that, we can finally write our LINQ expression:

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

var topics =

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

where item.ConversationTopic.Contains("VSTO")

select item.ConversationTopic;

foreach (var topic in topics)

{

System.Diagnostics.Debug.WriteLine(String.Format("Conversation Topic: {0}", topic));

}

Extensions to LINQ to DASL are not limited to the addition of new DASL properties.  You can also extend the LINQ to DASL classes to allow strongly-typed query expressions using custom user properties.  I'll discuss that topic in a future post.

In my last post I talked about LINQ to DASL, a LINQ provider that converts query expressions into their DASL equivalent in order to efficiently filter item collections in Outlook.  But LINQ to DASL solves only a very specific problem for one particular application.  The Office object model has many types of collections that we might like to use in LINQ expressions.  How do we do that?  The answer is: it depends. 

(If you don't care about the background information, skip to the end of the post to see how you can use the Office Interop API Extensions to simpliy the use of LINQ with the Office object model.)

Most collections in the Office object model implement IEnumerable, which allows them to be used in foreach statements.  Let's take Word's Documents collection interface, for example:

Word.Documents docs = Application.Documents;

foreach (Word.Document doc in docs)

{

MessageBox.Show(doc.Name);

}

LINQ expressions, however, require collections to implement IEnumerable<T>.  Luckily, .NET 3.5 includes the Cast<T>() and OfType() extension methods that convert an IEnumerable into the IEnumerable<T>, as shown in this example:

 Word.Documents docs = Application.Documents;

var names =

from doc in docs.Cast<Word.Document>()

select doc.Name;

foreach (var name in names)

{

MessageBox.Show(name);

}

There are some collections which do not implement IEnumerable but still expose a GetEnumerator() method.  These can also be used in foreach statements.  The Windows interface in the Excel object model is one such collection, as shown in this example:

Excel.Windows windows = Application.Windows;

foreach (Excel.Window window in windows)

{

MessageBox.Show(window.WindowNumber.ToString());

}

Unfortunately, there is no direct conversion between a type which exposes a GetEnumerator() method and IEnumerable<T>.  For that, our own conversion routine is needed.  We can actually make this an extension method on the Windows interface to simplify things, as shown below:

 public static class Extensions

{

public static IEnumerable<Excel.Window> ToEnumerable(this Excel.Windows windows)

{

foreach (Excel.Window window in windows)

{

yield return window;

}

}

}

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));

}

I like VSTO.  I like C#.  What I don't like is having to write VSTO code in C# like:

object fileName = "Test.docx";

object missing  = System.Reflection.Missing.Value;

doc.SaveAs(ref fileName, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);

This code creates a copy of a Word document, but it's not very elegant.  The SaveAs() method accepts a long list of arguments to tweak the way the file is copied.  For a simple copy, however, only the first argument--the new file name--is needed.  So why am I stuck passing this "missing" value for each and every omitted argument?  The definition of the SaveAs method on the Word Document COM interface defines each argument as optional (see below).  This would seem to allow callers to omit the irrelevant arguments.  VB developers wouldn't give this another thought.

HRESULT SaveAs(

                    [in, optional] VARIANT* FileName,

                    [in, optional] VARIANT* FileFormat,

                    [in, optional] VARIANT* LockComments,

                    [in, optional] VARIANT* Password,

                    [in, optional] VARIANT* AddToRecentFiles,

                    [in, optional] VARIANT* WritePassword,

                    [in, optional] VARIANT* ReadOnlyRecommended,

                    [in, optional] VARIANT* EmbedTrueTypeFonts,

                    [in, optional] VARIANT* SaveNativePictureFormat,

                    [in, optional] VARIANT* SaveFormsData,

                    [in, optional] VARIANT* SaveAsAOCELetter,

                    [in, optional] VARIANT* Encoding,

                    [in, optional] VARIANT* InsertLineBreaks,

                    [in, optional] VARIANT* AllowSubstitutions,

                    [in, optional] VARIANT* LineEnding,

                    [in, optional] VARIANT* AddBiDiMarks);

Unfortunately, the C# language and compiler do not comprehend optional arguments.  What's worse, unlike the rest of the Office object model, Word interfaces use VARIANT* instead of VARIANT.  That is, they are passed by reference rather than by value.  This means that, not only does the C# developer have to pass a value for each and every argument, he or she must do so by reference.  That means creating an extra object on the stack and then passing it to the method using the ref keyword.  How tedious!  And it gets even worse; because the values are all passed using objects, we've now lost all of the compile-time advantages of the strongly-typed C# language.  How easy would it be to accidentally swap the order of the arguments?

In a perfect world, a simple copy of the document could be created like this:

doc.SaveAs("Test.docx");

The method would take a strongly-typed string argument by value.  And if I wanted to change the format of the newly-saved document, I could write the following:

doc.SaveAs("Test.html", WdSaveFormat.wdFormatHTML);

This method would take the same string argument and another strongly-typed format specifier.  We could imagine a series of method overloads that incrementally add to the argument list.  But what if I need to specify a disjoint set of arguments?  Why can't I simply write:

The method would take an instance of an arguments class where I could set--using the fancy new C# 3.0 object initializer syntax--only the properties necessary.  Furthermore, all the properties would be strongly-typed so that any accidentally swapped arguments could be caught at compile-time and not after the application has been deployed to thousands of clients. 

Am I just dreaming?  Must I be content with typing "ref" over and over again for as long as I develop Office applications?  Must I leave my beloved C# for the seductive VB?  The answer is a resounding NO!  The VSTO Power Tools announced at this week's Office Developer Conference are expected to be released in the very near future.  One of those tools is the Office Interop API Extensions, a set of libraries that extend the Office object model and provide a more elegant and consistent API for the C# developer.  The three examples above are all possible using the Word extensions shipped as part of this tool.  Furthermore, many other interfaces* from across the Office object model have been extended in a similar manner in order to make the lives of C# developers easier.  Keep an eye out for this tool and use it to banish Missing.Value (and its close cousin Type.Missing) from your C# Office applications.

*In this initial release, most of the extension work was focused on Word and Excel.  The Outlook extensions had an entirely different focus which I'll discuss in a later post.

Yesterday I gave a presentation on VSTO at the third annual Portland Code Camp.  I demonstrated an Outlook 2007 add-in that used Outlook Form Regions, WCF, and WPF to integrate Amazon.com Wish Lists with contact items.  As my first public speaking event since joining Microsoft last November, I thought it went pretty well.

I've attached the add-in that I created during the presentation.  You will need to sign up with Amazon and receive an access key before it can be run.  The add-in was developed with Visual Studio "Orcas" Beta1.