Code First EF 4.1 : Querying Many to Many Relationship

Code First EF 4.1 : Querying Many to Many Relationship

  • Comments 0

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

image

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

image

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!!!

Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post