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.