Linq to Entities DateTime Conversion

Question

If given a an entity with a datetime as a string... what are my options to filter the data with Linq to Entities on the date?

It does not seem to support me doing datetime conversions.

Basically, I want to accomplish:

var filtered = from item in entities.itemsSet
               where Convert.ToDateTime(shift.starttime) >= startDate 
                   && Convert.ToDateTime(shift.endtime) < endDate
               select item;

What are my options to accomplish this?

Answer 1

You are going to end up doing in memory filtering.

//So first select your data

var data= (from item in entities.itemsSet).ToList();


//Then filter
var filtered = from item in data
           where Convert.ToDateTime(shift.starttime) >= startDate 
               && Convert.ToDateTime(shift.endtime) < endDate
           select item;

Another option would be to create a stored procedure to do it for you. In the SP you would have to take start/end and then compare it to your date time strings casted as Date Times.

Answer 2

I was afraid this was the answer. I'm a little new to linq - what kind of performance implications are we talking? for a table say of 10k records?

Theres also a workaround using the CAST in a where statement, but I haven't figured out how to use it in my situation:

http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/abc19619-61ff-4bc5-bda9-df501f113c74

cheers

rusty