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

Question

I am trying to execute the following code and am receiving an error

public List<Log> GetLoggingData(DateTime LogDate, string title)
{
     var context = new LoggingEntities();
     var query = from t in context.Logs

           where t.Title == title 
           && t.Timestamp == LogDate

           select t;
     return query.ToList();
}

The error I'm receiving is "The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported." I have tried various attempts of casting everythign to a string, only comparing the date part, but can't seem to get the right combinaation. Any help is greatly appreciated.

I am using EFv1 with .net 3.5. The log class is just a log table from the enterprise library with the title as a String type and Timestamp as a datetime field. I just want to compare the datepart — mikemurf22, Aug 16 '11 at 19:41

score 90 · Answer 1 · edited Nov 15 '16 at 21:40

90

If you are using EF 6.0+, you can use DbFunctions.TruncateTime(DateTime?) :

var query =
    from t in context.Logs
    where t.Title == title 
    && DbFunctions.TruncateTime(t.Timestamp) == LogDate.Date
    select t;

Note: For earlier version of EF where DbFunctions isn't available, EntityFunctions.TruncateTime(DateTime?) can be used instead.

edited Nov 15 '16 at 21:40

Xiaoy312

14,292
1
32
44

answered Aug 01 '12 at 07:48

Henrik Stenbæk

3,982
5
31
33

You will also have to Truncate your LogDate.Date property – invalidusername Dec 31 '12 at 20:47
1

@invalidusername LogDate is a DateTime which means that LogDate.Date is "truncated" – Henrik Stenbæk Jan 09 '13 at 05:26
3

+1 for you sir. I couldn't use the accepted answer in my case as I did a 'greater than' comparison, in which you can't compare on Day, Month and Year separately. – HTBR Feb 26 '13 at 15:33
2

Didn't know about the functions. Fantastic. – Lotok Jun 07 '13 at 09:51
19

BTW -- That function is now obsolete and has been moved to another assembly. Use 'DbFunctions.TruncateTime(t.Timestamp)' instead. – Bob Jan 23 '14 at 15:57
Make sure to put your date in a variable. I did a compare like 'DbFunctions.TruncateTime(t.Timestamp) == DateTime.Now.Date', but that doesn't work. 'var today = DateTime.Now.Date; DbFunctions.TruncateTime(t.Timestamp) == today works! – kloarubeek Aug 11 '19 at 21:18

score 25 · Accepted Answer · answered Aug 16 '11 at 20:05

Not the greatest solution, but it works. For a variety of reasons, I have to use .net 3.5 at this point and modifying the database would be difficult. Anyways, here is a solution that works:

            var query = from t in context.Logs
                      where t.Title == title 
                      && t.Timestamp.Day == LogDate.Day
                      && t.Timestamp.Month == LogDate.Month
                      && t.Timestamp.Year == LogDate.Year
                      select t;

Not the most elegant solution, but it is effective.

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. — SAR, May 07 '17 at 08:30

score 3 · Answer 3 · answered Oct 26 '15 at 07:12

3

EntityFunctions.TruncateTime(t.Timestamp) is obsolete from EF6.

Use below

DbFunctions.TruncateTime(t.Timestamp)

answered Oct 26 '15 at 07:12

Rahul Nikate

6,192
5
42
54

score 2 · Answer 4 · answered Jun 13 '13 at 17:34

Always use EntityFunctions.TruncateTime() for both x.DateTimeStart and LogDate. such as :

var query = from t in context.Logs
              where t.Title == title 
              && EntityFunctions.TruncateTime(t.Timestamp) == EntityFunctions.TruncateTime(LogDate)
              select t;

score 1 · Answer 5 · edited Dec 20 '12 at 16:47

Correct me if I'm wrong, but in mikemurf22's example, it would need to check each part of the date component, and potentially a lot more server processing?

Anyway, I stumbled across this problem, and this is my solution.

Assuming that you're going to be passing in the date component only, you can find the last minute of the day that you pass in, and use the where clause to define the range.

public List<Log> GetLoggingData(DateTime LogDate, string title)
{
    DateTime enddate = new DateTime(LogDate.Year, LogDate.Month, LogDate.Day, 23, 59, 59)

    var query = from t in context.Logs
                where t.Timestamp >= date
                where t.Timestamp <= enddate
                select t;

    return query.ToList();
}

score 0 · Answer 6 · edited Sep 16 '15 at 11:45

0

Convert LongDate to .ToShortDateStringand then you can use it this way:

EntityFunctions.TruncateTime(t.Timestamp) == LogDate

like mike did

edited Sep 16 '15 at 11:45

fbarikzehy

4,885
2
33
39

answered Oct 30 '13 at 10:48

sansalk

4,595
2
36
37

score 0 · Answer 7 · edited Jan 24 '17 at 20:15

0

Try this:

var calDate = DateTime.Now.Date.AddDays(-90);

var result = return (from r in xyz where DbFunctions.TruncateTime(r.savedDate) >= DbFunctions.TruncateTime(calDate)

edited Jan 24 '17 at 20:15

Jemar Jones

1,491
2
21
27

answered Aug 20 '15 at 13:47

user3783446

425
5
11

algreat · Answer 8 · 2014-03-26T14:32:25.150

-1

You can use this hack:

DateTime startDate = LogDate.Date;
DateTime endDate = LogDate.Date.AddDays(1);

var query = from t in context.Logs
            where t.Title == title 
                  && t.Timestamp >= startDate 
                  && t.Timestamp < endDate
            select t;

edited Mar 26 '14 at 14:32

answered Jul 26 '12 at 08:51

algreat

8,592
5
41
54

I corrected it a little. Of cource you can't use AddDays method inside your query. But you can move it outside. So workaround still works. – algreat Mar 26 '14 at 14:35

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

8 Answers8

Linked

Related