'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.

Opaque

Show us the Logs class and the table it's being mapped to. – Gey

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 – Opaque

Answer 1

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.

Opaque

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

Answer 2

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.

Scud

You will also have to Truncate your LogDate.Date property – Biogeography

@Biogeography LogDate is a DateTime which means that LogDate.Date is "truncated" – Cistaceous

+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. – Tibia

Didn't know about the functions. Fantastic. – Mesarch

BTW -- That function is now obsolete and has been moved to another assembly. Use 'DbFunctions.TruncateTime(t.Timestamp)' instead. – Bulbiferous

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! – Teacup

Answer 3

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

Use below

DbFunctions.TruncateTime(t.Timestamp)

Peripeteia

Answer 4

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;

Brunette

Answer 5

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

Demagnetize

Answer 6

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

EntityFunctions.TruncateTime(t.Timestamp) == LogDate

like mike did

Wulfenite

Answer 7

Try this:

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

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

Variolous

Answer 8

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;

Trilateration

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. – Trilateration