Wriju's BLOG

.NET, Cloud and everything

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 

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

[Project1].[EmpId] AS [EmpId], 
[Project1].[EmpName] AS [EmpName], 
[Project1].[C1] AS [C1], 
[Project1].[ProjectId] AS [ProjectId], 
[Project1].[ProjectName] AS [ProjectName]
	[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