How to write a linq query to prevent duplicate connections?

Question

I have a query that searches for all numbers in an order, sorted by day. When I check which query is running, I see several joins in the same table on the same keys

var parcourt = this.DataService.From<OrderItem>() .Where(i => i.OrderId == orderId && i.Product.ProductTypeId == (int)ProductTypes.Accommodation) .OrderBy(i => i.DayNumber) .ThenBy(i => i.OrderItemId) .Select(i => new { i.OrderItemId, i.DayNumber, i.Product.Establishment.Address, i.Product.Establishment.Coordinates }); 

If you check the resulting SQL (as shown by ToTraceString ), you will see two connections in the Products and Establishments table.

 SELECT [Project1].[OrderItemId] AS [OrderItemId], [Project1].[DayNumber] AS [DayNumber], [Project1].[Address] AS [Address], [Project1].[EstablishmentId] AS [EstablishmentId], [Project1].[Latitude] AS [Latitude], [Project1].[Longitude] AS [Longitude] FROM ( SELECT [Extent1].[OrderItemId] AS [OrderItemId], [Extent1].[DayNumber] AS [DayNumber], [Extent4].[Address] AS [Address], [Extent5].[EstablishmentId] AS [EstablishmentId], [Extent5].[Latitude] AS [Latitude], [Extent5].[Longitude] AS [Longitude] FROM [dbo].[OrderItems] AS [Extent1] INNER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[ProductId] = [Extent2].[ProductId] LEFT OUTER JOIN [dbo].[Products] AS [Extent3] ON [Extent1].[ProductId] = [Extent3].[ProductId] LEFT OUTER JOIN [dbo].[Establishments] AS [Extent4] ON [Extent3].[EstablishmentId] = [Extent4].[EstablishmentId] LEFT OUTER JOIN [dbo].[Establishments] AS [Extent5] ON [Extent3].[EstablishmentId] = [Extent5].[EstablishmentId] WHERE (1 = [Extent2].[ProductTypeId]) AND ([Extent1].[OrderId] = @p__linq__0) ) AS [Project1] ORDER BY [Project1].[DayNumber] ASC, [Project1].[OrderItemId] ASC 

How can I prevent these linq-to-entity relationships from joining the table twice? How can I rewrite a request to avoid this situation?

The structure of the table is as follows (simplified):

This is a request

Answer 1

Could you try this query? I think that if you call all of your connections explicitly, they will not automatically create connections.

 var parcourt = (from i in this.DataService.OrderItem join p in this.DataService.Product on p.ProductId equals i.ProductId join e in this.DataService.Establishments on e.EstablishmentId equals p.EstablishmentId where i.OrderId == orderId && p.ProductTypeId == (int)ProductTypes.Accomodation orderby i.DayNumber, i.OrderItemId select new { i.OrderItemId, i.DayNumber, e.Address, e.Coordinates });