具有两个多对多关系的Linq查询

本文关键字：关系 Linq 查询 两个 | 更新日期: 2023-09-27 18:01:48

我设置了以下实体，我想查询:

  • StoreCapability。一个商店可以有多种功能(例如，卖巧克力，是大的)-但不是必须有。
  • 。一个优惠可能需要多个存储功能才能有效，但不需要任何要求。

Store -[m2m]- StoreCapability -[m2m]- Offer

所以有五张表。

我希望能够使用linq获得以下内容:

  • 给定一个优惠，它对
    • 有效的商店列表
  • 给定一个商店，可用的优惠列表

使用SQL可以从Store，到StoreCapability，到Offer，然后按Offer和Store分组，并且只获得具有count()等于Offer所具有的需求数量的Store。然而，我不知道从哪里开始使用Linq，因为许多许多表被实体框架隐藏起来。请问有人能告诉我怎么做吗?

SQL语句可以类似如下:

SELECT Offers.Id, offers.Name, Stores.Id, Stores.Name FROM Offers
--join to the capabilities that this offer needs
LEFT OUTER JOIN StoreCapabilityOffers offerRequirements ON Offers.Id = offerRequirements.Offer_Id
--join to stores which have capability
LEFT OUTER JOIN StoreCapabilities ON offerRequirements.StoreCapability_Id = StoreCapabilities.Id
--join to stores
LEFT OUTER JOIN StoreStoreCapabilities storeCap ON offerRequirements.StoreCapability_Id = storeCap.StoreCapability_Id
LEFT OUTER JOIN Stores on storeCap.Store_Id = Stores.Id
GROUP BY Offers.Id, offers.Name, Stores.Id, Stores.Name
-- get stores who have the right number of capabilities so all requirements are all met
HAVING COUNT(*) = (
    select COUNT(*) from StoreCapabilityOffers x where x.Offer_Id = Offers.Id
)

实体如下:

public class Store
{
  public int Id { get; set; }
  public virtual ICollection<StoreCapability> Capabilities { get; set; }
}
public class StoreCapability
{
  public int Id { get; set; }
  public virtual ICollection<Store> Stores { get; set; }
  public virtual ICollection<Offer> Offers { get; set; }
}
public class Offer
{
  public int Id { get; set; }
  public virtual ICollection<StoreCapability> StoreCapabilityRequirements { get; set; }
}

具有两个多对多关系的Linq查询

我认为这样的东西应该工作:

给定一个优惠，一个有效的商店列表:

var stores = from o in context.Offers
             from c in o.StoreCapabilityRequirements
             from s in c.Stores
             where o.Id == 1
             select s;

给定一个商店，可提供的商品列表:

var offers = from s in context.Stores
             from c in s.Capabilities
             from o in c.Offers
             where s.Id == 1
             select o;
相关文章：