Compare Dates using LINQ to Entities (Entity Framework)

Question

I need to return a list of items from my database that expire at a pre-specified time on the date supplied by the item. My erroneous code is as follows:

return All().Where(o => new DateTime(o.expiry_date.Year, o.expiry_date.Month, o.expiry_date.Day, 17, 30, 0) >= DateTime.Now)

The error I get is:

Only parameterless constructors and initializers are supported in LINQ to Entities

Does anyone know how I can fix this?

Answer 1

Use EntityFunctions instead. Maybe CreateDateTime method.

So maybe like this:

return All().Where(o => EntityFunctions.CreateDateTime(o.expiry_date.Year, o.expiry_date.Month, o.expiry_date.Day, 17, 30, 0) >= DateTime.Now)

Update:

When using EF6, use DbFunctions instead.

Answer 2

You can use:

var result = await db.Articles
          .Where(TruncateTime(x.DateCreated) >= EntityFunctions.TruncateTime(DateTime.Now))
          .ToListAsync();

Answer 3

Please use:

return All().Where(o => EntityFunctions.CreateDateTime(o.expiry_date.Year, o.expiry_date.Month, o.expiry_date.Day, 17, 30, 0) >= DateTime.Now)