如何在LINQ语句的where子句中查询和计算日期？

问题描述

我在下列代码中遇到问题。在我粘贴之前，请给我一些关于应该怎么做的历史。

I am having trouble with the following piece of code. Before I paste it, Let me give a bit of history on what should happen.

我现在有一个包含2个兴趣领域的模型，这是一个名字客户所在的订单，以及他/她放置的日期。将使用预先计算的日期来查询 dateplaced 字段（并且应该仅查询日期，而不是时间）。该查询计算在MondayOrder字段中发生的重复数量，并将它们组合在一起。现在，当我排除应该查询日期的where子句时，查询运行良好。但是，此查询的目标是根据订单日期计算下一周的订单金额。

I have a model containing 2 fields of interest at the moment, which is the name of the order the customer placed, and the date at which he/she placed it. A pre-calculated date will be used to query the dateplaced field (and should only query the dates , and not the time). The query counts the amount of duplicates that occur in the MondayOrder field, and groups them together. Now , when I exclude the where clause which should query the dates, the query runs great. However, The goal of this query is to count the amount of orders for the following week based on the date the order has been placed.

List<string> returnlist = new List<string>();
DateTime dt = getNextWeekMondaysDate().Date;
switch (day)
{
    case DayOfWeek.Monday:
    {
        var CountOrders =
           from x in Data.EntityDB.Orders
           group x by x.MondayOrder into m
           let count = m.Count()
           select new
           {
               MondayOrderItem = m.Key, Amount = count
           };

        foreach (var item in CountOrders)
        {
            returnlist.Add(item.MondayOrderItem + " : " +
               item.Amount);
        }
    }
    break;

getNextWeekMondaysDate（）方法有一个重载我可以使用，如果我提供一个日期，它将从给定的参数获得以下星期一的日期。问题是，LINQ不接受如下的查询：

The getNextWeekMondaysDate() method has an overload which I can use, where if I supply it a date, it will get the following Monday's date from the parameter given. The problem is though, LINQ does not accept queries such as the following:

var CountOrders =
    from x in Data.EntityDB.Orders
    where getNextWeekMondaysDate(x.DatePlaced.Value).Date == dt
    group x by x.MondayOrder into m
    let count = m.Count()
    select new { MondayOrderItem = m.Key, Amount = count };

这正是我必须实现的。这种情况有解决办法吗？

This is exactly what I must achieve. Is there any workaround for this situation?

更新

这是我尝试第二个查询时遇到的异常。

Here is the exception I get when I try the 2nd query.

LINQ to Entities不会识别方法'System.DateTime getNextWeekMondaysDate（System.DateTime）'方法，并且此方法无法转换为存储表达式。 p>

LINQ to Entities does not recognize the method 'System.DateTime getNextWeekMondaysDate(System.DateTime)' method, and this method cannot be translated into a store expression.

推荐答案

您无法直接执行此操作，因为EF查询提供程序无法将用户定义的方法调用转换为SQL。提供商可以将识别为可以转换为SQL以及。除非你写自己的查询提供者（理论上只是一个选项），否则只能使用这些方法不能表达的东西是非限制性的。

You cannot do this directly, as user-defined method calls cannot be translated to SQL by the EF query provider. The provider recognizes a limited set of .NET methods that can be translated to SQL and also a number of canonical functions as well. Anything that cannot be expressed using these methods only is off-limits unless you write your own query provider (which is only theoretically an option).

作为一个实际的解决方法，你可以在查询之前的代码中为 x.DatePlaced.Value 计算一个适当的范围，然后使用特定的 DateTime 其中子句。

As a practical workaround, you can calculate an appropriate range for x.DatePlaced.Value in code before the query and then use specific DateTime values on the where clause.

作为一个智力练习，请注意 被查询提供者识别，可用作表达式的一部分。所以这个可憎之处也应该是这样：

As an intellectual exercise, note that this method is recognized by the query provider and can be used as part of the expression. So this abomination should work too:

var CountOrders =
    from x in Data.EntityDB.Orders
    where EntityFunctions.AddDays(
        x.DatePlaced.Date.Value,
        (9 - DateAndTime.DatePart(DateInterval.WeekDay, x.DatePlaced.Value)) % 7)
        .Date == dt
    group x by x.MondayOrder into m
    let count = m.Count()
    select new { MondayOrderItem = m.Key, Amount = count };

这篇关于如何在LINQ语句的where子句中查询和计算日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！