Using DateTime in LINQ to Entities

Question

I have a PostgreSQL database that interacts with the program through Entity Framework Code First.

Database contains a table "users" that has column "visit" type of DateTime.

The application is described as;

public class Users
{    ...
    [Required]
    [Column("visit")]
    public DateTime VisitDate
    ...
}

I trying to run this query;

var rslt = context.Visitors.Where(v => v.VisitDate.Date == DateTime.Now.Date).ToList()

But getting an exception: NotSupportedException

What's wrong?

Answer 1

Mayur Borad's answer (IMHO more correct than the accepted answer) has become out of date:

System.Data.Entity.Core.Objects.EntityFunctions is obsolete. You should use System.Data.Entity.DbFunctions instead.

var today = DateTime.Today; // (Time already removed)

var bla = context.Contacts
    .FirstOrDefault(x => DbFunctions.TruncateTime(x.ModifiedDate) == today);

Answer 2

DateTime.Date property is not supported. You have to use SqlFunctions.DatePart method instead. It will end up with DATEPART TSQL method within generated SQL query.

var rslt = context.Visitors
                  .Where(v => SqlFunctions.DatePart("year", v.VisitDate) == SqlFunctions.DatePart("year", DateTime.Now))
                  .Where(v => SqlFunctions.DatePart("dayofyear", v.VisitDate) == SqlFunctions.DatePart("dayofyear", DateTime.Now))
                  .ToList(); 

Answer 3

The problem is that the LINQ provider is trying to convert DateTime.Now.Date to a database method, which it cannot do by default. The trick to doing date comparison is to create a DateTime instance that has its time component set to the default value. You can get more information here and here.

Answer 4

Use the class EntityFunction for trimming the time portion.

using System.Data.Objects;    

var bla = (from log in context.Contacts
           where EntityFunctions.TruncateTime(log.ModifiedDate) ==  EntityFunctions.TruncateTime(today.Date)
           select log).FirstOrDefault();

Source: http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/84d4e18b-7545-419b-9826-53ff1a0e2a62/