Using date comparison in LINQ when querying SharePoint OData service

ANSWERED: Go below to find my answer to this question.

I am trying to consume SharePoint 2010 OData from an ASP.NET MVC 3 project using LINQ. I created a default project using the ASP.NET MVC 3 project template with the Razor view engine (VS 2010). I added a service reference pointing to my SharePoint 2010 site.

In my HomeController's Index method (this is just a test project), I created a variable to hold the context and set the Credentials property of the context variable to the current default credentials.

A link query like the following works fine and I can use the created variable to access any of the data:

var query = from a in context.Alerts
            select a;

This query simply gets all of the announcements from a list called Alerts in the SharePoint site. This list has fields for the Title, Content, Beginning Date, and Expiration Date.

When I change the query to the following, I do not get the expected results:

var query = from a in context.Alerts
            where (a.Begins < DateTime.Now)
            select a;

This query ignores the time component of the date. For example, if a.Begins contains a datetime from yesterday, the query returns the AlertItem. If on the other hand, a.Begins contains a datetime with the current date (but an earlier time) the comparison returns false (and a.Begins == DateTime.Now returns true).

If I do the following, the second LINQ query works as expected:

var query = (from a in context.Alerts
            select a).ToList();
var query2 = from q in query
             where (q.Begins < DateTime.Now)
             select q;

What am I missing?

Solution

After piecing together information from a lot of different sources -- none of which dealt with the exact circumstances of the problem that I am having, I've come to the following conclusion:

When querying SharePoint data using the SharePoint Object Model and Collaborative Application Markup Language (CAML), SharePoint by default does not use the time component of DateTime elements when doing comparisons. To tell SharePoint to use the time component, you must include the IncludeTimeValue = 'TRUE' property on the value type as shown here:

<Where>
 <Eq>
  <FieldRef Name='Begins' />
  <Value Type='DateTime'  IncludeTimeValue='TRUE'>
   2008-03-24T12:00:00Z
  </Value>
 </Eq>
</Where>

I found several blog posts that referenced a bug in LINQ to SharePoint that caused the generated CAML to be output as:

<Where>
 <Eq>
  <FieldRef Name='dateTimeField'  IncludeTimeValue='TRUE' />
  <Value Type='DateTime'>
   2008-03-24T12:00:00Z
  </Value>
 </Eq>
</Where>

Notice that the IncludeTimeValue = 'TRUE' is on the FieldRef element instead of the Value element. Since this is not the right place for that property, it causes all LINQ to SharePoint queries that perform datetime comparisons to only compare on the date component.

Since I am seeing that exact same behavior when using LINQ and WCF Data Services to connect to SharePoint, I can only assume that under the covers LINQ/WCF Data Services is producing the same invalid CAML.

The solution (assuming I still want to use LINQ/WCF Data Services) is to perform two queries (as stated in the original question). The first LINQ query pulls the list data from SharePoint and stores it in a List. The second LINQ query handles the date comparisons to only pull the data I want.

Since in my particular circumstance, I may have many entries in the SharePoint list covering a large time span but will only be interested in entries on a particular day or couple of days, I wanted to find a way not to bring back the entire list in the first query.

What I settled on was doing a <= and >= comparison to get close, and then further limiting that in my second query. So my two queries now become:

DateTime RightNow = DateTime.Now;
var query = (from a in context.Alerts
             where (a.Begins <= RightNow) && (a.Expires >= RightNow)
             select a).ToList();
var query2 = from q in query
             where q.Begins < RightNow) && (a.Expires > RightNow)
             select q;

The first LINQ statement will return all the items that I am ultimately interested in; along with a few that I'm not (because it's comparing just the date component of the datetime). The second LINQ statement will further pare that down to just those that I'm interested in.