Tridion OData Query with Datetime filter - error : datetime is not property of Entity

Question

I am trying to query the OData using .net client to get the Last Published components within a specific DateTime. It is always failing with an exception 'datetime' is not property of entity: 'com.tridion.storage.ComponentMeta'

var lastPubComponents = _client.Components
                               .Where(p => p.PublicationId == sitePubId && p.LastPublishDate > Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-ddThh:mm:ss")))
                               .ToList();

When I checked the constructed Odata URL it looks like the following and I tried with the same URL from browser just to double check I got the same error which is expected.

Odata URL when I tried with LINQ

/cd_webservice/odata.svc/Components?$filter=PublicationId eq 59 and LastPublishDate ge datetime'2012-10-11T09:22:14'

I tried by removing the datetime string from the above URL and checked from browser again. This URL is working as expected and I am getting the correct results.

Below is the URL (working w/out datetime)

/cd_webservice/odata.svc/Components?$filter=PublicationId eq 59 and LastPublishDate ge '2012-10-11T09:22:14'

That kind of leads me thinking it is something wrong with LINQ and/or .net client combination.

I tried playing with different Datetime format combinations and I get the same error. When I checked Odata standard it is suggesting to use datetime string but that is the one I am having issues with.

Anybody knows how to debug/resolve this from LINQ code ? Any help will be greatly appreciated.

Answer 1

The OData specs do mention the possibility to query with constructs like "LastPublishDate ge datetime'2012-10-11T09:22:14'" in http://www.odata.org/documentation/overview#AbstractTypeSystem but this was not implemented into the product. However, in the product are the date methods allowed by OData specs for filtering (http://www.odata.org/documentation/uri-conventions#FilterSystemQueryOption). These are day(), hour(), minute(), month(), second() and year() and this basically translates into a nightmare query for you:

DateTime timeNow = DateTime.Now;
int yearNow = timeNow.Year;
int monthNow = timeNow.Month;
int dayNow = timeNow.Day;
int hourNow = timeNow.Hour;
int minuteNow = timeNow.Minute;
int secondNow = timeNow.Second;
var lastPubComponents = service.Components
     .Where(p => p.PublicationId == 3 && 
            (p.LastPublishDate.Value.Year > yearNow || 
              (p.LastPublishDate.Value.Year == yearNow && p.LastPublishDate.Value.Month > monthNow) ||
               (p.LastPublishDate.Value.Year == yearNow && p.LastPublishDate.Value.Month == monthNow && p.LastPublishDate.Value.Day > dayNow) ||
               (p.LastPublishDate.Value.Year == yearNow && p.LastPublishDate.Value.Month == monthNow && p.LastPublishDate.Value.Day == dayNow && p.LastPublishDate.Value.Hour > hourNow) ||
               (p.LastPublishDate.Value.Year == yearNow && p.LastPublishDate.Value.Month == monthNow && p.LastPublishDate.Value.Day == dayNow && p.LastPublishDate.Value.Hour == hourNow && p.LastPublishDate.Value.Minute > minuteNow) ||
               (p.LastPublishDate.Value.Year == yearNow && p.LastPublishDate.Value.Month == monthNow && p.LastPublishDate.Value.Day == dayNow && p.LastPublishDate.Value.Hour == hourNow && p.LastPublishDate.Value.Minute == minuteNow && p.LastPublishDate.Value.Second > secondNow)
              )
             )
     .ToList();

Hope this helps.

Regards, Daniel.