c# - 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);