LINQ: Using a pivot table in linq

Question

Hi there,

Can anyone help?

I have the following structure using associations, and as you can see v.StructureType.Description works great as its a 1 to 1 association, but i need to get in my example below v.StructureGroup.StructureGroupTariffs.Tariff.Price but StructureGroupTariffs is a Pivot table to interlink the StructureGroup and Tariffs, hence its a Many to Many so it returns a EntitySet, I understand the issue now but i am unsure how to fix it

    from v in Houses
       select new 
       {
          Id = v.Id,
          Color = v.Color.Description,
          StructureType= v.StructureType.Description,
          Price = v.StructureGroup.StructureGroupTariffs. // Tariff.Price doesn't appear because its MANY to ONE
       }

So understanding that my structureGroupTariffs is my pivot (inter linking table) i must pass in

                 IdTariff  and  StructureGroupId

this then gives me a 1 to 1... So i presume i can then use StructureGroupTariffs.Tariff.Price ??? as this will then return a 1 to 1 between StructureGroup and Tariff

I am a little confused and would really appreciate some help.

I understand the issue now but unsure of how i can fix it.

Answer 1

Think about how you would solve this kind of query in SQL and I think you can apply that reasoning. There are two primary ways of writing this kind of join: the first is to use inner joins from the main table to the join table and again to the result table, with appropriate filters in place. If you are not careful to filter the join correctly you would get multiple rows per main table (which is effectively why you are getting an EntitySet. If you use .FirstOrDefault() against that entity set you will get the Entity you are looking for (assuming that you have filtered down to a single result: if not you will get some random result).

An alternative would be to write a subquery, such as

Price = (from t in v.StructureGroup.StructureGroupTariffs.Tariffs 
where t.StructionGroupID = v.StructureGroupID 
and t.IdTariff = *not sure where this filter comes from*
select t.Price).First()

The missing filter above is likewise a problem for the first approach: either way you need to uniquely identify the t.Price you want.

Here is an example of inner joins (simplified from a schema of mine where Docs are many to many with DocTags via the Doc_DocTag table.

from d in Docs 
join ddt in Doc_DocTags on d.DocGuid equals ddt.DocGuid
join dt in DocTags on ddt.DocTagID equals dt.DocTagID
select new {
    d.DocName, dt.DocTagName
}

If I wanted only one tag:

from d in Docs 
join ddt in Doc_DocTags on d.DocGuid equals ddt.DocGuid
join dt in DocTags on ddt.DocTagID equals dt.DocTagID
where dt.DocTagName == "Target"
select new {
    d.DocName, dt.DocTagName
}

This would find only docs with a tag of Target (which would further ensure that multiple DocTags are not possible).