Fetch only key value from EF association

Question

I've set up a many-to-many association between two tables based on a third table that just holds a pair of key values. Now I'd like to do a query that groups the right tables key values by the lefts without needing other data.

LeftTable { LeftID, LeftField1, LeftField2 } 
JoinTable { LeftID, RightID} 
RightTable { RightID, RightField1, RightField2 }

Is there any way to essentially just query the JoinTable and get all the 'RightIDs' grouped by the 'LeftIDs' without the SQL trying to fetch the fields from either side? The JoinTable is not an entity in its own right in the model, but is mapped to the association.

I've experimented a bit with both using ObjectQuery and EntityCommand (ESQL) and both seem to still load in the other fields by joining to RightTable which I don't need.

My ESQL looks something like:

SELECT lt.LeftID, (SELECT rt.RightID
FROM NAVIGATE(lt, MyModel.LeftToRightAssoc, RightTable) as rt)
FROM MyEntities.LeftTable as lt;

but the generated SQL is still fetching in RightField1 and RightField2.

Surely there must be a simpler way to do this?

score 1 · Answer 1 · answered May 23 '11 at 15:51

Assuming that your class Left has a navigation property Rights (a collection of Right entities) you could try this:

var list = context.Lefts.Select(l => new
{
    LeftId = l.LeftId,
    RightIds = l.Rights.Select(r => r.RightId)
});

foreach (var item in list)
{
    Console.WriteLine("LeftId = {0}", item.LeftId);
    foreach (var rightId in item.RightIds)
    {
        Console.WriteLine("RightId = {0}", rightId);
    }
}

You would get a collection of anonymous type objects where each element has the LeftId and a collection of corresponding RightIds. This query should not touch the other fields like RightField1, etc. Instead of an anonymous type you could also create your own custom type and then project into this type in the query above.

Fetch only key value from EF association

1 Answers1