Code First EF 4.1 : Querying Many to Many Relationship

In continuation to my previous post on how to create one to many, let’s see how can we query it.

We will use the same code base and query it

Option 1

When we want to query it normally like below

 var emps = ctx.Emps
            .Where(e => e.EmpId == 1)
            .SelectMany(e => e.Projects, 
                    (em, proj) => new 
                    {
                        em.EmpName, 
                        proj.ProjectName
                    });

The generated object graph would look like

Option 2

Whereas if we want to write our query as below

 var em = from e in ctx.Emps.Include(p => p.Projects)
            where e.EmpId == 1
            select e;
            

This case object graph is more complicated

Generated SQL

Interestingly both the cases the generated SQL is same

 SELECT 
[Project1].[EmpId] AS [EmpId], 
[Project1].[EmpName] AS [EmpName], 
[Project1].[C1] AS [C1], 
[Project1].[ProjectId] AS [ProjectId], 
[Project1].[ProjectName] AS [ProjectName]
FROM ( SELECT 
  [Extent1].[EmpId] AS [EmpId], 
 [Extent1].[EmpName] AS [EmpName], 
 [Join1].[ProjectId] AS [ProjectId], 
   [Join1].[ProjectName] AS [ProjectName], 
   CASE WHEN ([Join1].[Project_ProjectId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM  [dbo].[Emps] AS [Extent1]
   LEFT OUTER JOIN  (SELECT [Extent2].[Project_ProjectId] AS [Project_ProjectId], [Extent2].[Emp_EmpId] AS [Emp_EmpId], [Extent3].[ProjectId] AS [ProjectId], [Extent3].[ProjectName] AS [ProjectName]
     FROM  [dbo].[ProjectEmps] AS [Extent2]
        INNER JOIN [dbo].[Projects] AS [Extent3] ON [Extent3].[ProjectId] = [Extent2].[Project_ProjectId] ) AS [Join1] ON [Extent1].[EmpId] = [Join1].[Emp_EmpId]
 WHERE 1 = [Extent1].[EmpId]
)  AS [Project1]
ORDER BY [Project1].[EmpId] ASC, [Project1].[C1] ASC

Namoskar!!!