为什么LINQ to SQL被一个扩展方法欺骗？现在怎么办？ [英] why is LINQ to SQL tricked by an extension method? what now?

问题描述

考虑我的事件类，并且我将数据库中的 DateTime 存储在UTC中作为UTC日期。我只想根据特定时区的当前日期返回一个过滤的范围 - 容易吗？

这个工作正常：

  IQueryable< Event> test1 = this.GetSortedEvents（）。Where（e => e.FinishDateTime.Date> = DateTime.UtcNow.Date）; 
  

这也可以正常工作：

  IQueryable< Event> test2 = this.GetSortedEvents（）。Where（e => e.FinishDateTime.AddHours（3）.Date> = DateTime.UtcNow.AddHours（3）.Date）; 
  

..另外满足我的时区要求。

$ b $所以在这里我想我可以把这个特定的转换移到这个扩展方法：

  public static DateTime RiyadhTimeFromUtc（this DateTime utcTime）
 {
 return utcTime.AddHours（3）; 
} 
  

这不工作：

  IQueryable< Event> test3 = this.GetSortedEvents（）。其中​​（e => e.FinishDateTime.RiyadhTimeFromUtc（）。Date> = DateTime.UtcNow.RiyadhTimeFromUtc（）。Date）; 
  

..我得到这个NotSupportedException：方法'System.DateTime RiyadhTimeFromUtc（System.DateTime）'有不支持翻译成SQL。

这显然是垃圾，因为当扩展方法中没有相同的代码时，编译器会高兴地将其转换为SQL。

我遇到某些类型和最近的DateTime的没有支持的SQL翻译问题。但是上面的测试和此链接证明了AddHours方法应该是在SQL翻译中支持。

如果有人可以告诉我我在这里做错了什么（或者这个问题的另一种解决方法），我将非常感激。

解决方案

您必须根据表达式树来考虑它，这是Linq-to-SQL如何解析您的查询将它变成SQL。

检查树时，会看到一个 DateTime 对象，然后检查是否调用了在它是支持的之一（添加， AddHours 等），所以当你直接使用该方法工作正常。

当您使用其他扩展方法时，无法查看该方法，以查看该方法的作用，因为该方法的正文的信息不在表达式树，它隐藏在IL中。因此，扩展方法的内容是否受支持并不重要，因为Linq-to-SQL无法解决内容的内容。

创建方法是封装和信息隐藏，这通常在应用程序开发中工作良好，但不幸的是，它隐藏了Linq-to-SQL的信息，您需要能够查看信息。

---

为了回应编辑过的问题 - 你如何解决这个问题？如果你想保持在Linq表达式中的日期计算，唯一可以做的是保持查询的效率是不要使用扩展方法，只需使用 AddHours（3）直接在 DateTime 对象上。

这是Linq不幸的限制之一。像许多事情一样，这是一个有些漏洞的抽象，虽然在一系列源上提供通用语法，但对于源的提供者可以/将支持哪些操作具有不同的限制和限制（例如，这将在Linq-to -Objects，因为它不需要翻译表达式树来执行它）。

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.

解决方案

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).

这篇关于为什么LINQ to SQL被一个扩展方法欺骗？现在怎么办？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！