why is LINQ to SQL tricked by an extension method? what now?

Question

Consider my Event class, and that i store DateTime's in the DB as UTC dates. I simply want to return a filtered range based on the current date in a particular time zone - easy right?

This works fine:

IQueryable<Event> test1 = this.GetSortedEvents().Where(e => e.FinishDateTime.Date >= DateTime.UtcNow.Date);

This also works fine:

IQueryable<Event> test2 = this.GetSortedEvents().Where(e => e.FinishDateTime.AddHours(3).Date >= DateTime.UtcNow.AddHours(3).Date);

.. and additionally meets my time zone requirements.

So here i am thinking i can move this specific conversion out to this extension method:

    public static DateTime RiyadhTimeFromUtc(this DateTime utcTime)
    {
        return utcTime.AddHours(3);
    }

This does NOT work:

IQueryable<Event> test3 = this.GetSortedEvents().Where(e => e.FinishDateTime.RiyadhTimeFromUtc().Date >= DateTime.UtcNow.RiyadhTimeFromUtc().Date);

.. and i get this NotSupportedException: Method 'System.DateTime RiyadhTimeFromUtc(System.DateTime)' has no supported translation to SQL.

This is obviously rubbish as the compiler happily converted it to SQL when the identical code was NOT in the extension method.

I've run into the "has no supported translation to SQL" problem before with certain types and most recently DateTime's. But my tests above and this link prove that the AddHours method should be supported in the SQL translation.

If someone could tell me what i'm doing wrong here (or a different workaround approach to this problem) i'd be really grateful.

Answer 1

You have to think about it in terms of an expression tree, which is how Linq-to-SQL parses your query to turn it into SQL.

When examining the tree it will see a DateTime object and then check whether the method called on it is one of the supported ones (Add, AddHours, etc.) so when you use the method directly it works fine.

When you use some other extension method, it can't go and look inside that method to see what it does, as the information about that method's body isn't in the expression tree, it's hidden in the IL. So it doesn't matter whether the contents of the extension method are supported, because Linq-to-SQL can't work out what the contents are.

The point of creating methods is encapsulation and information hiding, which typically works well in application development, but unfortunately here it's hiding the information from Linq-to-SQL who you need to be able to see the information.

---

In response to the edited question - how do you solve this? If you want to keep the date calculation within the Linq expression, the only thing you can do to keep the query efficient is not to use an extension method, and just use AddHours(3) directly on the DateTime object.

It's one of the unfortunate limitations of Linq. Like many things it's a somewhat leaky abstraction which, while providing common syntax over a range of sources, has different limitations and restrictions on which operations the provider for the source can/will support (for example, this would work perfectly fine in Linq-to-Objects as it doesn't need to translate the expression tree to execute it).

Answer 2

It seems like for LINQ-to-SQL to be able to figure out that RiyadhTimeFromUtc was really just a call to AddHours(3) it would have to do some pretty sophisticated analysis of your code. Say you wrote:

public static DateTime RiyadhTimeFromUtc(this DateTime utcTime)
{
    if (Random.GetInt() < 99) {
       return utcTime.AddHours(3);
    } else {
       return utcTime.AddDays(5);
    }
}

How would it translate that to SQL? See this discussion for some more info.