如何在LINQ的子表上创建where条件 [英] How do I create a where condition on a sub table in LINQ

问题描述

这是对我有一个Orders集合，每个Order都有一个OrderItems集合，每个OrderItem都有一个PartId.使用LINQ如何实现以下SQL语句:

I have a collection of Orders, each Order a collection of OrderItems, and each OrderItem has a PartId. Using LINQ how do I implement the following SQL statements:

1)选择所有具有特定部件ID的订单

1) Select all the orders that have a specific part ID

SELECT *
FROM Order
WHERE Id in (SELECT OrderId FROM OrderItems WHERE PartId = 100)

2)选择Order.OrderNumber和OrderItem.PartName

2) Select the Order.OrderNumber and OrderItem.PartName

SELECT Order.OrderNumber, OrderItem.PartName
FROM Order INNER JOIN OrderItem ON Order.Id = OrderItem.OrderId
WHERE OrderItem.partId = 100

3)选择Order.OrderNumber和整个OrderItem详细信息:

3) SELECT the Order.OrderNumber and the whole OrderItem detail:

SELECT Order.OrderNumber, OrderItem.*
FROM Order INNER JOIN OrderItem ON Order.Id = OrderItem.OrderId
WHERE OrderItem.partId = 100

推荐答案

实际代码应为

1)

var orders = from o in Orders
             where o.OrderItems.Any(i => i.PartId == 100)
             select o;

Any()方法返回布尔值，就像SQL的"in"子句一样.这将获取所有PartItem为100的OrderItems的所有订单.

The Any() method returns a bool and is like the SQL "in" clause. This would get all the order where there are Any OrderItems what have a PartId of 100.

2a)

// This will create a new type with the 2 details required 
var orderItemDetail = from o in Orders
                      from i in Orders.OrderItems
                      where i.PartId == 100
                      select new()
                      {
                          o.OrderNumber,
                          i.PartName
                      }

这两个from子句就像一个内部联接.

The two from clauses are like an inner join.

2b)

// This  will populate the OrderItemSummary type
var orderItemDetail = from o in Orders
                      from i in Orders.OrderItems
                      where i.PartId == 100
                      select new OrderItemSummary()
                      {
                          OriginalOrderNumber = o.OrderNumber,
                          PartName = i.PartName
                      }

3)

// This will create a new type with two properties, one being the
// whole OrderItem object.
var orderItemDetail = from o in Orders
                      from i in Orders.OrderItems
                      where i.PartId == 100
                      select new()
                      {
                          OrderNumber = o.OrderNumber,
                          Item = i
                       }

由于"i"是OrderItem类型的对象，因此将Item创建为OrderItem.

Since "i" is an object of Type OrderItem, Item is create as an OrderItem.

这篇关于如何在LINQ的子表上创建where条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！