涉及关联表的 Linq 查询

本文关键字：Linq 查询 关联 | 更新日期: 2023-09-27 18:37:20

IQueryable<Appeal> appeal = Repository.Appeals.Include("Case").Include("Case.Patient").Include("ShipmentAppeals").Where(ap => ap.CaseId == caseID);
IQueryable<ICollection<ShipmentAppeals>> shipmentAppeals = appeal.Select(ap => ap.ShipmentAppeals)

ShipmentAppeal是Appeal和Shipping实体之间的关联表。

我想从存储库中获取这些货件。货件 ID 存在于上述货件申诉集合中的货件。

我写的查询如下。它确实有效，但我想避免每个循环：

 foreach (ICollection<ShipmentAppeals> sAppealOuter in shipmentAppeals.ToList())
        {
            foreach (ShipmentAppeals sAppealInner in sAppealOuter)
            {
                Shipment shipment = Repository.Shipments.First(s => s.ID == sAppealInner.ShipmentID);
                    caseShipments.Add(shipment);
                }
            }
        }

涉及关联表的 Linq 查询

首先，我们可以使用 SelectMany 将一系列上诉集合转换为上诉。

接下来，与其逐一检查并进行数据库查询以获取货物，不如只使用单个查询来获取所有上诉的所有货物。 这可以通过Join来完成。 （或者，如果您有不想要的重复货件，您可以使用GroupJoin并选择每个组中的第一项。

var caseShipments = shipmentAppeals.SelectMany(repository => repository)
    .Join(Repository.Shipments, appeal => appeal.ShipmentID
        , shipment => shipment.ID, (appeal, shipment) => shipment);

或者在查询语法中：

var caseShipments = from repository in shipmentAppeals
                    from appeal in repository
                    join shipment in Repository.Shipments
                    on appeal.ShipmentID equals shipment.ID
                    select shipment;
请注意，由于延迟执行，

整个查询将只进行一次数据库往返，因为每个查询都用于编写另一个查询，而不是首先执行。

相关文章：