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.

I opened a support ticket for the same. Will post it once I have an update. — Ram G, Oct 12 '12 at 21:59
I am looking at http://www.odata.org/documentation/uri-conventions#FilterSystemQueryOption but I still don't find where the OData standard suggests to use 'DateTime'... — Daniel Neagu, Oct 15 '12 at 06:26

Daniel Neagu · Accepted Answer · 2012-10-15T08:03:45.513

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.

Thank You. +15. The code worked perfectly. As you mentioned it is building long query :), but right now this is a good alternative I can use of. I need to see as I add additional filters whether it might hit the query string limit but I could handle that may be at the server level. — Ram G, Oct 15 '12 at 14:51

score 0 · Answer 2 · answered Oct 13 '12 at 04:59

0

Try this

var lastPubComponents = _client.Components
                               .Where(p => p.PublicationId == sitePubId && p.LastPublishDate.ToString("yyyyMMdd") < DateTime.Now.ToString("yyyyMMdd")))
                               .ToList();

answered Oct 13 '12 at 04:59

Shekhar Gigras

654
3
5

Thank You. This does not look like client side datetime issue, it is related How Odata service is handling the datetime on server side. Also, VS complaining that ToString does not take overloaded parameters with this code. – Ram G Oct 13 '12 at 12:12

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

2 Answers2