WCF DataServices - Date Time comparison in OData query

The concept of WCF data service which uses OData is really great. But I doubt whether that is ready to be used in an enterprise production applications. The main reason is its immaturity and lack of support on some Linq queries and operators. One of the major item is lack of joins. I believe OData specification itself doesn't have join. Below is another scenario which is not supported by WCF Data Services.

Requirement - Compare date in database against current date

Lets take and scenario where a MessageProcessAttempt table is exposed via WCF Data service. There is a field called DequeuedTime which tells when a message is dequeued. We need to list down all the messages which dequeued in last 1 hour.

In normal sense we can obtain the list by subtracting the de-queued time from current time and checking if the difference < 1 hour. The query can be as follows.

---

List<MessageProcessingAttempt> msgs = (from msg
                           in qProxy.MessageProcessingAttempts
                           where (DateTime.UtcNow- msg.DequeuedTime.Value).TotalHours<1
                           select msg).ToList();

---

But it will fail with below error message
"No property 'TotalHours' exists in type 'Edm.DateTime' at position 56."
Position will change based on the code.There will be no compile time errors. It just fails at runtime. Tried to find the details about Edm.DateTime class in msdn but able to find out except one EdmDateTimeConstant class.So what can we do

Solution - Calculate target date and compare

Find the target date and do comparison rather than finding the difference in the OData query.

---

DateTime timeToFilter = DateTime.UtcNow.Subtract(TimeSpan.FromHours(1));

List<MessageProcessingAttempt> msgs = (from msg
                           in qProxy.MessageProcessingAttempts
                           where msg.DequeuedTime.Value >= timeToFilter
                           select msg).ToList();

Here the major difference we can notice that the date subtraction is not happening via operator and its not producing the TimeSpan obejct. We can have DateTime.UtcNow.Subtract(TimeSpan.FromHours(1)) in OData as well instead of variable and it will work. This points to the fact that TotalHours property is not there in a special type called Edm.TotalHours which is produced as part of subtraction operator.

This was found by our development team in India and I didn't agree to them in the beginning. I spend around 2-3 hours and could see WCF Data Service is still not suitable for enterprise apps. Thanks to the team for finding the workaround.

Happy coding...