Object functions not executed in LINQ to Entities expressions

Question

PAY ATTENTION . I know how to get around this. I am NOT looking for a solution, I am looking for clarity of the problem itself.

class Program { static void Main(string[] args) { using (var context = new TestDbContext()) { var eventsFound = context.Events .Where(e => e.EventDate >= DateTime.Now.AddDays(-1) && e.EventDate <= DateTime.Now.AddDays(+1) ) .ToList(); } } } public class TestDbContext : DbContext { public DbSet<Event> Events { get; set; } } public class Event { public int EventId { get; set; } public DateTime EventDate { get; set; } } 

Ok, so the above program failed:

 LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression. 

Why LINQ cannot distinguish a database function from an object function. The system must be smart enough to understand that the AddDays function is part of a DateTime object. Then it must first enable this function, and then after all the functions in the request have been resolved, convert to SQL and execute them with the database.

I am sure this is much more complicated, but I would like to understand why.

========= EDIT ===============

Thus, the above was not really a good example, since "AddDays" is a function that exists in both .NET and SQL. How about when I change it to an independent function where ambiguity cannot exist.

t

 public class Event { public int EventId { get; set; } public DateTime EventDate { get; set; } public DateTime ReturnDateNowExample() { return DateTime.Now; } } static void Main(string[] args) { var myEvent = new Event {EventDate = new DateTime(2013, 08, 28)}; using (var context = new TestDbContext()) { var eventsFound = context.Events .Where(e => e.EventDate >= myEvent.ReturnDateNowExample() ) .ToList(); } } 

And this is if the DateTime object is ambiguous, and then replace it with a string / int object.

Answer 1

The reason for this has nothing to do with the fact that it is “smart,” and is more related to how Linq works. Linq uses something called an expression tree. Essentially, it compiles your expression into a dataset, which is then converted by the translation layer to SQL.

The reason this does not work is because it is a where clause, and the where clause must be executed in SQL to be exact. It cannot be executed in C # code on the back side, at least not without silence to return all the rows of the table, which would not be desirable functionality ... and if so, you can say that this is done explicitly.

The Entity Framework provides a set of functions for working with dates that can be converted directly to SQL, and they are in the EntityFunctions namespace. These cards belong to the so-called "canonical functions", which means that SQL has 1: 1 translations. Linq to Sql passes the client side where the where parameter is the parameter, but this may or may not be the desired value, because you may need the server-side value rather than the calculated value on the client side .. so L2S will give you an unexpected result to some situations.

Simply put, you need special expression functions to be able to convert to SQL, and, unfortunately, some old standard .NET classes that dateTime classes do not work.

You can find the following helpful articles:

http://blogs.msdn.com/b/charlie/archive/2008/01/31/expression-tree-basics.aspx

http://tomasp.net/blog/linq-expand.aspx/

http://social.msdn.microsoft.com/Forums/en-US/21a9c660-13e5-4751-aa51-6519bddae044/enterprise-framework-linq-queries-failing

Answer 2

It is interesting to note the different query created by LINQ-to-SQL and Entity Framework if we use DateTime.Now directly in the query:

LINQ to SQL:

 WHERE ([t0].[EventDate] >= @p0) AND ([t0].[EventDate] <= @p1) 

Entity Framework

 WHERE ([Extent1].[EventDate] >= CAST( SysDateTime() AS datetime2)) AND ([Extent1].[EventDate] <= CAST( SysDateTime() AS datetime2)) 

The difference is that LINQ-to-SQL considers DateTime.Now something that should be calculated by the .NET side and sent as a query parameter, while EF considers DateTime.Now something that SQL can calculate - side. From this it is clear that DateTime.Now.AddDays() "works in LINQ-to-SQL (since this part of the expression is fully evaluated by the .NET side), but this does not work on EF because SQL does not have AddDays() , which works "Exactly" like .NET AddDays() ( DATEADD works with integers, not floats).

Is it true what LINQ-to-SQL does or what EF does? I will say that it is more correct what EF does (even if it is more "weird") ...

Example: what happens if the .NET application and the SQL application are in two different time zones (so with different times) ... Would it be more correct for DateTime.Now be .NET time or SQL time? I think the second one (but I repeat, if I found this to be an “error” in my application, even I would make a big ooooooh).

As a side element (not very important), you should not double-calculate the date in one place and consider that they will be equal. This time you used the full date, so no problem, but if you only took DateTime.Now.Date , and if the code was executed around midnight, maybe very, maybe the two dates would be different, because one is designed for 23: 59: 59.9999999, and the other is for 00: 00: 00.0000000 the next day.

Answer 3

The problem is that EF is trying to convert and then execute your query on the SQL side. And there is no equivalent to System.DateTime.AddDays .

Thus, the DateTime.AddDays method is neither a canonical nor a database function and cannot be converted to the corresponding node command tree for further execution. Typically, you should use SqlFunctions or EntityFunctions in your queries. But there is still a way to invoke user-defined database functions by defining them in a .edmx file.

Also note that LINQ to Entities does not support some standard query methods: Aggregate , Last , etc. And a lot of overloads, for example Select<TSource, TResult>(IQueryable<TSource>, Expression<Func<TSource, Int32, TResult>>) .
A complete list of supported operators is here .

---

To fix this, you should use EntityFunctions.AddDays : Replace DateTime.Now.AddDays(+1) with EntityFunctions.AddDays(DateTime.Now, 1);

Answer 4

You can also save these days in a variable:

 DateTime yesterday = DateTime.Now.AddDays(-1); DateTime tomorrow = DateTime.Now.AddDays(+1); 

and in the Where section:

 .Where(e => e.EventDate >= yesterday && e.EventDate <= tomorrow )