TomDog wrote:
Otis wrote:
I stared at the query for a while, till I saw what's the problem: it contains a catch22 in the join, or better: one side's rows depend on the other side's rows. This is only doable with a CROSS APPLY statement, something we don't support (as it's a SQL Server 2005+ specific join statement).
You'll likely see Linq to sql use a CROSS APPLY in this query.
Linq to sql
SELECT [t1].[OrderID]
FROM [Customers] AS [t0], [Orders] AS [t1]
WHERE [t1].[CustomerID] = [t0].[CustomerID]
EF
SELECT
[Extent1].[OrderID] AS [OrderID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] IS NOT NULL
The only way to solve this properly is thus to add additional optimizers which take care of this particular situation. The main problem is that the nested from clause in the linq query defines a cross join and the second from clause defines the same join, however the Select with custom projection requires that that join is seen as a derived table. To handle a nested from with the same join in the second part is to replace the cross join from the two from's with the join in the second from.
This is the generic way to handle it and how we implemented it. This of course runs into a problem in the situation where you have an encapsulated join which is the same as the nested from clause: you now can't replace one with the other directly, unless you perform an extra step for this particular situation, the special case code path we didn't implement (as frankly, it's a never ending story, these list of special cases, and we can only implement them after we've learned about them, which is about ... now, in this case)
Perhaps there's a more generic pattern to implement for this situation:
from x in metaData.X
from y in x.Ys(... whatever is specified here, can be nothing, can be query)
...
than the one we implemented, namely replace the SelectMany between x and y with x.Ys, which can't be applied here because x.Ys isn't equal to SelectMany over x + y as there's a query Y is retrieved from.
However this generic pattern hasn't been known to us, but apparently it is to Microsoft. I decided some time ago it's not worth the effort to chase down every special case and re-work pattern to make every cumbersome linq query to work properly as you'll never end up with something that works, there's always a query which fails.