CODEWITHSUNDEEP
c#sqllinqentity-frameworklinq-to-entities
Tom Haigh
Tom Haigh

Reputation: 57805

Linq query with two many-many relationships

I have the following entities set up which I am trying to query:

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

So there are five tables involved.

I would like to be able to obtain the following using linq:

Using SQL it would be possible to join from Store, to StoreCapability, to Offer and then group by offer and store and only obtain the stores having count() equal to the number of requirements an offer has. However I don't know where to start with this with Linq, because the many-many tables are hidden away from me by the Entity framework. Please can anyone help with how I can do this?

The SQL could be something similar to this:

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
)

Entities below:

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; }
}

Upvotes: 0

Views: 1432

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

I think something like this should work:

Given an offer, a list of stores which it is valid for:

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

Given a store, a list of offers available:

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

Upvotes: 1

Related Questions