LINQ to Entities - 在我的示例中排除部分发货订单的 WHERE 子句

Question

我有一个订单表。一个订单中可以包含多个商品，每个商品在不同的时间发货。我想获取所有订单的列表，不包括部分发货或订单。换句话说，我需要获取所有已完全发货的订单的列表。我可能知道如何在 T-SQL 中执行此操作。但我正在尝试使用 LINQ-to-Entities (EF4/.Net 4.0/C#) 来完成此任务。

考虑以下数据：

PK      OrderID      Item       Status
1       00001        TV         Shipped
2       00001        TABLET     Shipped
3       00002        BLURAYPL   Not Shipped
4       00002        MOBILEPH   Shipped
5       00002        XBOX       Shipped
6       00003        PAPER      Shipped
7       00003        PENCIL     Shipped

目标是获得 00001 和 00003 作为输出。

这是我到目前为止所得到的，明显简化了：

using (MyDBEntities dbcontext = new MyDBEntities())
{
  var WashingtonClients = from a in dbcontext.CustomerMasterTable
                    where a.City == "Washington"
                    select a.CustomerID;

 var ShippedOrdersToWashingtonClients = from o in dbcontext.OrderDetail
                     where WashingtonClients.Contains(o.CustomerID)
                     && o.Status.ToUpper() == "SHIPPED"
                     //how to exclude Partially Shipped orders here???
                     select o.OrderID;
}

如何以排除包含单个未发货商品的订单的方式构建第二个查询？非常感谢您抽出时间。

Answer 1

假设您的数据有假列表：

var orderDetails = new List<OrderDetail>
    {
        new OrderDetail{ ID = 1, Item = "TV", OrderID = "00001", Status = "Shipped"},
        new OrderDetail{ ID = 2, Item = "TABLET", OrderID = "00001", Status = "Shipped"},
        new OrderDetail{ ID = 3, Item = "BLURAYPL", OrderID = "00002", Status = "NotShipped"},
        new OrderDetail{ ID = 4, Item = "MOBILEPH", OrderID = "00002", Status = "Shipped"},
        new OrderDetail{ ID = 5, Item = "XBOX", OrderID = "00002", Status = "Shipped"},
        new OrderDetail{ ID = 6, Item = "PAPER", OrderID = "00003", Status = "Shipped"},
        new OrderDetail{ ID = 7, Item = "PENCIL", OrderID = "00003", Status = "Shipped"}
    };

那么您的 linq 查询应该如下所示：

var result = orderDetails
    .GroupBy(o => o.OrderID)
    .Where(g => g.All(i => i.Status == "Shipped"))
    .Select(g => g.Key);

这样您就会得到几个字符串 - "00001" 和 "00003" 作为结果。

因此，对于真正的数据库查询，您可以编写如下内容：

dbContext.OrderDetails
    .Where(o => WashingtonClients.Contains(o.CustomerID))
    .GroupBy(o => o.OrderID)
    .Where(g => g.All(i => i.Status == "Shipped"))
    .Select(g => g.Key);