Filter Outlook Items by Date with LINQ to DASL
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).
Comments
Anonymous
May 06, 2008
I tried the sample against a list of Appointment objects after adding the property attributes you describe. When I execute the following LINQ query I get appointments that fall outside of the my start and end times... var appointments = (chkCategories.Checked) ? from item in folder.Items.AsQueryable<MyAppointment>() where item.Start >= _start && item.Start <= _end && item.Categories.Contains(txtCategories.Text) select item.Item : from item in folder.Items.AsQueryable<MyAppointment>() where (item.Start >= _start && item.Start <= _end) select item.Item; I am basically wanting to pull back Appointments which were started between two dates.Anonymous
May 13, 2008
Hi Dean, Sorry for the late reply. You might want to make sure that your _start and _end times are specified in UTC, as DASL seems to require it in some cases. Another check you can make is to create the same query in an Outlook filter and see if the same set of results is returned. -PhilAnonymous
June 01, 2008
...with a bit o' Office 07' thrown in for spice. This is my accumulated link listing that I've gathered...Anonymous
June 17, 2008
The following code works well - no UTC conversion required... var q = outlookItems.CalendarItems.Where( c => c.Start >= Start && c.End <= End; where Start and End are respective DateTimes.. very weird.Anonymous
June 30, 2008
Hi Dean, If CalendarItems is simply a typed wrapper around an Items collection, then the Where() extension method is operating in "LINQ to Objects" mode and not "LINQ to DASL" mode. That is, it is iterating over all of the items in memory rather than executing a DASL query. In that case, I don't believe conversion of dates/times to UTC is required. -PhilAnonymous
January 12, 2009
Why do most examples including this one found on the net break the rules of CDO Do's and Dont's? - this states one should never use a foreach loop as it introduces memory leaks in the underlying Outlook object model... I've been bit by this - none reading this should use this pattern instead use a for loop calling Marshal.ReleaseCOMObject on each obj for each iteration through the loopAnonymous
July 06, 2010
Hi Phil, Inspired by this post, I have created an addin to show a report on my calendar items. By specifying the date range the addin uses "LINQ to DASL" and reports the summary i.e. Date, Subject, Total Duration. I have tried to use the following LINQ but I am getting this NotSupportedException "Queries using GroupBy are not supported": var resultsSummary = ( from item in this.DataSourceFolder.Items.AsQueryable<MyAppointment>() where (item.Start >= this.dateStart.Value.Date.ToUniversalTime()) && (item.Start <= this.dateEnd.Value.Date.ToUniversalTime()) group item by new { item.Start.Date, item.Item.Subject } into sg orderby sg.Key.Date, sg.Key.Subject select new { Date = sg.Key.Date, Subject = sg.Key.Subject, Duration = sg.Sum(i => i.Item.Duration) / 60.0 } ); Note:
- this.DataSourceFolder contains reference to Outlook.MAPIFolder
- this.dateStart and this.dateEnd are my datetimepicker controls. Is GroupBy not supported yet in LINQ to DASL queries? Or am I missing something? Pl. guide me. However, I have found an interim workaround by splitting the query into two and it works fine (as follows): IEnumerable<MyAppointment> results = ( from item in this.DataSourceFolder.Items.AsQueryable<MyAppointment>() where (item.Start >= dateStart.Value.Date.ToUniversalTime()) && (item.Start <= dateEnd.Value.Date.ToUniversalTime()) select item ); var resultsSummary = ( from item in results group item by new { item.Start.Date, item.Item.Subject } into sg orderby sg.Key.Date, sg.Key.Subject select new { Date = sg.Key.Date, Subject = sg.Key.Subject, Duration = sg.Sum(i => i.Item.Duration) / 60.0 } ); Great post and thanks.
- Anonymous
July 07, 2010
Hi Sankarbha, The LINQ to DASL query provider is pretty basic; it supports only the where and select clauses. I'm not sure Outlook's DASL implementation supports grouping and ordering, so even if the query provider did support other clauses, it would have to fall back to the LINQ to Objects implementation. The workaround is, as you found, simply to break the query into two parts: an initial query (using LINQ to DASL) for gathering the Outlook items and then a second query (using LINQ to Objects) that groups and orders the results from the first query. -Phil