Hi. We use the latest 5.9.1 ORM. The schema relations are: Report -> Order -> OrderProcedure -> ProcedureCode

Here's our LINQ code with some nested queries:

var q = from rep in metaData.Report
       join ord in metaData.Order on rep.ReportID equals ord.LastReportID
       join .......  // some more joins
       where rep.ReportStatusID == 7
       select new
      {
            rep.ReportID,
            ord.OrderID,
            ord.LastReportID,
            ord.StartDate,
            Procedures = (from op in metaData.OrderProcedure
                              where op.OrderID == ord.OrderID
                              select new  { op.ProcedureCodeID,  ... } )
      };

When there are less than 50 Reports, LLBL generates an OrderProcedures subquery with a simple IN clause:

exec sp_executesql N'SELECT [LPLA_17].[ProcedureCodeID], 1 AS [LPFA_41], 1 AS [LPFA_42], [LPLA_17].[OrderID] 
FROM [OrderProcedure] [LPLA_17] 
WHERE ( ( ( [LPLA_17].[OrderID] IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9))))',N'@p1 int,@p2 int,@p3 int,@p4 int,@p5 int,@p6 int,@p7 int,@p8 int,@p9 int',@p1=494347,@p2=495317,@p3=511482,@p4=511507,@p5=511595,@p6=511638,@p7=511799,@p8=511868,@p9=512292

But when it exceeds 50 Reports, which is the default ParameterisedPrefetchPathThreshold, we get this:

exec sp_executesql N'SELECT [LPLA_17].[ProcedureCodeID], 1 AS [LPFA_41], 1 AS [LPFA_42], [LPLA_17].[OrderID] 
FROM [OrderProcedure] [LPLA_17] 
WHERE ( ( (  
    EXISTS (
        SELECT [LPA_L2].[OrderID] 
        FROM (
            SELECT [LPA_L3].[ReportID],  [LPA_L4].[OrderID], [LPA_L4].[LastReportID], [LPA_L4].[StartDate], 1 AS [LPFA_44] 
            FROM (((([Report] [LPA_L3] 
                INNER JOIN [Order] [LPA_L4] ON [LPA_L3].[ReportID] = [LPA_L4].[LastReportID]) 
                INNER JOIN ....... 
                WHERE ( ( [LPA_L3].[ReportStatusID] = @p3))
            ) [LPA_L2] 
            WHERE ( ( ( ( ( [LPA_L2].[ReportID] <> @p5) 
            AND ( [LPA_L2].[LastReportID] <> @p7)))) 
            AND [**LPA_L2].[OrderID] = [LPA_L2].[OrderID]**)))))',N'@p2 int,@p3 int,@p5 int,@p7 int',@p2=73093,@p3=7,@p5=516398,@p7=516398

But [LPA_L2].OrderID=[LPA_L2].OrderID is wrong as it fetches the entire table and causes major delays. We would expect to output [LPLA_17].OrderID=[LPA_L2].OrderID.

Is that a bug? How can we modify the LINQ to get the relation right?

Thanks.

That's an issue with the patch we released in 5.9.1 and which is fixed in the 5.9.2 hotfix. Please try v. 5.9.2 hotfix.

Walaa wrote:

That's an issue with the patch we released in 5.9.1 and which is fixed in the 5.9.2 hotfix. Please try v. 5.9.2 hotfix.

I tried 5.9.2 and produces the same query.

obzekt wrote:

Walaa wrote:

That's an issue with the patch we released in 5.9.1 and which is fixed in the 5.9.2 hotfix. Please try v. 5.9.2 hotfix.

I tried 5.9.2 and produces the same query.

You reported an issue earlier, in 5.9.0 and 5.8.4 which we fixed in 5.9.1 and 5.8.5. However there was a problem with that fix, and we patched that in v5.9.2 and 5.8.6 hotfixes which will be released to GA on July 24th or thereabout.

If it's not fixing the issue, then please provide a detailed repro case so we can investigate the issue. The issue at first glance looks like it's the same issue we fixed or that you reported earlier. The fix was to re-alias an entity that was re-used and the alias had to be removed from the re-aliased elements again which was a problem in the 5.9.1 patch as that wasn't always done.

E.g. cut down the linq query to the minimum that it still reproduces the issue and give it in full + the exact sql query it produces. Be aware that nested queries result in multiple SQL queries per linq query. Use adapter.ParameterisedPrefetchPathThreshold = 1; to always trigger the subquery approach which might testing easier.

E.g. this works fine here so it must be something else in your query which you removed in your post (please, next time, don't do that, we ask for full queries for a reason so we can see what's going on and not have to guess like we are doing now)

adapter.ParameterisedPrefetchPathThreshold = 1;
var metaData = new LinqMetaData(adapter);
var q = from st in metaData.SalesTerritory
        join sp in metaData.StateProvince on st.TerritoryId equals sp.TerritoryId
        where st.SalesYtd > 1.0M
        select new
               {
                   st.TerritoryId,
                   sp.Name,
                   sp.CountryRegionCode,
                   Addresses = (from a in metaData.Address
                                where sp.StateProvinceId == a.StateProvinceId
                                select new
                                       {
                                           a.StateProvinceId, 
                                           a.City,
                                           SalesOrders = (from soh in metaData.SalesOrderHeader
                                                          where soh.BillToAddressId == a.Id
                                                          select new {soh.SalesOrderId, soh.ContactId})
                                       })
               };
var results = q.ToList();

-- q1
SELECT [LPA_L1].[TerritoryID]     AS [TerritoryId],
       [LPA_L2].[Name],
       [LPA_L2].[CountryRegionCode],
       1                          AS [LPFA_8],
       [LPA_L2].[StateProvinceID] AS [StateProvinceId]
FROM   ([AdventureWorksUnitTests].[Sales].[SalesTerritory] [LPA_L1]
        INNER JOIN [AdventureWorksUnitTests].[Person].[StateProvince] [LPA_L2]
            ON [LPA_L1].[TerritoryID] = [LPA_L2].[TerritoryID])
WHERE  ((((([LPA_L1].[SalesYTD] > @p1))))) 

--q2
SELECT [LPLA_4].[StateProvinceID] AS [StateProvinceId],
       [LPLA_4].[City],
       1                          AS [LPFA_6],
       [LPLA_4].[AddressID]       AS [Id]
FROM   [AdventureWorksUnitTests].[Person].[Address] [LPLA_4]
WHERE  (((EXISTS
          (SELECT [LPA_L3].[StateProvinceID] AS [StateProvinceId]
           FROM   ([AdventureWorksUnitTests].[Sales].[SalesTerritory] [LPA_L2]
                   INNER JOIN [AdventureWorksUnitTests].[Person].[StateProvince] [LPA_L3]
                       ON [LPA_L2].[TerritoryID] = [LPA_L3].[TerritoryID])
           WHERE  ((((([LPA_L2].[SalesYTD] > @p1))))
               AND [LPA_L3].[StateProvinceID] = [LPLA_4].[StateProvinceID]))))) 

--q3
SELECT [LPLA_5].[SalesOrderID]    AS [SalesOrderId],
       [LPLA_5].[ContactID]       AS [ContactId],
       [LPLA_5].[BillToAddressID] AS [BillToAddressId]
FROM   [AdventureWorksUnitTests].[Sales].[SalesOrderHeader] [LPLA_5]
WHERE  (((EXISTS
          (SELECT [LPLA_4].[AddressID] AS [Id]
           FROM   [AdventureWorksUnitTests].[Person].[Address] [LPLA_4]
           WHERE  (((EXISTS
                     (SELECT [LPA_L3].[StateProvinceID] AS [StateProvinceId]
                      FROM   ([AdventureWorksUnitTests].[Sales].[SalesTerritory] [LPA_L2]
                              INNER JOIN [AdventureWorksUnitTests].[Person].[StateProvince] [LPA_L3]
                                  ON [LPA_L2].[TerritoryID] = [LPA_L3].[TerritoryID])
                      WHERE  ((((([LPA_L2].[SalesYTD] > @p1))))
                          AND [LPA_L3].[StateProvinceID] = [LPLA_4].[StateProvinceID]))))
               AND [LPLA_5].[BillToAddressID] = [LPLA_4].[AddressID]))))) 

Not sure which issue you refer to, that I reported for 5.9.0.. but here's the full LINQ and generated queries:

(deleted - see compact sample below)

And if you cut out the defaultifempty or joins, does it still produce it? Is there inheritance in the model? Please cut it down to the minimum linq query that reproduces the issue as I requested.

(e.g. if you cut out the temp projections in the joins, which wrap sources into anonymous types and which aren't needed, as the sql optimizer will cut the columns you're not fetching anyway, and join with the entities directly, it might work already. the main issue with this query looks like the original source of a field is lost among the tree of anonymous types. Every join you create folds the sources into anonymous types and the linq provider has to puzzle back which original source was in there. This is needed to assign the alias of the entity the source represents to it. But as these trees get longer and temp projections get into the chain, it might be the sources get lost as it's no longer certain where the sources come from. I suspect that's the root cause here. I for one can't immediately fix the alias in the end SQL manually so for me it's also hard to track down what alias it should have been... (I think LPLA_17) )

(edit, hmm, indeed you didn't report that issue, I assumed you had. Sorry about that! The issue isn't related to that problem as well, it's a linq provider issue)

(edit) also these 2 predicates:

        WHERE ( ( ( ( ( [LPA_L2].[ReportID] <> @p5) 
            AND ( [LPA_L2].[LastReportID] <> @p7)))) 

I can't find them back in the linq query? They look like constant comparisons, but the actual predicate in the linq query, where rep.ReportStatusID == (int)ReportStatus.Final doesn't resemble those, in fact that one is absent.

So there's a mismatch somewhere between your linq query and the sql queries you reported.

(edit) It's odd the where predicate uses the wrong alias for one side, I can't think of a reason why it would do that. The source of the deeply nested element is properly aliased even (LPA_L2) as it's part of the inner query, why it doesn't refer to the outer query properly is a mystery atm. With a cut down query which matches the SQL we might be able to answer that. We think it's a problem related to the linq provider.

I tried a few different combinations, cut it down to this LINQ:

var q = from rep in metaData.Report
           join ord in metaData.Order on rep.ReportID equals ord.LastReportID
        where rep.ReportStatusID == (int)ReportStatus.Final
            select new
        {
                rep.ReportID,
                rep.IsAddendum,
                rep.LastSignDate,
                ord.OrderID,
                ord.LastReportID,
                ord.ProcedureCodeList,
                ord.ProcedureDescList,
                ord.StartDate,
                ord.FillerOrderNumber,
                Procedures = (from op in metaData.OrderProcedure
                                  where op.OrderID == ord.OrderID
                                  select new { op.ProcedureCodeID } )
        };

        if (reportID > 0)
            q = q.Where(x => x.ReportID != reportID && x.LastReportID != reportID);

It's this last conditional predicate that causes the problem. I forgot to include it in the full sample earlier, as it was in another section of the code.

If I comment out this q = q.Where(...), it works. The behavior is the same with 5.9.1 and 5.9.2-hotfix.

This is the faulty query with the WHERE clause:

exec sp_executesql N'SELECT [LPLA_4].[ProcedureCodeID], [LPLA_4].[OrderID] FROM [OrderProcedure] [LPLA_4] WHERE ( ( ( EXISTS (SELECT [LPA_L2].[OrderID] FROM (SELECT [LPA_L3].[ReportID], [LPA_L3].[IsAddendum], [LPA_L3].[LastSignDate], [LPA_L4].[OrderID], [LPA_L4].[LastReportID], [LPA_L4].[ProcedureCodeList], [LPA_L4].[ProcedureDescList], [LPA_L4].[StartDate], [LPA_L4].[FillerOrderNumber], 1 AS [LPFA_6] FROM ([Report] [LPA_L3] INNER JOIN [Order] [LPA_L4] ON [LPA_L3].[ReportID] = [LPA_L4].[LastReportID]) WHERE ( ( ( [LPA_L3].[ReportStatusID] = @p1)))) [LPA_L2] WHERE ( ( ( ( ( [LPA_L2].[ReportID] <> @p3) AND ( [LPA_L2].[LastReportID] <> @p5)))) AND [LPA_L2].[OrderID] = [LPA_L2].[OrderID])))))',N'@p1 int,@p3 int,@p5 int',@p1=7,@p3=516398,@p5=516398

And this is without it:

exec sp_executesql N'SELECT [LPLA_4].[ProcedureCodeID], [LPLA_4].[OrderID] FROM [OrderProcedure] [LPLA_4] WHERE ( ( ( EXISTS (SELECT [LPA_L3].[OrderID] FROM ([Report] [LPA_L2] INNER JOIN [Order] [LPA_L3] ON [LPA_L2].[ReportID] = [LPA_L3].[LastReportID]) WHERE ( ( ( ( ( [LPA_L2].[ReportStatusID] = @p1)))) AND [LPLA_4].[OrderID] = [LPA_L3].[OrderID])))))',N'@p1 int',@p1=7

There are two relations of Order and Report tables, one on Order.ReportID and another on Order.LastReportID.

Thanks for that, we'll look into it. Will be next week before we have an answer tho. I hope we can fix it. This is a complex situation where multiple where clauses are moved to other places in the query and at the same time the aliases of the elements they're targeting have to stay correct. That last part is where things go wrong.

Reproduced. End result is ok, but it indeed fetches all rows, which it shouldn't do

[Test]
public void AppendedWhereClauseToProjectionWithNestedQueryTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from c in metaData.Customer
                join o in metaData.Order on c.CustomerId equals o.CustomerId
                where c.Country=="USA"
                    select new
                           {
                               c.CustomerId,
                               o.OrderId,
                               OrderDetails = (from od in metaData.OrderDetail
                                             where od.OrderId == o.OrderId select new { od.ProductId})
                           };
        q = q.Where(x => x.CustomerId != "CHOPS");
        var r = q.ToList();
        var totalODs = (from x in r
                        from od in x.OrderDetails
                        select od).Count();
        Assert.AreEqual(311, totalODs);
    }
}

SQL

-- Q1
SELECT [LPA_L1].[CustomerId],
       [LPA_L1].[OrderId],
       [LPA_L1].[LPFA_6]
FROM   (SELECT [LPA_L2].[CustomerID] AS [CustomerId],
               [LPA_L3].[OrderID]    AS [OrderId],
               1                     AS [LPFA_6]
        FROM   ([Northwind].[dbo].[Customers] [LPA_L2]
                INNER JOIN [Northwind].[dbo].[Orders] [LPA_L3]
                    ON [LPA_L2].[CustomerID] = [LPA_L3].[CustomerID])
        WHERE  ((([LPA_L2].[Country] = @p1)))) [LPA_L1]
WHERE  (((([LPA_L1].[CustomerId] <> @p3)))) 

-- Q2
SELECT [LPLA_4].[ProductID] AS [ProductId],
       [LPLA_4].[OrderID]   AS [OrderId]
FROM   [Northwind].[dbo].[Order Details] [LPLA_4]
WHERE  (((EXISTS
          (SELECT [LPA_L2].[OrderId]
           FROM   (SELECT [LPA_L3].[CustomerID] AS [CustomerId],
                          [LPA_L4].[OrderID]    AS [OrderId],
                          1                     AS [LPFA_6]
                   FROM   ([Northwind].[dbo].[Customers] [LPA_L3]
                           INNER JOIN [Northwind].[dbo].[Orders] [LPA_L4]
                               ON [LPA_L3].[CustomerID] = [LPA_L4].[CustomerID])
                   WHERE  ((([LPA_L3].[Country] = @p1)))) [LPA_L2]
           WHERE  (((([LPA_L2].[CustomerId] <> @p3)))
               AND [LPA_L2].[OrderId] = [LPA_L2].[OrderId])))))       -- ISSUE

Btw, I found a workaround, please check if it works for you in your real query:

var q = from rep in metaData.Report
           join ord in metaData.Order on rep.ReportID equals ord.LastReportID
        where rep.ReportStatusID == (int)ReportStatus.Final
            select new
        {
                rep.ReportID,
                rep.IsAddendum,
                rep.LastSignDate,
                ord.OrderID,
                ord.LastReportID,
                ord.ProcedureCodeList,
                ord.ProcedureDescList,
                ord.StartDate,
                ord.FillerOrderNumber,
                Procedures = ord.OrderProcedures.Select(op =>new { op.ProcedureCodeID } )   // implied correlation predicate
        };

        if (reportID > 0)
            q = q.Where(x => x.ReportID != reportID && x.LastReportID != reportID);

See the Procedures projection. Here the explicitly specified correlation predicate is implied through the navigation to OrderProcedures. This then doesn't cause the alias issue as the predicate the issue is in (the explicitly specified correlation predicate) isn't in the tree but added properly by the linq provider. When I use this construction instead of the explicitly specified predicate, it works in my repro query.

This might help you work around the slow downs now, till we fixed the issue.

Thank you Frans. The implied correlation predicate works around the issue and generates the correct subquery.

It appears the re-aliaser targets the wrong side at times If I switch the fields in the correlation predicate in:

OrderDetails = (from od in metaData.OrderDetail
                                             where od.OrderId == o.OrderId select new { od.ProductId})

to

OrderDetails = (from od in metaData.OrderDetail
                                             where o.OrderId == od.OrderId select new { od.ProductId})

it produces the right predicate too. The re-aliaser has to be more clever than just picking one side always. Fixing...

Fixed in hotfix builds 5.8.6 and 5.9.2