CODEWITHSUNDEEP
linqentity-framework-4
Piyush Sardana
Piyush Sardana

Reputation: 1758

date comparison in EF query

I have a EF query in which i'm using lambda expressions, when i try to get the difference between two dates, it throws me exception

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

my query is 

var unApprovedLeaves = db.Leaves.Where(l => l.Status.Id == 1 && ((System.DateTime.Now.Date - l.ToDate.Date).TotalDays)==0)
    .Include("Employee")
    .Include("Employee.Manager")
    .ToList();

can anyone tell me how do i get this thing right?

Upvotes: 0

Views: 479

Answers (2)

Tomasz Maj
Tomasz Maj

Reputation: 1539

In Entity Framework it is recomended to use DbFunctions helper from System.Data.Entity

Try this:

var today = DateTime.Now.Date;
var unApprovedLeaves = db.Leaves.Where(l => l.Status.Id == 1 && 
                                      (DbFUnction.DiffDays(today, l.ToDate))==0)
    .Include("Employee")
    .Include("Employee.Manager")
    .ToList();

Upvotes: 1

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364409

You must use SqlFunctions helper from System.Data.Objects.SqlClient. Try this:

var today = DateTime.Now.Date;
var unApprovedLeaves = db.Leaves.Where(l => l.Status.Id == 1 && 
                                      (SqlFunctions.DateDiff("day", today, l.ToDate))==0)
    .Include("Employee")
    .Include("Employee.Manager")
    .ToList();

Upvotes: 4

Related Questions