Date/time operations are not supported

Most direct operations with DateTime objects are not supported.

Problem

It is often necessary to produce operations over DateTime properties, such as, for example, computing the difference between two columns. In the past (Entity Framework pre-Core), there was a class called DbFunctions (https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions(v=vs.113).aspx) that had some useful extension methods that we could use for this.

Unfortunately, as of Entity Framework Core 1, this class is not included. This means that the following queries do not work or will not work as expected–in this example, AddDays will be executed client-side, not in the database:

var age = ctx
  .Blogs
  .Select(b => DateTime.UtcNow – b.CreationDate)
  .ToList();
var oneWeekAfterCreation = ctx
  .Blogs
  .Select(b => b.CreationDate.AddDays(7))
  .ToList();

How to solve it…

For queries that need to perform complex date/time operations in the database side, we need to resort to plain SQL; there is no way around it. For example, the first of these queries could be rewritten as follows:

var age = ctx
  .Blogs
  .FromSql("SELECT GETUTCDATE() – CreationDate AS CreationDate FROM Blogs")
  .Select(b => b.CreationDate)
  .ToList();

And the second could be rewritten as follows:

var oneWeekAfterCreation = ctx
  .Blogs
  .FromSql("SELECT DATEADD(day, 7, CreationDate) AS CreationDate FROM Blogs")
  .Select(b => b.CreationDate)
  .ToList();