Using DateTime in LINQ for Objects

Question

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

The database contains a "users" table, which has a column type of "visit" DateTime.

The application is described as:

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

I am trying to run this query;

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

But getting exception: NotSupportedException

What's wrong?

Answer 1

DateTime.Date property is not supported. Instead, you should use SqlFunctions.DatePart . This will result in the DATEPART TSQL method in the 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 2

Use the EntityFunction class to trim 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/

Answer 3

The problem is that the LINQ provider is trying to convert DateTime.Now.Date to a database method that it cannot execute by default. The purpose of comparing dates is to create an instance of DateTime with its time component set to the default value. You can get more information here and here .

Answer 4

Mayur Borad's answer (IMHO is more correct than the accepted answer) is deprecated:

System.Data.Entity.Core.Objects.EntityFunctions deprecated. Instead, use System.Data.Entity.DbFunctions .

 var today = DateTime.Today; // (Time already removed) var bla = context.Contacts .FirstOrDefault(x => DbFunctions.TruncateTime(x.ModifiedDate) == today);