Object functions fail within LINQ to Entities Expressions
c#, linq, linq-to-entities, sql
Solution
The reason for this has nothing to do with it being "clever", and more to do with the way Linq works. Linq uses something called an "expression tree". Basically, it compiles your expression down to a set of data, which is then converted by a translation layer into SQL.
The reason this doesn't work is because this is in a where clause, and the where clause must be executed in SQL to be accurate. It cannot be executed in C# code on the back end, at least not without silently returning all rows of the table, which would not be a desired functionality... and if it is, you can tell it to do this explicitly.
Entity Framework provides a set of functions for working with dates that CAN be converted directly to SQL, and these are in the EntityFunctions namespace. These map to so-called "canonical functions" which just means that there are 1:1 translations to SQL. Linq to Sql passes the client-side evaluated where clause as a parameter, but this may or may not be the desired value because you may need a server-side value rather than a client-side calculated value.. thus L2S will give you unexpected results in some situations.
Simply put, you need special expression functions to be able to convert to SQL, and just any old standard .NET classes won't work, which the DateTime classes are, unfortunately.
You might find the following articles useful:
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
Problem
PLEASE NOTE: I know how to work around this. I am NOT looking for a solution, I am looking for clarity on 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 fails with: ``` LINQ to Entities does not recognize the method 'System.DateTime AddDays(Double)' method, and this method cannot be translated into a store expression. ``` Why can LINQ not tell the difference between a database function and an object function. The system should be clever enough to realize that the AddDays function is part of the DateTime object. It should then first resolve that function and then once all functions in the query are resolved, convert to SQL and execute that against the database. I'm sure it's a lot more complicated than that but I would like to understand why. ========= EDIT ============== So the above was not actually a good example as "AddDays" is a function which exists in both .NET and SQL. What about when I change it to a self defined function where no ambiguity could exist. ie: ``` 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 it if is the DateTime object that is ambiguous, then replace with a string/int object.