How do I correctly use LINQ groupby for two tables joined via an intermediate table?

Question

I have three tables.

1. Interviews
2. Interviewers
3. InterviewSchedule.

An interviewer can be scheduled for multiple interviews

An interview can have multiple interviewers.

So,

• InterviewSchedule table has columns interviewid, interviewerid. (Many to many relationship)
• Interviewtable has columns - InterviewId, InterviewLocation, InterviewSubject.
• Interviewer table has columns - InterviewerId, InterviewerName, InterviewerTitle.

Now, I want to generate a report of interviews with the interviewer details. I created a dataobject as InterviewId, InterviewLocation, InterviewSubject, List<Interviewer>;

I am trying to make one LINQ query to get my output. I use entityframework and already have the context created.

I am kind of new to LINQ, but I see this should be possible and I saw multipleposts from people groupby, using Id.

I think my problem is I want to select multiple fields from both the tables via the intermediate table.

var output = (from i in Interview 
              join ia in InterviewSchedule on i.interviewid equals ia.interviewid 
              join iw in Interviewers on ia.interviewerid equals iw.interviewerid) 
              group i by i.interviewid into g 
              select new {i, interviewers = new {interviewername, interviewertitle} };

I am lost at this point. Is this not the right approach? Do I have to make a 'for' loop to add all the interviewers to the list, one by one?

Answer 1

Please, try this

            var output = (from i in Interview
                          group i by i.InterviewId into g
                          join ia in InterviewSchedule on g.Key equals ia.InterviewId
                          join iw in Interviewers on g.Key equals iw.InterviewerId
                          select new { g.Key, interviewers = iw }).ToList();

see LINQ: combining join and group by