TomDog wrote:

Oops not so fast - these two in Barf() are still failing

var sohquery = from soh in AWHelper.MetaData.SalesOrderHeader select soh;

var w = (from soh in sohquery 
              from sod in soh.SalesOrderDetail
               select new { soh.SalesOrderId, soh.Customer.AccountNumber, soh.CreditCard.CardNumber }).ToList();

query = from soh in AWHelper.MetaData.SalesOrderHeader
              from sod in soh.SalesOrderDetail
              select soh;

//Bad alias? error
var x = (from soh in query
               select new {soh.SalesOrderId, soh.Customer.AccountNumber}).ToList();

hmm...

LeftJoinUsingDefaultIfEmptyToFetchCustomersWithoutAnOrder doesn't crash however

      var q4 = from customer in customers
               from soh in customer.SalesOrderHeader.Where(soh => soh.SalesOrderId < 10).DefaultIfEmpty()
               select new {customer.CustomerId, soh.SalesOrderId};
      var x = q4.ToList();

produces

SELECT DISTINCT TOP 4 [lpa_l1].[customerid] AS [customerid],
                      [lpa_l2].[salesorderid] AS [salesorderid]
FROM   ([adventureworks].[sales].[customer] [lpa_l1]
        LEFT JOIN [adventureworks].[sales].[salesorderheader] [lpa_l2]
          ON [lpa_l1].[customerid] = [lpa_l2].[customerid])
WHERE  (((([lpa_l2].[salesorderid] < @SalesOrderId1))))

instead of

SELECT TOP (5) [t2].[CustomerID], [t2].[value] AS [SalesOrder]
FROM (
    SELECT [t0].[CustomerID], [t1].[SalesOrderID] AS [value]
    FROM [Sales].[Customer] AS [t0]
    LEFT OUTER JOIN [Sales].[SalesOrderHeader] AS [t1] ON ([t1].[SalesOrderID] < @p0) AND ([t1].[CustomerID] = [t0].[CustomerID])
    ) AS [t2]

like linq to sql does(debatable but filter should be in the ON clause)

SD.LLBLGen.Pro.LinqSupportClasses.NET35 Assembly Version: 2.6.0.0 Win32 Version: 2.6.08.0910

This is interesting... (one has top4 other top5 btw... ) The sets aren't equal. The first returns 0 rows, as the set of Salesorderheaders is empty (0 rows match the where clause). The second, linq to sql one, does return rows, as it joins an empty set with salesorderheader.

Looking at the linq query, our code isn't correct.

I.o.w.: pulling the Where clauses out of the query was a mistake: they should be pushed into the On clause and if there's none, be used as an On clause + make the query an inner join.

the code I wrote shouldn't extract where clauses... I'll experiment a bit. Good catch!

I have the where stuff working now...

The alias crap still fails... Have to adjust some unittests as well, as they now return different rows (which are correct).

(edit). It's not always the case to add the where clause to the relation.... ->

var q1 = from customer in metaData.Customer
         where customer.CustomerId<100
         from soh in customer.SalesOrderHeaderCollection.DefaultIfEmpty()
         select new { customer.CustomerId, soh.SalesOrderId };

Here, it has to be moved to the outer query like I managed to do yesterday. Looking at it... it seems that this is only important for Where clauses which are referred by DefaultIfEmpty clauses... If the where isn't referred by a DefaultIfEmpty -> move it to outer query.

A tweak to the visitor would do...

Got it... well.. it wasn't a silly tweak...

At one time, everything except one test worked. The issue left was fixed and something else failed... repeat.

Finally I got everything working, and also understand why. My head hurts....

Close but no cigar, this is still crashing:

var sohquery = from soh in AWHelper.MetaData.SalesOrderHeader select soh;

var w = (from soh in sohquery 
              from sod in soh.SalesOrderDetail
               select new { soh.SalesOrderId, soh.Customer.AccountNumber, soh.CreditCard.CardNumber }).ToList();

TomDog wrote:

Close but no cigar, this is still crashing:

var sohquery = from soh in AWHelper.MetaData.SalesOrderHeader select soh;

var w = (from soh in sohquery 
              from sod in soh.SalesOrderDetail
               select new { soh.SalesOrderId, soh.Customer.AccountNumber, soh.CreditCard.CardNumber }).ToList();

This works as expected:

var q = from soh in metaData.SalesOrderHeader
        from sod in soh.SalesOrderDetailCollection
        select soh;

var x = (from soh in q
         select new { soh.SalesOrderId, soh.Customer.AccountNumber, soh.CreditCard.CardNumber }).ToList();

Which to me is the same query... ?

And this:

var sohquery = from soh in metaData.SalesOrderHeader select soh;

var w = (from soh in sohquery
         from sod in soh.SalesOrderDetailCollection
         select new { soh.SalesOrderId, soh.Customer.AccountNumber, soh.CreditCard.CardNumber }).ToList();

also works here... What exactly does crash?

I'll build a RTM build with the build system and attach it to this post. Stay tuned.

(edit) Attached. Be sure you overwrite the old one. if you install these dlls in the GAC (please don't) first remove the old one as this one has the same Fileversion as the one earlier posted today...

Both those fail for me the same, something to do with the casing of CreditCardId Application_ThreadException: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Invalid column name 'CreditCardID'..

SELECT [LPA_L1].[SalesOrderId],
       [LPA_L3].[AccountNumber],
       [LPA_L4].[CardNumber]
FROM   ((((SELECT [LPLA_1].[SalesOrderID] AS [SalesOrderId],
                  [LPLA_1].[RevisionNumber],
                  [LPLA_1].[OrderDate],
                  [LPLA_1].[DueDate],
                  [LPLA_1].[ShipDate],
                  [LPLA_1].[Status],
                  [LPLA_1].[OnlineOrderFlag],
                  [LPLA_1].[SalesOrderNumber],
                  [LPLA_1].[PurchaseOrderNumber],
                  [LPLA_1].[AccountNumber],
                  [LPLA_1].[CustomerID] AS [CustomerId],
                  [LPLA_1].[ContactID] AS [ContactId],
                  [LPLA_1].[SalesPersonID] AS [SalesPersonId],
                  [LPLA_1].[TerritoryID] AS [TerritoryId],
                  [LPLA_1].[BillToAddressID] AS [BillToAddressId],
                  [LPLA_1].[ShipToAddressID] AS [ShipToAddressId],
                  [LPLA_1].[ShipMethodID] AS [ShipMethodId],
                  [LPLA_1].[CreditCardID] AS [CreditCardId],
                  [LPLA_1].[CreditCardApprovalCode],
                  [LPLA_1].[CurrencyRateID] AS [CurrencyRateId],
                  [LPLA_1].[SubTotal],
                  [LPLA_1].[TaxAmt],
                  [LPLA_1].[Freight],
                  [LPLA_1].[TotalDue],
                  [LPLA_1].[Comment],
                  [LPLA_1].[rowguid] AS [Rowguid],
                  [LPLA_1].[ModifiedDate]
           FROM   [AdventureWorks].[Sales].[SalesOrderHeader] [LPLA_1]) [LPA_L1]
          INNER JOIN [AdventureWorks].[Sales].[SalesOrderDetail] [LPA_L2]
            ON ([LPA_L1].[SalesOrderId] = [LPA_L2].[SalesOrderID]))
         INNER JOIN [AdventureWorks].[Sales].[Customer] [LPA_L3]
           ON [LPA_L3].[CustomerID] = [LPA_L1].[CustomerId])
        LEFT JOIN [AdventureWorks].[Sales].[CreditCard] [LPA_L4]
          ON [LPA_L4].[CreditCardID] = [LPA_L1].[CreditCardID])

Run my code and you'll see it.

"Run my code" ... Which routine? You mean the code I posted?

I see there's indeed a casing problem: the derived table causes it. Here it works because the sqlserver 2005 instance is case insensitive.

I'll try to see if I can fix this, either by getting rid of the derived table, or by making the correction routine for these things also work on relations

Otis wrote:

"Run my code" ... Which routine? You mean the code I posted?

Here it works because the sqlserver 2005 instance is case insensitive.

The Barf routine which yes now contains versions of the two ones you posted, I assumed it worked for your unit tests because maybe your AW project hadn't changed the case of the fields but I see even running my stuff would work for you. btw I'm now having issues with my real code which I haven't reproduced in RapidDevBookCode yet, something to do maybe with the change you did to get the On clause filtering working. I'll get back to you...

TomDog wrote:

Otis wrote:

"Run my code" ... Which routine? You mean the code I posted?

Here it works because the sqlserver 2005 instance is case insensitive.

The Barf routine which yes now contains versions of the two ones you posted, I assumed it worked for your unit tests because maybe your AW project hadn't changed the case of the fields but I see even running my stuff would work for you. btw I'm now having issues with my real code which I haven't reproduced in RapidDevBookCode yet, something to do maybe with the change you did to get the On clause filtering working. I'll get back to you...

The casing issue is in the derived table which has an alias for the CreditCardID field with 'Id' instead of 'ID'. The ON clause in the join with the derived table refers to the field, with 'ID', but that field isn't in the set of the derived table, as it is aliased.

In the runtime a routine is ran to collect all derived table targetting fields which are then corrected for alias mismatches etc. It should pick up this field in the relation properly. So this only pops up in case sensitive databases with the query construct where you project from a nested from with a relation on board. The logical fix would be the correction of the field name in teh ON clause, as the situation of joining with a derived table happens in more situations than just this one.

The corrector finds only the two SalesOrderId fields which need correcting. Looking into it.

Ok I think I reproduced my new problem

    public void LeftJoinUsingDefaultIfEmptyToFetchCustomersWithoutAnOrder()
    {
      var customers = AWHelper.MetaData.Customer.AsQueryable();
      //var customersDerivedTable = customers.Select(customer => customer);
      var customersDerivedTable = from customer in customers select customer; //Using this to force a derived table causes a crash
      customersDerivedTable = customers.Where(c => c.CustomerId > 10); //To force a derived table

//    var q = AWHelper.MetaData.Customer.SelectMany(customer => customer.CustomerAddress, (customer, ca) => new {customer, ca}).GroupJoin(AWHelper.MetaData.SalesOrderHeader, @t => @t.customer.CustomerId, soh => soh.CustomerId, (@t, oc) => new {@t, oc}).SelectMany(@t => @t.oc.DefaultIfEmpty(), (@t, nullableSOH) => new {@t.@t.customer.CustomerId, @t.@t.ca.AddressId, nullableSOH.SalesOrderId});
      AWHelper.TraceOut("ExplicitJoin with Derived Table");
      var q = from customer in customersDerivedTable            
              join soh in (from s in AWHelper.MetaData.SalesOrderHeader where s.SalesPersonId > 22 select s) on customer.CustomerId equals soh.CustomerId into oc
              from nullableSOH in oc.DefaultIfEmpty()

              from ca in customer.CustomerAddress.DefaultIfEmpty()
              select new { customer.CustomerId, ca.AddressId, nullableSOH.SalesOrderId, customer.SalesTerritory.Name };
      if (MaxNumberOfItemsToReturn > 0)
        q = q.Take(MaxNumberOfItemsToReturn);
      salesOrderHeaderEntityBindingSource.DataSource = q;

produces this

SELECT DISTINCT TOP 5 [LPA_L1].[CustomerID] AS [CustomerId],
                      [LPA_L2].[AddressID] AS [AddressId],
                      [LPA_L3].[SalesOrderID] AS [SalesOrderId],
                      [LPA_L4].[Name]
FROM   ((([AdventureWorks].[Sales].[Customer] [LPA_L1]
          LEFT JOIN [AdventureWorks].[Sales].[CustomerAddress] [LPA_L2]
            ON [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID]
               AND (((([LPA_L3].[SalesPersonID] > @SalesPersonId1)))))
         LEFT JOIN [AdventureWorks].[Sales].[SalesOrderHeader] [LPA_L3]
           ON [LPA_L1].[CustomerID] = [LPA_L3].[CustomerID])
        LEFT JOIN [AdventureWorks].[Sales].[SalesTerritory] [LPA_L4]
          ON [LPA_L4].[TerritoryID] = [LPA_L1].[TerritoryID])
WHERE  (((([LPA_L1].[CustomerID] > @CustomerId2))))

which fails with Application_ThreadException: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPA_L3.SalesPersonID" could not be bound..

Change the join order of the SQL and it works OK

SELECT DISTINCT TOP 5 [LPA_L1].[CustomerID] AS [CustomerId],
                      [LPA_L2].[AddressID] AS [AddressId],
                      [LPA_L3].[SalesOrderID] AS [SalesOrderId],
                      [LPA_L4].[Name]
FROM   ((([AdventureWorks].[Sales].[Customer] [LPA_L1]
         LEFT JOIN [AdventureWorks].[Sales].[SalesOrderHeader] [LPA_L3]
           ON [LPA_L1].[CustomerID] = [LPA_L3].[CustomerID])
          LEFT JOIN [AdventureWorks].[Sales].[CustomerAddress] [LPA_L2]
            ON [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID]
               AND (((([LPA_L3].[SalesPersonID] > 1)))))
        LEFT JOIN [AdventureWorks].[Sales].[SalesTerritory] [LPA_L4]
          ON [LPA_L4].[TerritoryID] = [LPA_L1].[TerritoryID])
WHERE  (((([LPA_L1].[CustomerID] > 1))))

(casing issue) Bug in runtime, relationcollection. The second time a join with the same derived table occurs, it doesn't correct the fields in the ON clause targeting the derived table. (CustomerId is corrected in the query, the CreditCardID isn't).

Working on fix.

That relation order issue... that's interesting. I've an idea where that comes from, though how to fix it is another question.

Casing issue: fixed.

Relation order... It's indeed wrong. Looking at the flow of the query, customer - salesorderheader should come first, then customer - customeraddress, then customer - salesterritory

However, customer-customeraddress is first... strange thing is... I can't find why that is... Looking into it.

Well... 1 line of code and it worked . My visitor to extract where clauses crawled over all the joins in the tree when handling joins, but it should stop at the first join it saw (as that would be handled further in the first recursive call) so it extracted the where s.SalesPersonId > 22 from the group join and added it to the wrong relation. It now adds it to the right relation.

I'll attach a .zip with a new ORMSupportclasses build and a new linq provider build (casing issue and onclause updating issue)

(edit) attached.

That fixed the RapidDevBookCode crashes and the error in my real code but I made it a bit more complicated and it crashed again.

     var oc = MetaData.OcOccurrence.AsQueryable();

      var q = (from Occurrence in oc
              join RiskMatrix0 in
                (from RiskMatrix in MetaData.QaocRiskMatrix where RiskMatrix.RiskSource == "O" select RiskMatrix)
                on new {part0 = Occurrence.ReoccurrenceProbability, part1 = Occurrence.SeverityFactor} equals
                new {part0 = RiskMatrix0.LikelihoodCode, part1 = RiskMatrix0.SeverityCode} into join2
              join Likelihood in
                (from Likelihood in MetaData.QaocLikelihood where Likelihood.LikelihoodSource == "O" select Likelihood)
                on Occurrence.ReoccurrenceProbability equals Likelihood.LikelihoodCode into join3
              from Likelihood in join3.DefaultIfEmpty()
              from RiskMatrix in join2.DefaultIfEmpty()
              from OpenAction in Occurrence.OpenActions.DefaultIfEmpty()
              select new
                       {
                         Occurrence.OccurrenceNo,
                       }).Take(5).ToList();

gives: column prefix 'LPA_L4' does not match with a table name or alias name used in the query

SELECT
 DISTINCT TOP 5 [LPA_L1].[Occurrence_No] AS [OccurrenceNo] 
FROM
 ((( [AQD].[oc_Occurrence] [LPA_L1]  
LEFT JOIN
 [AQD].[Oc_Open_Actions] [LPA_L2]  ON  [LPA_L1].[Occurrence_No]=[LPA_L2].[Occurrence_No]) 
LEFT JOIN
 [AQD].[qaoc_Likelihood] [LPA_L3]  ON  ( ( ( [LPA_L4].[Risk_Source] = @RiskSource1)) AND ( ( [LPA_L3].[Likelihood_Source] = @LikelihoodSource2)) AND [LPA_L1].[Reoccurrence_Probability] = [LPA_L3].[Likelihood_Code])) 
LEFT JOIN
 [AQD].[qaoc_RiskMatrix] [LPA_L4]  ON  ( [LPA_L1].[Reoccurrence_Probability] = [LPA_L4].[Likelihood_Code] AND [LPA_L1].[Severity_Factor] = [LPA_L4].[Severity_Code]))

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[AQDLLB.EntityClasses.OcOccurrenceEntity]). GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[AQDLLB.EntityClasses.QaocRiskMatrixEntity]).Where(RiskMatrix => (RiskMatrix.RiskSource = "O")), Occurrence => new <>f__AnonymousType3`2(part0 = Occurrence.ReoccurrenceProbability, part1 = Occurrence.SeverityFactor), RiskMatrix0 => new <>f__AnonymousType3`2(part0 = RiskMatrix0.LikelihoodCode, part1 = RiskMatrix0.SeverityCode), (Occurrence, join2) => new <>f__AnonymousTypee`2(Occurrence = Occurrence, join2 = join2)).GroupJoin(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[ AQDLLB.EntityClasses.QaocLikelihoodEntity]).Where(Likelihood => (Likelihood.LikelihoodSource = "O")), <>h__TransparentIdentifier11 => <>h__TransparentIdentifier11.Occurrence.ReoccurrenceProbability, Likelihood => Likelihood.LikelihoodCode, (<>h__TransparentIdentifier11, join3) => new <>f__AnonymousTypef`2(<>h__TransparentIdentifier11 = <>h__TransparentIdentifier11, join3 = join3)).SelectMany(<>h__TransparentIdentifier12 => <>h__TransparentIdentifier12.join3.DefaultIfEmpty(), (<>h__TransparentIdentifier12, Likelihood) => new <>f__AnonymousType10`2(<>h__TransparentIdentifier12 = <>h__TransparentIdentifier12, Likelihood = Likelihood)).SelectMany(<>h__TransparentIdentifier13 => <>h__TransparentIdentifier13.<>h__TransparentIdentifier12.<>h__TransparentIdentifier11.join2.DefaultIfEmpty(), (<>h__TransparentIdentifier13, RiskMatrix) => new <>f__AnonymousType11`2(<>h__TransparentIdentifier13 = <>h__TransparentIdentifier13, RiskMatrix = RiskMatrix)).SelectMany(<>h__TransparentIdentifier14 => <>h__TransparentIdentifier14.<>h__TransparentIdentifier13.<>h__TransparentIdentifier12.<>h__TransparentIdentifier11.Occurrence.OpenActions.DefaultIfEmpty(), (<>h__TransparentIdentifier14, OpenAction) => new <>f__AnonymousType0`1(OccurrenceNo = <>h__TransparentIdentifier14.<>h__TransparentIdentifier13.<>h__TransparentIdentifier12.<>h__TransparentIdentifier11.Occurrence.OccurrenceNo)).Take(5)

also if i change the order of the joins around in the linq the app seems to hang here: SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll!SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore. FixMaxLengthPrecisionScaleInDerivedTableTargetingFields(System.Collections.Generic.Dictionary< SD.LLBLGen.Pro.ORMSupportClasses.IEntityFieldCore,SD.LLBLGen.Pro.ORMSupportClasses.DerivedTableDefinition> derivedTablePerField = Count =

I think this is because the DefaultIfEmpty stuff adds relations before the left side's relations, not sure. I changed that temporarily this morning but it had only nasty side effects.

Let me spend some more minutes on this.

However, keep in mind that I do know that I'll never reach the 100% bugfree limit with this, as the trial/error way of doing development with this couldn't lead to a possible 100% bugfree situation. I can't spend week after week fixing all kinds of edge cases, if teh queries producing them are fairly bizarre.

also if i change the order of the joins around in the linq the app seems to hang here: SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll!SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore. FixMaxLengthPrecisionScaleInDerivedTableTargetingFields(System.Collections.Generic.Dictionary< SD.LLBLGen.Pro.ORMSupportClasses.IEntityFieldCore,SD.LLBLGen.Pro.ORMSupportClasses.DerivedTableDefinition> derivedTablePerField = Count =

That's due to some issue where a derived table targeting field targets a derived table with the same alias as the field's set does, i.e it ends up in an infinite loop. I haven't found the reason for this, as it's fairly problematic to find. (i.e. the queries are VERY huge and therefore tracking down the exact situation is due to the massive scale of the expression tree involved almost undoable).

this part: [AQD].[qaoc_Likelihood] [LPA_L3] ON ( ( ( [LPA_L4].[Risk_Source] = @RiskSource1)) is odd, as it's the where clause of the first relation. This shouldn't be here, the fix I posted earlier was correcting this...

LinqMetaData metaData = new LinqMetaData(adapter);
var customers = metaData.Customer.AsQueryable();
var q = from customer in customers
        join soh in
            (from s in metaData.SalesOrderHeader where s.SalesPersonId > 22 select s) on customer.CustomerId equals soh.CustomerId into join1
        join ca in 
            (from custaddr in metaData.CustomerAddress where custaddr.AddressId > 100 select custaddr) 
                on customer.CustomerId equals ca.CustomerId into join2
        from xca in join2.DefaultIfEmpty()
        from xsoh in join1.DefaultIfEmpty()
        select new { customer.CustomerId, xca.AddressId, xsoh.SalesOrderId, customer.SalesTerritory.Name };
q = q.Take(10);

var l = q.ToList();

reproduces the sql error. Clearly the salesperson where is added to the custaddr join. That's not good.

This hangs:

LinqMetaData metaData = new LinqMetaData(adapter);
var customers = metaData.Customer.AsQueryable();
var q = from customer in customers
        join ca in 
            (from custaddr in metaData.CustomerAddress where custaddr.AddressId > 100 select custaddr) 
                on customer.CustomerId equals ca.CustomerId into join2
        from xca in join2.DefaultIfEmpty()
        join soh in
            (from s in metaData.SalesOrderHeader where s.SalesPersonId > 22 select s) on customer.CustomerId equals soh.CustomerId into join1
        from xsoh in join1.DefaultIfEmpty()
        select new { customer.CustomerId, xca.AddressId, xsoh.SalesOrderId, customer.SalesTerritory.Name };
q = q.Take(10);

var l = q.ToList();

The reason: SELECT DISTINCT TOP 10 [LPA_L1].[CustomerID] AS [CustomerId], [LPA_L2].[AddressID] AS [AddressId], [LPA_L3].[SalesOrderId], [LPA_L4].[Name] FROM ((( [AdventureWorks].[Sales].[Customer] [LPA_L1] LEFT JOIN [AdventureWorks].[Sales].[CustomerAddress] [LPA_L2]
ON ( ( ( [LPA_L2].[AddressID] > @AddressId1)) AND [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID])) LEFT JOIN ( SELECT [LPA_L3].[SalesOrderId], [LPA_L3].[RevisionNumber], [LPA_L3].[OrderDate], [LPA_L3].[DueDate], [LPA_L3].[ShipDate], [LPA_L3].[Status], [LPA_L3].[OnlineOrderFlag], [LPA_L3].[SalesOrderNumber], [LPA_L3].[PurchaseOrderNumber], [LPA_L3].[AccountNumber], [LPA_L3].[CustomerId], [LPA_L3].[ContactId], [LPA_L3].[SalesPersonId], [LPA_L3].[TerritoryId], [LPA_L3].[BillToAddressId], [LPA_L3].[ShipToAddressId], [LPA_L3].[ShipMethodId], [LPA_L3].[CreditCardId], [LPA_L3].[CreditCardApprovalCode], [LPA_L3].[CurrencyRateId], [LPA_L3].[SubTotal], [LPA_L3].[TaxAmt], [LPA_L3].[Freight], [LPA_L3].[TotalDue], [LPA_L3].[Comment], [LPA_L3].[Rowguid], [LPA_L3].[ModifiedDate] FROM [AdventureWorks].[Sales].[SalesOrderHeader] [LPLA_6]
WHERE ( ( [LPA_L3].[SalesPersonId] > @SalesPersonId2)) ) **[LPA_L3] ** ON [LPA_L1].[CustomerID] = [LPA_L3].[CustomerId]) LEFT JOIN [AdventureWorks].[Sales].[SalesTerritory] [LPA_L4] ON [LPA_L4].[TerritoryID]=[LPA_L1].[TerritoryID])

i.o.w.: it refers to self, hence the endless loop. Ok, one at a time... first the wrong filter placement.

Otis wrote:

That's due to some issue where a derived table targeting field targets a derived table with the same alias as the field's set does, i.e it ends up in an infinite loop. I haven't found the reason for this, as it's fairly problematic to find. (i.e. the queries are VERY huge and therefore tracking down the exact situation is due to the massive scale of the expression tree involved almost undoable).

After your latest build is the first time i've seen it.

Otis wrote:

[AQD].[qaoc_Likelihood] [LPA_L3] ON ( ( ( [LPA_L4].[Risk_Source] = @RiskSource1)) is odd, as it's the where clause of the first relation. This shouldn't be here, the fix I posted earlier was correcting this...

It did - it was only when I added the 'Likelihood' join it came back (before I just had RiskMatrix and OpenAction)

One down (misplaced on clause)

SELECT DISTINCT TOP 10 [LPA_L1].[CustomerID] AS [CustomerId], [LPA_L2].[AddressID] AS [AddressId], [LPA_L3].[SalesOrderID] AS [SalesOrderId], [LPA_L4].[Name] 
FROM ((( [AdventureWorks].[Sales].[Customer] [LPA_L1]  
        LEFT JOIN [AdventureWorks].[Sales].[CustomerAddress] [LPA_L2]  
            ON  ( ( ( [LPA_L2].[AddressID] > @AddressId1)) AND [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID])) 
        LEFT JOIN [AdventureWorks].[Sales].[SalesOrderHeader] [LPA_L3]  
            ON  ( ( ( [LPA_L3].[SalesPersonID] > @SalesPersonId2)) AND [LPA_L1].[CustomerID] = [LPA_L3].[CustomerID])) 
        LEFT JOIN [AdventureWorks].[Sales].[SalesTerritory] [LPA_L4]  ON  [LPA_L4].[TerritoryID]=[LPA_L1].[TerritoryID])

...

and two down... (hanging query)

SELECT DISTINCT TOP 10 [LPA_L1].[CustomerID] AS [CustomerId], [LPA_L2].[AddressID] AS [AddressId], [LPA_L3].[SalesOrderID] AS [SalesOrderId], [LPA_L4].[Name] 
FROM ((( [AdventureWorks].[Sales].[Customer] [LPA_L1]  
        LEFT JOIN [AdventureWorks].[Sales].[CustomerAddress] [LPA_L2]  
            ON  ( ( ( [LPA_L2].[AddressID] > @AddressId1)) AND [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID])) 
        LEFT JOIN [AdventureWorks].[Sales].[SalesOrderHeader] [LPA_L3]  
            ON  ( ( ( [LPA_L3].[SalesPersonID] > @SalesPersonId2)) AND [LPA_L1].[CustomerID] = [LPA_L3].[CustomerID])) 
        LEFT JOIN [AdventureWorks].[Sales].[SalesTerritory] [LPA_L4]  ON  [LPA_L4].[TerritoryID]=[LPA_L1].[TerritoryID])

All tests pass.... I'll attach a new build. I extract the where clauses in step 3 before the final phase from a groupjoin right side. This is the only situation where things can go wrong, as these are referred by defaultifempty expressions somewhere else in the expression tree. They're handled together with the groupjoin, and pulled forward and used when the defaultifempty refers to the groupjoin. Et voila

I also added hang-prevention (but that will cause a query to crash) in the ormsupportclasses. At least the query then looks bad so one can track down what's wrong and report it to us.

(edit). Attached.

Yessss - break out the Champagne! well apart from one test query which gives out a

Application_ThreadException: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: Internal error: QueryExpression of non-entity type without projection encountered without prior source with projection. Did you misplace a where/orderby construct in a multiple-from containing query?

but I don't really care about that cause my main (even nastier)query worked. But all the same I'll gives details later. Well done Frans - thanks for sticking with it!

TomDog wrote:

Yessss - break out the Champagne! well apart from one test query which gives out a

Application_ThreadException: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: Internal error: QueryExpression of non-entity type without projection encountered without prior source with projection. Did you misplace a where/orderby construct in a multiple-from containing query?

but I don't really care about that cause my main (even nastier)query worked. But all the same I'll gives details later. Well done Frans - thanks for sticking with it!

cool!

Btw, what was the query which failed with that error? it might still be a situation some people might run into (and it's likely due to a derived table being created without a projection...)

OK i've reduced it down to this:

      var oc = MetaData.OcOccurrence.AsQueryable();
      oc = oc.Where(occ => occ.OccurrenceNo == occurrenceNoTextBox.Text); //Take this out and it works
      var q = from Occurrence in oc
              from OccTypeClass in Occurrence.OcOccurrenceTypeClassificat
              //orderby Occurrence.OccurrenceDateTime descending , Occurrence.OccurrenceNo descending //Leave this in and it works
              select new
                       {
                         OccTypeClass.OccurrenceTypeCode,
                         OutstandingActionsjoin = (from oa in MetaData.OcOccurrenceInvestigation
                                                   from finding in oa.QaocFinding
                                                   from action in finding.QaocAction
                                                   where statuses.Contains(action.ActionStatus)
                                                   where oa.OccurrenceNo == Occurrence.OccurrenceNo
                                                   select oa).Count()
                       };

      if (MaxNumberOfItemsToReturn > 0)
        q = q.Take(MaxNumberOfItemsToReturn);

It crashes as is. Note the comments -if either of those two lines are enable/disabled it works.

And if you move the where to just above the select ? It's odd though... I'll see if I can repro it with some query. The query itself doesn't look really odd.

Otis wrote:

And if you move the where to just above the select ?

the where only cause the crash if it is where it is - just my old trick of using a where to force a derived table

Repro query in adventureworks:

LinqMetaData metaData = new LinqMetaData(adapter);
var customers = metaData.Customer.AsQueryable();
customers = customers.Where(c => c.CustomerId > 10);
var q = from c in customers
        from soh in c.SalesOrderHeaderCollection
        select new
        {
            Scalar = (from a in metaData.Address
                      from ca in a.CustomerAddressCollection
                      where ca.CustomerId == c.CustomerId
                      select a).Count()
        };

(it's a nonsense query, I just grabbed elements here and there to repro it. ). The scalar is essential as well as the nested from inside the scalar as well as the nested froms earlier in the query.

Looking into why this dies...

Well. it IS a bug So I'll fix it. (completely unrelated to the previous issues though)

The reason this bug pops up is the following. In all its wisdom, MS' C# team decided that if you do: from c in .. join ... select something

the select is placed inside the Join, as the Join already has a projection expression, so this saved them a call. The problem is: when traversing the tree, a linq provider now has to check whether these projections in a join should be taken into account when merging tree elements together (which leads to derived tables) or not (which leads to merging of just the relationships) Because their own approach is a very different one (build large stacks of derived tables and then run an optimizer to reoptimize it) they dont run into this much, but we do.

The provider uses an, admittedly stupid, flag which is set once a projection is seen (so it can flag projections in joins to be ignored.

You probably guessed it: the projection in the scalar triggers the flag! so the main query's projection is nowhere to be found as the projection to use, the one in the join, is flagged to be ignored!

So obvious fix: make the determination more clever. With our new visitor for the extraction of where's we already have build this in, so it's not really that hard. It will be monday though when this is available...

Moving the where or adding the orderby fixes this, because the last expression BEFORE the select isn't one with a projection anymore, so the compiler will append the call to Select.

VB.NET's compiler is less stupid in this case, so in there it should work. (but haven't checked)