Linq to Entities Converting DateTime

Question

If an entity with DateTime is given as a string, what are my options for filtering data with LINQ to Entities in a date?

It doesn't seem to support me doing DateTime conversions.

Basically, I want to execute:

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

What are my options for this?

Answer 1

In the process of filtering data you will do.

//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 is to create a stored procedure to do this for you. In SP, you will need to start / end and then compare it with date strings selected as Date Times.

Answer 2

Use System.Data.Objects.SqlClient.SqlFunctions

DateDiff, .

SqlFunctions SQL Linq .

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

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

"" , . SQL DATEFIFF:

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

Linq to SQL, .

Answer 3

, Sql Server EF DateTime.Parse CAST (varField As DateTime).

, , , , , : DateTime.Parse(x.DateField) == DateConstraint

. "" : EFQuery.Where()

if (client.DateOfBirth.HasValue) // can't find a string <-> DateTime conversion syntax with EF support

         {

            var day = client.DateOfBirth.Value.Day.ToString().PadLeft(2, '0');

            var month = client.DateOfBirth.Value.Month.ToString().PadLeft(2, '0');

            var year = client.DateOfBirth.Value.Year.ToString();

            // very verbose, but apparently there no EF support for String to DateTime conversion

            spec = spec.And(c => SqlFunctions.IsDate(c.DateOfBirth).HasValue && SqlFunctions.IsDate(c.DateOfBirth).Value == 1

               && c.DateOfBirth.StartsWith(day)

               && month == (c.DateOfBirth.Contains("-") || c.DateOfBirth.Contains("/")

               ? c.DateOfBirth.Substring(c.DateOfBirth.Contains("-") ? c.DateOfBirth.IndexOf("-") + 1 : c.DateOfBirth.Contains("/")

               ? c.DateOfBirth.IndexOf("/") + 1 : 0, 2) : "f")

               && c.DateOfBirth.EndsWith(year));

         }

, , DD/MM/YYYY (Aussie Aussie Aussie!). / Time .