Linq to Entities DateTime Conversion

Question

If given 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

Use System.Data.Objects.SqlClient.SqlFunctions

There is a function called DateDiff which has overloads to accept strings as dates.

All of the functions inside of the SqlFunctions class compile into SQL statements, and can only be used inside of Linq to entities.

[EdmFunctionAttribute("SqlServer", "DATEDIFF")]
public static Nullable<int> DateDiff(string datePartArg, string startDate, string endDate)

http://msdn.microsoft.com/en-us/library/dd466158.aspx

You will need to pass in a string like "day" as the first parameter specifying the part of the date to compare. The function directly maps to the SQL function DATEFIFF:

http://msdn.microsoft.com/en-us/library/ms189794.aspx

There is a similar class for Linq to SQL, just warning you.

Answer 2

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.