Northwind ORMQueryExecutionException with CustomerCustomerDemos in projection

Posts   
 
    
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 14-Jan-2011 04:26:32   

I might of raised something similar before but this query against northwind

from e in Employee
from o in e.Orders
select new {e.FirstName,o.Customer.CustomerCustomerDemos}

gave this SQL

DECLARE @p2 Int; SET @p2=1
SELECT [LPA_L1].[FirstName],
  @p2 AS [LPFA_2],
  [LPLA_3].[CustomerID] AS [CustomerId]
FROM
 ( [Northwind].[dbo].[Employees] [LPA_L1] 
INNER JOIN
 [Northwind].[dbo].[Orders] [LPA_L2]  ON  [LPA_L1].[EmployeeID]=[LPA_L2].[EmployeeID])

which gave ORMQueryExecutionException
An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_3.CustomerID" could not be bound..

linq 3.0.11.113 and orm support 3.0.10.1201

Jeremy Thomas
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 14-Jan-2011 06:22:28   

What is the [LPLA_3].[CustomerID] in the quenerated sql? Are you sure that is the sql for that code?

David Elizondo | LLBLGen Support Team
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 14-Jan-2011 09:51:58   

daelmo wrote:

What is the [LPLA_3].[CustomerID] in the quenerated sql? Are you sure that is the sql for that code?

No idea

yes here it from the exception

QueryExecuted 
  Query: SELECT [LPA_L1].[FirstName], @p2 AS [LPFA_2], [LPLA_3].[CustomerID] AS [CustomerId] FROM ( [Northwind].[dbo].[Employees] [LPA_L1]  INNER JOIN [Northwind].[dbo].[Orders] [LPA_L2]  ON  [LPA_L1].[EmployeeID]=[LPA_L2].[EmployeeID])
  Parameter: @p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

try and run it yourself.

Jeremy Thomas
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Jan-2011 10:05:42   

Reproduced.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-Jan-2011 10:43:53   

Hmm... The CustomerID is the info needed to merge the childset. However the relation needed to get it into the query result isn't added to the main query.

I'll look into this.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-Jan-2011 11:58:11   

The collection is seen as a nested query. It will try to merge the nested query over a single join, a single correlation relation. In your particular case this isn't possible, as more joins are required (order - customer, customer - ccd).

Currently, it's unknown which relationships are involved in navigation to the actual nested set: it might be the nested set is a complicated join with related tables for filtering. I.o.w.: it's not possible for the code to decide which relationships to add to the parent query: the projection it sees contains 'a' query (which is the nested query), but it's not correct to simply add the relationships in that query to the main query (which would solve this particular problem), as these relationships might be necessary only to fetch the nested query, not for correlation.

So in short, it's not supported at this point: to make this work we have to make changes to the linq provider and we can't oversee the effect of this change at this point. (added as workitem for 3.2)

I'll see if we can make it work in 3.1 beta, but can't promise it at this point. I.o.w.: it might be something simple to add, although from the looks of it now, this isn't the case (even though the query might look tremendously simple wink )

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 14-Jan-2011 12:20:01   

Otis wrote:

I'll see if we can make it work in 3.1 beta, but can't promise it at this point. I.o.w.: it might be something simple to add, although from the looks of it now, this isn't the case (even though the query might look tremendously simple wink )

No worries - this is just something I came across by accident - i.e. it's not a repro of a real world problem. The only reason I bothered to report it is because, as you say, it looks pretty simple.

Jeremy Thomas
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 23-Jan-2011 12:50:45   

Don't know if this is the same thing or not but this also throws ORMQueryExecutionException:

from c in Customer
from o in c.Orders.Select(o => new { o.OrderId })
select o 
SELECT [LPA_L1].[OrderId]
FROM
 ( (SELECT [LPLA_2].[OrderID] AS [OrderId]
FROM
 [Northwind].[dbo].[Orders]  [LPLA_2]  
WHERE
 ( ( [LPA_L2].[CustomerID] = [LPLA_2].[CustomerID]))) [LPA_L1]  CROSS JOIN [Northwind].[dbo].[Customers] [LPA_L2] )
Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Jan-2011 10:43:22   

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.

Frans Bouma | Lead developer LLBLGen Pro
TomDog
User
Posts: 618
Joined: 25-Oct-2005
# Posted on: 24-Jan-2011 11:56:16   

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

More complicated LINQ to SQL

from c in Customers from o in c.Orders.Select(o => new { o.OrderID, c.CompanyName }).DefaultIfEmpty()
select new { c.ContactName, o }

gives

SELECT [t0].[ContactName], [t2].[test], [t2].[OrderID], [t0].[CompanyName]
FROM [Customers] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[OrderID], [t1].[CustomerID]
    FROM [Orders] AS [t1]
    ) AS [t2] ON [t2].[CustomerID] = [t0].[CustomerID]
Jeremy Thomas
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Jan-2011 12:13:08   

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.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Jan-2016 11:20:25   

With our recent work on the linq provider to make navigations much more solid, we also fixed this particular issue (v5.0)

Frans Bouma | Lead developer LLBLGen Pro