Why is LINQ to SQL tricked by extension method? Now what?

Question

Consider my Event class and save the DateTime in the database as UTC dates. I just want to return the filtered range based on the current date in a particular time zone - easy?

This works great:

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

This also works great:

 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, I think I can move this particular conversion 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: the method 'System.DateTime RiyadhTimeFromUtc (System.DateTime)' does not support SQL translation.

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

I ran into the “no translation support in SQL” problem before with certain types, and more recently with DateTime. But my tests above and this link prove that the AddHours method must be supported in SQL translation.

If someone can tell me what I'm doing wrong here (or another approach to solving this problem), I would be very grateful.

Answer 1

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

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

When you use any other extension method, it cannot go and look inside this method to see what it does, since information about this method is not in the expression tree, it is hidden in IL. Therefore, it does not matter if the contents of the extension method are supported, since Linq-to-SQL cannot determine what the content is.

The point of creating methods is encapsulating and hiding information, which usually works well in application development, but, unfortunately, here it hides the information from Linq-to-SQL that you need to view the information.

---

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

This is one of Linq's unfortunate limitations. Like many things, this is a slightly leaking abstraction, which, while providing a common syntax for a number of sources, has various limitations and restrictions by which operators supporting the source can / will support (for example, this will work fine in Linq-to -Objects, since you don’t need to translate the expression tree to execute it).

Answer 2

It seems that for LINQ-to-SQL you can understand that RiyadhTimeFromUtc was really just a call to AddHours(3) , it would have to do a rather complicated 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 to translate this into SQL? See this discussion for more information.