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?

you can use `var rslt = context.Visitors.Where(v => v.VisitDate.Date == DateTime.Now).ToList()` — Saravanan, May 24 '13 at 13:35
@saravanan I think you may mean `Datetime.Today` instead of `DateTime.Now`? — Joachim Isaksson, May 24 '13 at 13:37

score 13 · Answer 1 · answered May 24 '13 at 13:42

13

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/

answered May 24 '13 at 13:42

Mayur Borad

1,295
9
23

Please see answer from Tim S below for the latest implementation using DbFunctions instead – Savage Sep 14 '18 at 08:32

score 11 · Accepted Answer · edited Nov 06 '13 at 21:29

11

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();

edited Nov 06 '13 at 21:29

keeehlan

7,874
16
56
104

answered May 24 '13 at 13:38

MarcinJuraszek

124,003
15
196
263

2

Thanks for you answer, but I get NotSupportedException again. But message contains other information: The specified method 'System.Nullable`1[System.Int32] DatePart(System.String, System.Nullable`1[System.DateTime])" on the type "System.Data.Objects.SqlClient.SqlFunctions" cannot be translated into a LINQ to Entities store expression. Maybe it's PostgreSQL's problem? – max May 24 '13 at 15:20

score 5 · Answer 3 · answered Dec 13 '14 at 05:29

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);

score 3 · Answer 4 · edited May 23 '17 at 11:48

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.

edited May 23 '17 at 11:48

Community

1
1

answered May 24 '13 at 13:37

Erik Schierboom

16,301
10
64
81

Using DateTime in LINQ to Entities

4 Answers4

Linked

Related