CRM 2011 - N:N (Many-To-Many) Linq Issue

Question

I have two entities who are N:N - related with each other. With an example I'll show you what I mean :

• I have a Session (ave_Session) and there we can put "Trainers" (ave_trainer) on each Session
• I'm tryting to get a list of al the "Trainers" for a particular Session
• They are related to each other in N:N (relationship name : ave_ave_session_ave_trainer)
• I work in VS2010 and with C# => I'm trying to get the data through LINQ

I recently just started with LINQ, so maybe you guys can help me out on this one. The following I've tried and i gave me an "AttributeFrom and AttributeTo must be either both specified or both ommited. You can not pass only one or the other. AttributeFrom: , AttributeTo: ave_trainerid"-error :

var formatteurs = (from f in ORGContext.CreateQuery<ave_trainer>()
                   join s in ORGContext.CreateQuery<ave_ave_session_ave_trainer>() on f.Id equals s.ave_trainerid.Value
                   join c in ORGContext.CreateQuery<ave_session>() on s.ave_sessionid.Value equals c.Id
                    where c.Id == item.Id
                    select f).ToList();

The item.id is the Id of the session. Thx in advance if you can help me out!

Answer 1

From the MSDN page:

// List the contacts in the Softball team marketing list.
System.Console.WriteLine("List all contacts in Softball Team:");

var members = from c in crm.contacts
              join mlm in crm.listmembers on c.contactid equals mlm.entityid
              join ml in crm.lists on mlm.listid equals ml.listid
              where ml.listname == "Softball Team"
              select c;

foreach (var c in members)
{
  System.Console.WriteLine(c.fullname + " " + c.emailaddress1);
}

Answer 2

It seems a little backwards the way you have it written now (assuming I'm parsing it correctly).

What you'd normally do is put your 'starting thing' first and then go through the mapping to get to the ones you want. I don't have any CRM 2011 experience, so hopefully I didn't mess this up too much. :)

Also, I'm not a fan of single-character names, so I took the liberty of using longer names :)

var formatteurs = (
    // first get the session we're interested in
    from session in ORGContext.CreateQuery<ave_session>()
    where session.Id == item.Id

    // now get the mapping rows that are related to it
    join mapping in ORGContext.CreateQuery<ave_ave_session_ave_trainer>() 
        on session.Id equals s.ave_sessionid.Value

    // now get from the mapping rows to the actual trainers
    join trainer in ORGContext.CreateQuery<ave_trainer>()
        on mapping.ave_trainerid.Value equals trainer.Id

    select trainer
).ToList();