Select Many to Many Relationship in LINQ to Entity Framework

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.

Leave a Reply

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

You are commenting using your 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