CODEWITHSUNDEEP
c#entity-framework-4many-to-many
John
John

Reputation: 3546

Best practice for many-to-many joins in EF

What is the best solution for many-to-many joins in Entity Framework. It looks like in the one project where we used EF after adding the tables to the edmx file it neglected to add the intersection entity i.e.:

With these tables

Customer(CustomerId,...)
CustomerOrder(CustomerId,OrderId)
Order(OrderId,...)

The CustomerOrder table was not added to the edmx so then the tables could not be joined with a conventional (conventional meaning how we used to do it in LINQ to SQL) inner join query e.g.

var q = from c in db.Customers
        join co in db.CustomerOrders on c.CustomerId equals co.CustomerId
        join o in db.Orders on co.OrderId equals o.OrderId
        select a;

As I understand from looking at previously asked questions you could do multiple selects and "join" by specifing conditions in the where clause, or use the intersects keyword. But I would like to know what is the best practice for situations like these.

Say I would like to find out which orders a customer has, how would I go about writing that query.

Upvotes: 1

Views: 1978

Answers (2)

John
John

Reputation: 3546

For future reference for anyone who has similar question, I just had to see what EF actually generates and aparently EF is smart enough to add the joins where necessary when the intersection entities are hidden e.g. :

var query = from c in db.Clients
from o in db.Orders
where c.Id == 1 
select o.Id;

genereates the following sql :

SELECT
[Extent2].[Id] AS [Id]
FROM  [dbo].[Client] AS [Extent1]
CROSS JOIN [dbo].[Order] AS [Extent2]
WHERE 1 = [Extent1].[Id]

Upvotes: 0

Erik Philips
Erik Philips

Reputation: 54638

Just for clarification, when the two tables are added, the joining table is not part of the edmx, unless it contains more than just the joining table IDs. In the scenario when EF decides to not use the joining table as an entity, each of the other tables should have navigational properties (Orders would have ICollection<Customer> Customers and Customers would have ICollection<Orders>) which allow access to the joining tables without the need to manually join them. In that scenario you would be able to do the following:

var customer = dbContext.Customers.Include("Orders")
                                  .Where(o => o.Orders.OrderID == 2);

Normally, the navigational properties are lazy loaded, where the object(s) are not in the properties of the entity until they are accessed by code. You can manually tell the framework to load them right away if you know you are going to use them by using the Include method. This method in this example is NOT necessary in order to access the Orders.OrderID value.

Upvotes: 5

Related Questions