LINQ to Entities many to many relationship

Question

I am new to LINQ and the Entity Framework and am having trouble coming up with a suitable query.

I have the following entities. I have included primary keys and some other relevant fields.

Contact Int ContactId(PK), String Name, String EMailAddress

Project Int ProjectId(PK)

ProjectContact Int ProjectId(PK), Int ContactId(PK), Boolean IsPrimaryContact, Boolean IsSecondaryContact

A project can have 1..n contacts, one of which is the primary contact for that project. Additionally if the project has more than one contact, one of the other contacts can be the secondary contact for that project.

A contact can be associated with many projects and may be the primary or secondary contact for several projects.

I need to generate an email for each group of projects that have a shared combination of primary and secondary contact. The idea is that the To field of the email contains the primary contact's email address, the CC field contains the secondary contact's email address (if there is a secondary contact) and the body of the email contains details of all the projects that have this combination of primary and secondary contacts.

I would like to populate a list containing objects with the following structure:

class EmailDetails
{
    public Contact PrimaryContact;
    public Contact SecondaryContact;
    public IEnumerable<Project> Projects = new List<Project>();
}

So far I have this:

var QueryResults =
    from project in ProjectSet
        join primaryContact in ProjectContacts on project.ProjectId equals primaryContact.ProjectId where primaryContact.IsPrimary
        join secondaryContact in ProjectContacts on project.ProjectId equals secondaryContact.ProjectId where secondaryContact.IsSecondary
        select new {primaryContact, secondaryContact, project}

Where do I go from here?

Thanks.

Answer 1

It's rarely correct to use join in L2E/L2S. Use associations/navigation properties instead.

Off the top of my head (may require some tweaking):

var QueryResults = from project in ProjectSet
                   let p = project.Contacts.FirstOrDefault(c => c.IsPrimary)
                   let s = project.Contacts.FirstOrDefault(c => c.IsSecondary)
                   group project by new { Primary = p, Secondary = s } into g
                   select new EmailDetails
                   {
                       PrimaryContact = g.Key.Primary,
                       SecondaryContact = g.Key.Secondary,
                       Projects = from proj in g
                                  select new ProjectDetails
                                  {
                                      Project = proj,
                                      Region = proj.Region,
                                      ProjectItems = proj.ProjectItems
                                  }
                   };