Date' Is Not Supported in Linq to Entities. Only Initializers, Entity Members, and Entity Navigation Properties Are Supported

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

DateTime.Date cannot be converted to SQL. Use EntityFunctions.TruncateTime method to get date part.

var eventsCustom = eventCustomRepository
.FindAllEventsCustomByUniqueStudentReference(userDevice.UniqueStudentReference)
.Where(x => EntityFunctions.TruncateTime(x.DateTimeStart) == currentDate.Date);

UPDATE: As @shankbond mentioned in comments, in Entity Framework 6 EntityFunctions is obsolete, and you should use DbFunctions class, which is shipped with Entity Framework.

The specified type member 'Date' is not supported in LINQ to Entities Exception

LINQ to Entities cannot translate most .NET Date methods (including the casting you used) into SQL since there is no equivalent SQL.

The solution is to use the Date methods outside the LINQ statement and then pass in a value. It looks as if Convert.ToDateTime(rule.data).Date is causing the error.

Calling Date on a DateTime property also cannot be translated to SQL, so a workaround is to compare the .Year .Month and .Day properties which can be translated to LINQ since they are only integers.

var ruleDate = Convert.ToDateTime(rule.data).Date;
return jobdescriptions.Where(j => j.Deadline.Year == ruleDate.Year 
                       && j.Deadline.Month == ruleDate.Month 
                       && j.Deadline.Day == ruleDate.Day);

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

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

EntityFramework cannot convert DateTime.Date to SQL. So, it fails to generate expected SQL. Instead of that you can use EntityFunctions.TruncateTime() or DbFunctions.TruncateTime()(based on EF version) method if you want to get Date part only:

 _dbEntities.EmployeeAttendances
            .Where(x => EntityFunctions.TruncateTime(x.DailyDate) == DateTime.Now.Date)
            .ToList();

Additional info:

EntityFunctions methods are called canonical functions. And these are a set of functions, which are supported by all Entity Framework providers. These canonical functions will be translated to the corresponding data source functionality for the provider. Canonical functions are the preferred way to access functionality outside the core language, because they keep the queries portable.

You can find all canonical functions here and all Date and Time Canonical Functions here.

Update:

As of EF6 EntityFunctions has been deprecated for System.Data.Entity.DbFunctions.

Entity Framework refusing Date comparison The specified type member 'Date' is not supported in LINQ to Entities

For better performance you have to use comparison operators. You have date without time, so add one day and write appropriate LINQ query.

var nextDay = date.AddDays(1);

List<int> Ids = context.Record
            .Where(rec => rec.Date >= date && rec.Date < nextDay)
            .Select(rec => rec.Id)
            .ToList();

Only in this case indexes

The specified type member 'Title' is not supported in LINQ to Entities

Title is a property in your entity. In your table there is not such column. Entity Framework can not convert your code to Sql. So, you must change your code as:

  var query = from item in db.ProjectManagers
              where item.ProjectID == projectID
              select new UserListItem
              {
                   ID = item.UserID,
                   Title = item.User.Firstname + " " + item.User.Lastname;
              };

But, I suggest you to select FirstName and LastName, and when you want to get the value of Title your getter accessor will work:

  var query = from item in db.ProjectManagers
              where item.ProjectID == projectID
              select new UserListItem
              {
                   ID = item.UserID,
                   Firstname = item.User.Firstname
                   Lastname =  item.User.Lastname;
              };

The specified type member 'Date' is not supported in LINQ to Entities

query = query.Where(x => x.From_Date.Date >= FromDate_ConvertedToDateTime1.Date);

could be replaced with:

var fromDate = FromDate_ConvertedToDateTime1.Date;
query = query.Where(x => x.From_Date >= fromDate);

It has the same logical meaning, will generate efficient SQL and (most importantly) it won't experience the error you are seeing.

For less than or equal to, it is slightly more complicated - but not markedly so.

query = query.Where(x => x.To_Date.Date <= ToDate_ConvertedToDateTime1.Date);

could be replaced with:

var dayAfterToDate = ToDate_ConvertedToDateTime1.Date.AddDays(1)
query = query.Where(x => x.To_Date < dateAfterToDate);

That logic might seem odd at first glance - but if it is before the date after the ToDate that is logically equivalent to being on or before the ToDate (ignoring the time component).

Repeat the same process for the other queries - they will follow the exact same pattern.

Only initializers, entity members, and entity navigation properties are supported when including child table in query

Ok, so the problem is that you can't use interfaces when defining EF Entity objects and relationships.

My bad.

---

Related Topics