Object functions fail within LINQ to Entities Expressions

Question

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.

Answer 1

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

Answer 2

It's interesting to note the different query generated by LINQ-to-SQL and by Entity Framework if we use directly DateTime.Now in a 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 here is that LINQ-to-SQL considers the DateTime.Now something that must be calculated .NET-side and sent as a parameter of the query, while EF considers the DateTime.Now something that can be calculated SQL-side. From this clearly we have that in LINQ-to-SQL the DateTime.Now.AddDays() "works" (because that part of the expression is fully evaluated .NET-side) while on EF it doesn't, because the SQL doesn't have an AddDays() that works "exactly" as the .NET AddDays() (the DATEADD works with integers, not floating points).

Is it more correct what the LINQ-to-SQL does or what the EF does? I'll say that it's more correct what the EF does (even if it's more "strange")...

Example: what would happen if the .NET app and the SQL app were on two different timezones (so with different times)... Would it be more correct that the DateTime.Now was the .NET time or the SQL time? I think the second one (but I repeat, if I had discovered this as a "bug" in my app, even I would have made a big ooooooh).

As a sidenote (not very important), you shouldn't calculate twice the date in the same place and think that they'll be equal. This time you used the full date, so no problems, but if you had taken only the DateTime.Now.Date, and if you code was executed around midnight, perhaps, very very perhaps the two dates would be different, because one is calculated on 23:59:59.9999999 while the other is calculated on 00:00:00.0000000 of the next day.

Answer 3

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

So DateTime.AddDays method is not a either canonical or database function and cannot be converted to proper command tree node for further execution. Normally you should use SqlFunctions or EntityFunctions in your queries. But still there is a way to invoke custom database functions by defining them in your .edmx file.

Also take into consideration that LINQ to Entities does not support some of standard query methods: Aggregate, Last, etc. and a lot of overloads like Select<TSource, TResult>(IQueryable<TSource>, Expression<Func<TSource, Int32, TResult>>).
Full 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);