How to use ToShortDateString in linq lambda expression?

Question

I need to call ToShortDateString in a linq query suing lambda expressions:

toRet.Notification = Repositories
    .portalDb.portal_notifications.OrderByDescending(p =>       p.id)
    .FirstOrDefault(p => p.date.ToShortDateString() == shortDateString);

but I get the error:

An exception of type 'System.NotSupportedException' occurred in System.Data.Entity.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'System.String ToShortDateString()' method, and this method cannot be translated into a store expression.

What can I do, considering that I do need to use ToShortDateString() ?

Thanks.

Answer 1

Linq to Entities cannot convert ToSortDateString method into SQL code. You can't call it on server side. Either move filtering to client side (that will transfer all data from server to client), or consider to use server-side functions to take date part of date (you should pass DateTime object instead of shortDateString):

EntityFunctions.TruncateTime(p.date) == dateWithoutTime

Answer 2

You shouldn't be forcing a string comparison when what you're working with is Date/time data - as soon as you force string comparisons, you're suddenly having to deal with how the strings are formatted.

Instead, have something like:

var endDate = targetDate.AddDays(1);

toRet.Notification = Repositories
.portalDb.portal_notifications.OrderByDescending(p =>       p.id)
.FirstOrDefault(p => p.date >= targetDate && p.date < endDate);

(Assuming that targetDate is whatever DateTime variable you had that was used to produce shortDateString in your code, and is already a DateTime with no time value)