LINQ: Associations not functioning correctly for use with innerjoins

Question

Hi there,

Can anyone help, I have been using linq2sql with great success using my associations (foreign keys) to provide inner joins etc...

for example this, works great, the color is stored in a table called Color which has association so i pick it up via Color.Description - Excellent. Same for StructureType its actually an association so i pick it up via the structureType association and the field Description which is in the StructureType table

     from v in Houses
     select new 
     {
        Id = v.Id,
        Color = v.Color.Description,
        StructureType= v.StructureType.Description,
        Price = v.StructureGroup.StructureGroupTariffs. // doesn't provide anything more only Any, All etc
}

The problem being is that Price i am trying to link, so houses has a Association to StructureGroup which has a association to StructureGroupTariffs but i don't get any more options..

StructureGroupTariffs in an Interlinking table that links StuctureGroup to Tariffs,

I actually did a test here with the interlinking table in Linq and it works! like so

        from g in StructureGroupTariffs
        select new 
        {
           Price = g.Tariff.Price
        }

So i am a little confused, why (see above) this

        Price = v.StructureGroup.StructureGroupTariffs.

only returns methods like Any, All etc but not the association....

Can anyone help please

Answer 1

StructureGroupTariffs is an EntitySet not an EntityRef, i.e. it contains many objects rather than a single object. You can't extract the 'many' into the 'single' that you are assembling in your query.

EDIT

I suspect that your House table has a StructureGroup and that there is a Tariffs table, in between is a StructureGroupTariffs table that holds FK references to each of the other tables allowing there to be many tariffs for each structure group (irrespective of whether there actually are many tariffs). Hence Linq using an EntitySet to refer to the StructureGroupTariffs.