Entity Framework - Many to many question

Question

I have a table called ASB and a table called PeopleInvolved. There is a junction table called PeopleInvolved_ASB which simply contains an ASBID and a PeopleInvolvedID column. The columns act as a composite primary key.

The designer does not show the junction table (as expected). I want to retrieve a list of PeopleInvolved based on an ASBID.

To retrieve the people, I'm doing this:

 // This top line gets the ASB record from the Case
 var asbRecord = (from c in dd.Case
                         where c.CaseID == caseID
                         select c.ASB).First();

        var asbID = asbRecord.Select(asb => asb.ASBID).First();

        var people = (from asb in dd.ASB
                      where asb.ASBID == asbID
                      select asb.PeopleInvolved);

Now, what I want to do is add each PeopleInvolved record to a simple list of type PeopleInvolved. I can't do this though. I keep getting:

Error 4 Cannot convert type 'System.Data.Objects.DataClasses.EntityCollection' to 'Dynamic.PeopleInvolved'

How can I get a simple list of PeopleInvolved into a generic list that I can pass back to my controller?

Thanks,

Answer 1

This is what you want to do?

 List<PeopleInvolved> genericPeopleInvolvedList = (from asb in dd.ASB
                          where asb.ASBID == asbID
                          select asb.PeopleInvolved).ToList();

[Updated: answered bad before]

Just realised that asb.PeopleInvolved is collection not single entity (damn!). So,previous linq query is returning CollectionS of PeopleInvolved entities. Since you are selecting by ASPID there should be only one asb.ASBID that fullfill where clause asb.ASBID == asbID, and you can do as follows:

var listWithCollectionOfPeopleInvolved = (from asb in dd.ASB
                              where asb.ASBID == asbID
                              select asb.PeopleInvolved).ToList();

List<PeopleInvolved> peopleInvolved = listWithCollectionOfPeopleInvolved.First().ToList();

But it's much nicer if you do it using Include:

var asbInstance = (from asb in dd.ASB.Include("PeopleInvolved")
                      where asb.ASBID == asbID
                      select asb).FirstOrDefault();
foreach(PeopleInvolved pi in asbInstance.PeopleInvolved)
{
//do your stuff
}

With Include automatically load associated properties.

Answer 2

Based on the error you report I'm guessing PeopleInvolved is a collection. So try this:

    var people = (from asb in dd.ASB
                  where asb.ASBID == asbID
                  from pi in asb.PeopleInvolved
                  select pi).ToList();

I think it's extremely confusing to have both a type named PeopleInvolved and a collection of that type with exactly the same name. Similarly, it seems you have a type called ASB and an Entity Set containing many instances of that type by the same name,

It's much clearer to make entity set names plural and type names singular.

If I've guessed the types wrong, please clarify the layout of ASB and Dynamic.PeopleInvolveed.

Answer 3

List<PeopleInvolved> = new List<PeopleInvolved>(people);