15

I have a product entity, which has 0 or 1 "BestSeller" entities. For some reason when I say:

db.Products.OrderBy(p => p.BestSeller.rating).ToList();

the SQL I get has an "extra" outer join (below). And if I add on a second 0 or 1 relation ship, and order by both, then I get 4 outer joins. It seems like each such entity is producing 2 outer joins rather than one. LINQ to SQL behaves exactly as you'd expect, with no extra join.

Has anyone else experienced this, or know how to fix it?

SELECT 
[Extent1].[id] AS [id], 
[Extent1].[ProductName] AS [ProductName]
FROM   [dbo].[Products] AS [Extent1]
LEFT OUTER JOIN [dbo].[BestSeller] AS [Extent2] ON [Extent1].[id] = [Extent2].[id]
LEFT OUTER JOIN [dbo].[BestSeller] AS [Extent3] ON [Extent2].[id] = [Extent3].[id]
ORDER BY [Extent3].[rating] ASC
BlueRaja - Danny Pflughoeft
  • 84,206
  • 33
  • 197
  • 283
Adam Rackis
  • 82,527
  • 56
  • 270
  • 393
  • It seems as though it only does this for a 0..1 entity. If there's a collection of entities, then eager loading the collection does result in the correct single outer join. – Adam Rackis May 27 '10 at 18:49

2 Answers2

2

That extra outer join does seem quite superfluous. I think it's best to contact the entity framework design team. They may know if it's a bug and see if it something that needs to be resolved in a next version. You can contact them at Link

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
René Wolferink
  • 3,558
  • 2
  • 29
  • 43
  • 1
    +1 for taking the time to help make the software better! – JohnB May 27 '10 at 21:11
  • Just curious whether you heard back from the EF team? I'm seeing the same thing and curious to know if they're calling this a bug or a "feature". Thought I'd ask here before I contact the team. – David Kreps Jun 03 '10 at 20:54
  • I'm also curious for any response. In the mean time i've run into this problem as well. With small DBs and low load it's not a big problem, but for more heave users this really can pose a problem! – René Wolferink Jun 25 '10 at 09:48
1

It may be a bug, but it seems like such a simple example that it is strange that the bug has not been caught and fixed.

Could you check your EF model.

Has the BestSeller table been added twice, or is there a duplication in the relationship between the tables.

Shiraz Bhaiji
  • 64,065
  • 34
  • 143
  • 252