Comparing DateTimes in a LINQ to SharePoint Query
Last time I blogged about LINQ to SharePoint (available for SharePoint 2010), and especially about the fact that the underlying CAML generation matters, even though it is hidden by the developer. Here is another issue, or a bug, that I came across.
Consider the following simple situation: you want to fetch and display those calendar events that start within the next three hours. Here’s the code that should do it:
{
string url = "http://test.sp.dev/test";
StringWriter logger = new StringWriter();
var time1 = DateTime.Now;
var time2 = time1.AddHours(3);
using (var context = new MyDataContext(url))
{
context.Log = logger;
var items = from e in context.Calendar
where e.StartTime != null && e.StartTime > time1
&& e.StartTime < time2
select e;
Console.WriteLine("Fetched items: {0}", items.Count());
}
Console.Write(logger.ToString());
}
Even though I have plenty of events that should match the criteria, the code prints:
The generated CAML (that also gets printed to the console) looks just fine at the first sight:
<Query>
<Where>
<And>
<BeginsWith>
<FieldRef Name="ContentTypeId" />
<Value Type="ContentTypeId">0x010200</Value>
</BeginsWith>
<And>
<And>
<IsNotNull>
<FieldRef Name="EventDate" IncludeTimeValue="TRUE" />
</IsNotNull>
<Gt>
<FieldRef Name="EventDate" IncludeTimeValue="TRUE" />
<Value Type="DateTime">2010-09-03T10:07:18Z</Value>
</Gt>
</And>
<Lt>
<FieldRef Name="EventDate" IncludeTimeValue="TRUE" />
<Value Type="DateTime">2010-09-03T13:07:18Z</Value>
</Lt>
</And>
</And>
</Where>
</Query>
<ViewFields>
</ViewFields>
<RowLimit Paged="TRUE">2147483647</RowLimit>
</View>
A closer look reveals that the <Value Type=”DateTime”> element is missing the IncludeTimeValue=”TRUE” attribute. This means that the date comparisons in CAML are made on date level. This is clearly not what I wanted. Not surprisingly, replacing the < and > comparisons with <= and >= comparisons returns plenty of events, but they actually return all events of the current day (or all events of two days if queried just before midnight).
The solution? I have to fetch all events of possible interest (i.e. all events of the current and possibly the next day), and do the time-level filtering with in-memory LINQ. Following the guidelines of my previous post, I refactored the code as follows:
{
string url = "http://test.sp.dev/test";
StringWriter logger = new StringWriter();
var time1 = DateTime.Now;
var time2 = time1.AddHours(3);
using (var context = new MyDataContext(url))
{
context.Log = logger;
// This query gets translated to CAML and executed in content database
// Note that the CAML datetime comparisons are made on a date level!
var items = (from e in context.Calendar
where e.StartTime != null && e.StartTime >= time1 && e.StartTime <= time2
select e).ToList(); // ToList() executes the CAML
// Perform additional filtering with in-memory LINQ
items = items.Where(e => e.StartTime >= time1 && e.StartTime <= time2).ToList();
Console.WriteLine("Fetched items: {0}", items.Count());
}
Console.Write(logger.ToString());
}
Note that even though the conditions in both LINQ statements look the same, the actual filtering logic is different!
Popularity: 5% [?]
Any ideas how we can fetch recurring events breakdown to recurring instances ?
I have a problem here. how can we user LINQ to query a calender list to get all the events including recurring events, all day events etc.
my experience is that when you use LINQ, the resultant data set only has events that are on the list as collective events. for examples recurring events may have several instances depending upon the frequency they are occurring. Do you have any suggestion on how to use LINQ to query calender with Recurring events expanded ?