RSS

Select Many to Many Relationship in LINQ to Entity Framework

21 Mar

Linq to Entity Framework query looks little bit different when it comes to selecting database records from two tables in which the relationship is many-to-many.

The database diagram looks like:

In regular T-SQL query, I would normally do this:

SELECT Departments.*, Rooms.*
FROM Departments INNER JOIN DepartmentRoomJunction ON Departments.Id = DepartmentRoomJunction.DepartmentId
	INNER JOIN Rooms ON DepartmentRoomJunction.RoomId = Rooms.Id
	WHERE Departments.Id = 2

The result is:

However, when try to do ‘join’ in Linq, the Intellisense doesn’t show Entity object that I want.

Linq is little bit different when it comes to querying many-to-many relationship. Here’s the code:

var _departments = from d in _context.Departments
                           from r in _context.Rooms
                           where d.Id == 2
                           select d;

This is because in Entity Framework, many-to-many relationship are automatically joined. So, there is no need to join them again in the query. As you can see the Entity data model designer below, both Departments and Rooms are not connected with a junction table, as it would with the database diagram.

Advertisements
 
Leave a comment

Posted by on March 21, 2011 in General

 

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: