Repro against AdventureWorks.

Created a typed list based on SalesOrderHeader with AddressLine1 from the associated shipping and billing address. Designer generates this LINQ

        /// <summary>Gets the query to fetch the typed list Sales.SalesOrder</summary>
        /// <returns>IQueryable</returns>
        public IQueryable<AW.Data.TypedListClasses.SalesOrderRow> GetSalesOrderTypedList()
        {
            var current0 = this.SalesOrderHeader;
            var current1 = from sales_SalesOrderHeader in current0
                           join shipping in this.Address on sales_SalesOrderHeader.ShipToAddressID equals shipping.AddressID
                           join billingAddress in this.Address on sales_SalesOrderHeader.BillToAddressID equals billingAddress.AddressID
                           select new {shipping, sales_SalesOrderHeader, billingAddress };
            return current1.Select(v=>new AW.Data.TypedListClasses.SalesOrderRow() { ShipToAddressID = v.sales_SalesOrderHeader.ShipToAddressID, SalesOrderID = v.sales_SalesOrderHeader.SalesOrderID, SalesOrderNumber = v.sales_SalesOrderHeader.SalesOrderNumber, BillToAddressID = v.sales_SalesOrderHeader.BillToAddressID, Comment = v.sales_SalesOrderHeader.Comment, ShippingAddressLine1 = v.shipping.AddressLine1, BillingAddressLine1 = v.billingAddress.AddressLine1 });
        }

when executed in LINQPad

GetSalesOrderTypedList().Where(s=>s.BillToAddressID!=s.ShipToAddressID)

ShippingAddressLine1 and BillingAddressLine1 are identical when obviously they shouldn't be. SQL is

SELECT [LPA_L1].[ShipToAddressID],
       [LPA_L1].[SalesOrderID],
       [LPA_L1].[SalesOrderNumber],
       [LPA_L1].[BillToAddressID],
       [LPA_L1].[Comment],
       [LPA_L1].[ShippingAddressLine1],
       [LPA_L1].[BillingAddressLine1]
FROM   (SELECT [LPA_L2].[ShipToAddressID],
               [LPA_L2].[SalesOrderID],
               [LPA_L2].[SalesOrderNumber],
               [LPA_L2].[BillToAddressID],
               [LPA_L2].[Comment],
               [LPA_L2].[AddressLine1] AS [ShippingAddressLine1],
               [LPA_L2].[AddressLine1] AS [BillingAddressLine1]
        FROM   (SELECT [LPA_L4].[AddressID],
                       [LPA_L4].[AddressLine1],
                       [LPA_L4].[AddressLine2],
                       [LPA_L4].[City],
                       [LPA_L4].[ModifiedDate],
                       [LPA_L4].[PostalCode],
                       [LPA_L4].[rowguid]        AS [Rowguid],
                       [LPA_L4].[StateProvinceID],
                       1 /* @p2 */                      AS [LPFA_8],
                       [LPA_L3].[AccountNumber],
                       [LPA_L3].[BillToAddressID],
                       [LPA_L3].[Comment],
                       [LPA_L3].[ContactID],
                       [LPA_L3].[CreditCardApprovalCode],
                       [LPA_L3].[CreditCardID],
                       [LPA_L3].[CurrencyRateID],
                       [LPA_L3].[CustomerID],
                       [LPA_L3].[DueDate],
                       [LPA_L3].[Freight],
                       [LPA_L3].[ModifiedDate]  AS [ModifiedDate0],
                       [LPA_L3].[OnlineOrderFlag],
                       [LPA_L3].[OrderDate],
                       [LPA_L3].[PurchaseOrderNumber],
                       [LPA_L3].[RevisionNumber],
                       [LPA_L3].[rowguid]        AS [Rowguid1],
                       [LPA_L3].[SalesOrderID],
                       [LPA_L3].[SalesOrderNumber],
                       [LPA_L3].[SalesPersonID],
                       [LPA_L3].[ShipDate],
                       [LPA_L3].[ShipMethodID],
                       [LPA_L3].[ShipToAddressID],
                       [LPA_L3].[Status],
                       [LPA_L3].[SubTotal],
                       [LPA_L3].[TaxAmt],
                       [LPA_L3].[TerritoryID],
                       [LPA_L3].[TotalDue],
                       1 /* @p4 */                      AS [LPFA_9],
                       [LPA_L5].[AddressID]    AS [AddressID2],
                       [LPA_L5].[AddressLine1]  AS [AddressLine13],
                       [LPA_L5].[AddressLine2]  AS [AddressLine24],
                       [LPA_L5].[City]          AS [City5],
                       [LPA_L5].[ModifiedDate]  AS [ModifiedDate6],
                       [LPA_L5].[PostalCode]      AS [PostalCode7],
                       [LPA_L5].[rowguid]        AS [Rowguid8],
                       [LPA_L5].[StateProvinceID] AS [StateProvinceID9],
                       1 /* @p6 */                      AS [LPFA_10]
                FROM   (([AdventureWorks].[Sales].[SalesOrderHeader] [LPA_L3]
                         INNER JOIN [AdventureWorks].[Person].[Address] [LPA_L4]
                             ON [LPA_L3].[ShipToAddressID] = [LPA_L4].[AddressID])
                        INNER JOIN [AdventureWorks].[Person].[Address] [LPA_L5]
                            ON [LPA_L3].[BillToAddressID] = [LPA_L5].[AddressID])) [LPA_L2]) [LPA_L1]
WHERE  ((([LPA_L1].[BillToAddressID] <> [LPA_L1].[ShipToAddressID])))

looking at

               [LPA_L2].[AddressLine1] AS [ShippingAddressLine1],
               [LPA_L2].[AddressLine1] AS [BillingAddressLine1]

you can see why they are the same.

Version 5.2.3

I think the error is in the linq provider, as the LINQ query itself looks OK to me (nothing is joined wrong it seems). Did you spot an error in the linq query itself that I'm overlooking?

Will look into it.

Otis wrote:

I think the error is in the linq provider, as the LINQ query itself looks OK to me (nothing is joined wrong it seems). Did you spot an error in the linq query itself that I'm overlooking?

I agree, the LINQ looks legit to me, albeit not how I would write it by hand. Interestingly the projection generated for the equivalent Derived Model is closer to what I would write ... and works .

private static System.Linq.Expressions.Expression<Func<AW.Data.EntityClasses.SalesOrderHeaderEntity, AW.DerivedModel.DtoClasses.SalesSalesOrderHeader>> CreateProjectionFunc()
{
            return p__0 => new AW.DerivedModel.DtoClasses.SalesSalesOrderHeader()
            {
                AccountNumber = p__0.AccountNumber,
                BillingAddressAddressLine1 = p__0.BillingAddress.AddressLine1,
                BillToAddressID = p__0.BillToAddressID,
                Comment = p__0.Comment,
                SalesOrderID = p__0.SalesOrderID,
                SalesOrderNumber = p__0.SalesOrderNumber,
                ShippingAddressAddressLine1 = p__0.ShippingAddress.AddressLine1,
                ShipToAddressID = p__0.ShipToAddressID,
            };
}

It's related to the wrapping of entities in a select, where multiple fields with the same name can pop up and in general this isn't a problem as a hand-written query would almost never use that kind of system (it would alias the fields already in a projection somewhere). The typedlists use the verbose linq stuff though to be able to handle left/right joins without a lot of extra complex code to generate them.

Adding tracking for this and code to correct the field names properly fixed the problem, but we still have 1 failing test (out of 1100), which is caused by a subtle bug which now pops up and which otherwise wouldn't be a problem: if you join the same entity in a tree a couple of times, it will start to pick the wrong one after 2 iterations, which goes unnoticed as normally it's the same entity. However in this case, as we need the original alias source to determine if the target field is a renamed field on the targeted derived table (still with me? ) it pops up to be a problem as it picks the wrong side of the join and thus the wrong original source alias, and thus modifies the wrong field.

I hope we have a proper fix tomorrow.

(edit) all fixed. See hotfix builds 5.2.5 and 5.3.2

Thanks Frans for the fast turn around, I'll happily take your word on what's going While your thinking about that sort of stuff I've found one more issue which I can't repro anywhere else so far.

var current1 = from risk_RiskReview in RiskReview
     join sponsoringManager in this.StaffMember on risk_RiskReview.SponsoringManagerID equals sponsoringManager.StaffMemberID into joinresult_2
      from sponsoringManager in joinresult_2.DefaultIfEmpty()
    select new {risk_RiskReview, sponsoringManager};
current1.Select(v => new 
{ 
  DepartmentID = v.risk_RiskReview.DepartmentID, 
  WRSortKey = v.risk_RiskReview.WRSortKey
}).Dump();

generates this SQL

SELECT [LPA_L1].[DepartmentID],
       [LPA_L1].[F12_26]
FROM   (SELECT [LPA_L3].[Actual_End_Date]            AS [F12_0],
               [LPA_L3].[Actual_Hours]              AS [F12_1],
               [LPA_L3].[Actual_Start_Date]        AS [F12_2],
               [LPA_L3].[Current_Step_ID]            AS [F12_3],
               [LPA_L3].[Department]                  AS [F12_4],
               [LPA_L3].[Estimated_hours]            AS [F12_5],
               [LPA_L3].[Objective]                AS [F12_6],
               [LPA_L3].[On_Hold]                    AS [F12_7],
               [LPA_L3].[Original_Closed_Date]      AS [F12_8],
               [LPA_L3].[Owner_ID]                  AS [F12_9],
               [LPA_L3].[PrioritySort]              AS [F12_10],
               [LPA_L3].[Publish_Feedback]          AS [F12_11],
               [LPA_L3].[Review_Date]                AS [F12_12],
               [LPA_L3].[Scope]                    AS [F12_13],
               [LPA_L3].[Staff_Member_Auditee_ID]    AS [F12_14],
               [LPA_L3].[State]                    AS [F12_15],
               [LPA_L3].[Investigation_Summary]    AS [F12_16],
               [LPA_L3].[Synopsis]                  AS [F12_17],
               [LPA_L3].[Target_End_Date]            AS [F12_18],
               [LPA_L3].[Target_Start_Date]        AS [F12_19],
               [LPA_L3].[Title]                    AS [F12_20],
               [LPA_L3].[TypeGroup]                AS [F12_21],
               [LPA_L3].[WR_Number]                AS [F12_22],
               [LPA_L3].[WR_Period]                AS [F12_23],
               [LPA_L3].[WR_Priority]                AS [F12_24],
               [LPA_L3].[WR_Priority_Level_ID]      AS [F12_25],
               [LPA_L3].[WR_Sort_Key]                AS [F12_26],
               [LPA_L3].[WR_Type]                    AS [F12_27],
               [LPA_L3].[Assumptions]                AS [F9_28],
               [LPA_L3].[Context]                    AS [F9_29],
               [LPA_L3].[People_Present]              AS [F9_30],
               [LPA_L3].[Purpose]                    AS [F9_31],
               [LPA_L3].[Restricted]                  AS [F9_32],
               [LPA_L3].[Sponsoring_Manager_ID]    AS [F9_33],
               [LPA_L3].[Stakeholders]              AS [F9_34],
               [LPA_L3].[Finding_No]                  AS [F7_35],
               [LPA_L3].[Hazard_No]                AS [F8_35],
               [LPA_L3].[Risk_No]                    AS [F10_35],
               1 /* @p2 */                                  AS [LPFA_6],
               [LPA_L4].[Computer_Username]        AS [ComputerUsername],
               [LPA_L4].[Created_On]                  AS [CreatedOn],
               [LPA_L4].[Daily_Login_Fails]        AS [DailyLoginFails],
               [LPA_L4].[Department_ID]            AS [DepartmentID],
               [LPA_L4].[Email_Address]            AS [EmailAddress],
               [LPA_L4].[Enforce_Password_Expiration] AS [EnforcePasswordExpiration],
               [LPA_L4].[Enforce_Password_Policy]    AS [EnforcePasswordPolicy],
               [LPA_L4].[Externally_Managed]          AS [ExternallyManaged],
               [LPA_L4].[External_StaffMember_Ref]  AS [ExternalStaffMemberRef],
               [LPA_L4].[Fax_Number]                  AS [FaxNumber],
               [LPA_L4].[Last_LoggedOn]            AS [LastLoggedOn],
               [LPA_L4].[Last_Login_Fail_On]          AS [LastLoginFailOn],
               [LPA_L4].[Locked],
               [LPA_L4].[Locked_On]                AS [LockedOn],
               [LPA_L4].[Party_ID]                  AS [PartyID],
               [LPA_L4].[Password],
               [LPA_L4].[Password_Last_Changed_On]  AS [PasswordLastChangedOn],
               [LPA_L4].[Phone_Number]              AS [PhoneNumber],
               [LPA_L4].[Portal_Password_Hash]      AS [PortalPasswordHash],
               [LPA_L4].[Position],
               [LPA_L4].[Require_Password_Change]    AS [RequirePasswordChange],
               [LPA_L4].[Staff_Member_ID]            AS [StaffMemberID],
               [LPA_L4].[Staff_Member_Name]        AS [StaffMemberName],
               [LPA_L4].[Status],
               1 /* @p4 */                                  AS [LPFA_7]
        FROM   ([AQD].[qaoc_Work_Request_View] [LPA_L3]
                LEFT JOIN [AQD].[gn_Staff_Member] [LPA_L4]
                    ON [LPA_L3].[Sponsoring_Manager_ID] = [LPA_L4].[Staff_Member_ID])
        WHERE  ((([LPA_L3].[TypeGroup] IN (15 /* @p5 */, 14 /* @p6 */, 12 /* @p7 */, 13 /* @p8 */, 16 /* @p9 */)
               OR [LPA_L3].[TypeGroup] IS NULL)))) [LPA_L1]

Problem is DepartmentID is coming from the gn_Staff_Member table rather than qaoc_Work_Request_View

take away the left join and its fine e.g.

               join sponsoringManager in this.StaffMember on risk_RiskReview.SponsoringManagerID equals sponsoringManager.StaffMemberID 
               select new {risk_RiskReview, sponsoringManager};

This fails with the fix I made for your initial issue as well? If so, did it work before this fix or did it fail regardless?

Otis wrote:

This fails with the fix I made for your initial issue as well? If so, did it work before this fix or did it fail regardless?

It fails the same before and after, I think - I haven't gone back and checked. But I have reproed on AdventureWorks

        public IQueryable<AW.Data.TypedListClasses.TransactionHistoryDtoRow> GetTransactionHistoryDtoTypedList()
        {
            var current0 = this.TransactionHistory;
            var current1 = from production_TransactionHistory in current0
                           join production_Product in this.Product on production_TransactionHistory.ProductID equals production_Product.ProductID into joinresult_1
                           from production_Product in joinresult_1.DefaultIfEmpty()
                           select new {production_Product, production_TransactionHistory };
            return current1.Select(v=>new AW.Data.TypedListClasses.TransactionHistoryDtoRow() { TransactionID = v.production_TransactionHistory.TransactionID, ModifiedDateTh = v.production_TransactionHistory.ModifiedDate, ModifiedDate = v.production_Product.ModifiedDate });
        }

generates

SELECT [LPA_L1].[F4_7],
       [LPA_L1].[ModifiedDate] AS [ModifiedDateTh],
       [LPA_L1].[ModifiedDate]
FROM   (SELECT [LPA_L4].[Class],
               [LPA_L4].[Color],
               [LPA_L4].[DaysToManufacture],
               [LPA_L4].[DiscontinuedDate],
               [LPA_L4].[FinishedGoodsFlag],
               [LPA_L4].[ListPrice],
               [LPA_L4].[MakeFlag],
               [LPA_L4].[ModifiedDate],
               [LPA_L4].[Name],
               [LPA_L4].[ProductID],
               [LPA_L4].[ProductLine],
               [LPA_L4].[ProductModelID],
               [LPA_L4].[ProductNumber],
               [LPA_L4].[ProductSubcategoryID],
               [LPA_L4].[ReorderPoint],
               [LPA_L4].[rowguid]             AS [Rowguid],
               [LPA_L4].[SafetyStockLevel],
               [LPA_L4].[SellEndDate],
               [LPA_L4].[SellStartDate],
               [LPA_L4].[Size],
               [LPA_L4].[SizeUnitMeasureCode],
               [LPA_L4].[StandardCost],
               [LPA_L4].[Style],
               [LPA_L4].[Weight],
               [LPA_L4].[WeightUnitMeasureCode],
               1 /* @p2 */                           AS [LPFA_6],
               [LPA_L3].[ActualCost]           AS [F4_0],
               [LPA_L3].[ModifiedDate]       AS [F4_1],
               [LPA_L3].[ProductID]         AS [F4_2],
               [LPA_L3].[Quantity]           AS [F4_3],
               [LPA_L3].[ReferenceOrderID]   AS [F4_4],
               [LPA_L3].[ReferenceOrderLineID] AS [F4_5],
               [LPA_L3].[TransactionDate]     AS [F4_6],
               [LPA_L3].[TransactionID]     AS [F4_7],
               [LPA_L3].[TransactionType]     AS [F4_8],
               1 /* @p4 */                           AS [LPFA_7]
        FROM   ([AdventureWorks].[Production].[TransactionHistory] [LPA_L3]
                LEFT JOIN [AdventureWorks].[Production].[Product] [LPA_L4]
                    ON [LPA_L3].[ProductID] = [LPA_L4].[ProductID])) [LPA_L1]

TransactionHistoryEntity is the root in a Target-per-Entity Hierarchy

Thanks, reproduced with this test:

[Test]
public void EntityLeftJoinedWithTPEWrappedQueryTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var current0 = metaData.Customer;
        var current1 = from c in current0
                       join soh in metaData.SalesOrderHeader on c.CustomerId equals soh.CustomerId
                       where c.CustomerId==676
                       select new {c, soh};
        var q = current1.Select(x => new {x.c.Rowguid, SohRG = x.soh.Rowguid, CID = x.c.CustomerId});
        var results = q.ToList();
        Assert.AreEqual(676, results[0].CID);
        Assert.AreEqual(Guid.Parse("2AA331A0-6448-4EA9-9A92-0EBF254BDCD3"), results[0].Rowguid);
        Assert.AreEqual(Guid.Parse("79B65321-39CA-4115-9CBA-8FE0903E12E6"), results[0].SohRG);
    }
}

indeed, it picks the wrong fields, likely due to the name mangling because of the inheritance. Will see how to make this work too. (Left join isn't required to reproduce it) It can be reproduced as long as there's a wrapping of the inner query, which causes fields to be renamed and thus it fails. Removing the left join in your situation doesn't cause a wrapping and therefore it works. The 'where' I introduced also causes a wrapping to be done which reproduces it too.

Well, this turned out to be an easy fix As all fields in an inheritance hierarchy are aliased as Fn_m, they were actually 'renamed' fields in the projection but weren't seen as such. Seeing them as renamed fields, made the fix introduced earlier work as planned and everything should work now OK.

Please download teh latest hotfix build for v5.2.5 and v5.3.2 to get the fix (or update through nuget, prerelease builds)

Otis wrote:

Please download teh latest hotfix build for v5.2.5 and v5.3.2 to get the fix (or update through nuget, prerelease builds)

That fixed it for my AdventureWorks example but my real code remains unchanged. The date of SD.LLBLGen.Pro.ORMSupportClasses.dll is 5/12/2017 3:07:54 PM so I think it's got the right one...

I've actually just switched from using Typed Lists to Derived Models so the issue is a little moot.

Hmm. Weird that the original code fails still... As it looks like it's really the same issue. The fix required a field to be renamed to be logged in the tracker, and it never was logged as such (even though it is renamed) and thus fails to find the right one back.

The only difference I see is that the field in the original entity was called 'Department' and it was renamed as 'DepartmentId' in the designer ?

Otis wrote:

The only difference I see is that the field in the original entity was called 'Department' and it was renamed as 'DepartmentId' in the designer ?

qaoc_Work_Request_View.Department goes to DepartmentID and gn_Staff_Member.Department_ID also goes to DepartmentID.

'goes to' means, is target field of 'DepartmentID' field in entity? Perhaps that's the difference in this case and it fails because of that. I'll do some additional tests to check whether that's true.

Otis wrote:

'goes to' means, is target field of 'DepartmentID' field in entity? Perhaps that's the difference in this case and it fails because of that. I'll do some additional tests to check whether that's true.

Yep DepartmentID in both entities

Well, that's not it. I rewrote my test to use TerritoryID in both customer and salesorderheader, and renamed the TerritoryID field in the customer table to TerrID (field in the entity is still 'territoryid') and altered the data abit so territoryid in salesorderheader for that particular customer is different. This is equal to your failing situation, I think.

Test runs fine, query is OK. So I don't know what else to setup to make it fail...

[Test]
public void EntityLeftJoinedWithTPEWrappedQueryTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var current0 = metaData.Customer;
        var current1 = from c in current0
                       join soh in metaData.SalesOrderHeader on c.CustomerId equals soh.CustomerId
                       where c.CustomerId==676
                       select new {c, soh};
        var q = current1.Select(x => new {x.c.TerritoryId, SohTID = x.soh.TerritoryId, CID = x.c.CustomerId});
        var results = q.ToList();
        Assert.AreEqual(676, results[0].CID);
        Assert.AreEqual(5, results[0].TerritoryId);
        Assert.AreEqual(6, results[0].SohTID);
    }
}

query

SELECT [LPA_L1].[F1_5]      AS [TerritoryId],
       [LPA_L1].[TerritoryId] AS [SohTID],
       [LPA_L1].[F1_1]      AS [CID]
FROM   (SELECT [LPA_L3].[AccountNumber]   AS [F1_0],
               [LPA_L3].[CustomerID]      AS [F1_1],
               [LPA_L3].[CustomerType]  AS [F1_2],
               [LPA_L3].[ModifiedDate]  AS [F1_3],
               [LPA_L3].[rowguid]        AS [F1_4],
               [LPA_L3].[TerrID]          AS [F1_5],
               [LPA_L4].[CustomerID]      AS [F3_6],
               [LPA_L4].[ContactID]    AS [F3_7],
               [LPA_L4].[Demographics]  AS [F3_8],
               [LPA_L4].[ModifiedDate]  AS [F3_9],
               [LPA_L5].[CustomerID]      AS [F7_6],
               [LPA_L5].[Demographics]  AS [F7_7],
               [LPA_L5].[ModifiedDate]  AS [F7_8],
               [LPA_L5].[Name]          AS [F7_9],
               [LPA_L5].[rowguid]        AS [F7_10],
               [LPA_L5].[SalesPersonID]   AS [F7_11],
               1 /* @p2 */                      AS [LPFA_7],
               [LPA_L6].[AccountNumber],
               [LPA_L6].[BillToAddressID] AS [BillToAddressId],
               [LPA_L6].[Comment],
               [LPA_L6].[ContactID]    AS [ContactId],
               [LPA_L6].[CreditCardApprovalCode],
               [LPA_L6].[CreditCardID]  AS [CreditCardId],
               [LPA_L6].[CurrencyRateID]  AS [CurrencyRateId],
               [LPA_L6].[CustomerID]      AS [CustomerId],
               [LPA_L6].[DueDate],
               [LPA_L6].[Freight],
               [LPA_L6].[ModifiedDate],
               [LPA_L6].[OnlineOrderFlag],
               [LPA_L6].[OrderDate],
               [LPA_L6].[PurchaseOrderNumber],
               [LPA_L6].[RevisionNumber],
               [LPA_L6].[rowguid]        AS [Rowguid],
               [LPA_L6].[SalesOrderID]  AS [SalesOrderId],
               [LPA_L6].[SalesOrderNumber],
               [LPA_L6].[SalesPersonID]   AS [SalesPersonId],
               [LPA_L6].[ShipDate],
               [LPA_L6].[ShipMethodID]  AS [ShipMethodId],
               [LPA_L6].[ShipToAddressID] AS [ShipToAddressId],
               [LPA_L6].[Status],
               [LPA_L6].[SubTotal],
               [LPA_L6].[TaxAmt],
               [LPA_L6].[TerritoryID]    AS [TerritoryId],
               [LPA_L6].[TotalDue],
               1 /* @p4 */                      AS [LPFA_8]
        FROM   ((([AdventureWorksUnitTests].[Sales].[Customer] [LPA_L3]
                  INNER JOIN [AdventureWorksUnitTests].[Sales].[SalesOrderHeader] [LPA_L6]
                      ON [LPA_L3].[CustomerID] = [LPA_L6].[CustomerID])
                 LEFT JOIN [AdventureWorksUnitTests].[Sales].[Individual] [LPA_L4]
                     ON [LPA_L3].[CustomerID] = [LPA_L4].[CustomerID])
                LEFT JOIN [AdventureWorksUnitTests].[Sales].[Store] [LPA_L5]
                    ON [LPA_L3].[CustomerID] = [LPA_L5].[CustomerID])
        WHERE  ((([LPA_L3].[CustomerID] = 676 /* @p5 */)))) [LPA_L1]

Don't know if it's related but noticed there is no AS on one of the fields like you would expect - to recap:

var current1 = from risk_RiskReview in RiskReview
     join sponsoringManager in this.StaffMember on risk_RiskReview.SponsoringManagerID equals sponsoringManager.StaffMemberID into joinresult_2
      from sponsoringManager in joinresult_2.DefaultIfEmpty()
    select new {risk_RiskReview, sponsoringManager};
current1.Select(v => new 
{ 
  DepartmentID = v.risk_RiskReview.DepartmentID, 
  WRSortKey = v.risk_RiskReview.WRSortKey
}).Dump();

generates this SQL

SELECT [LPA_L1].[DepartmentID],
       [LPA_L1].[F12_26]
FROM   (SELECT [LPA_L3].[Actual_End_Date]            AS [F12_0],

shouldn't there be [LPA_L1].[F12_26] as WRSortKey ?

No, as the value is already an alias and in an inheritance hierarchy so the field is mangled.

Back to my example of GetTransactionHistoryDtoTypedList above with the fix the SQL is now

SELECT [LPA_L1].[F4_7],
       [LPA_L1].[F4_1] AS [ModifiedDateTh],
       [LPA_L1].[ModifiedDate]
FROM   (SELECT [LPA_L4].[Class],
               [LPA_L4].[Color],
               [LPA_L4].[DaysToManufacture],
               [LPA_L4].[DiscontinuedDate],
               [LPA_L4].[FinishedGoodsFlag],
               [LPA_L4].[ListPrice],
               [LPA_L4].[MakeFlag],
               [LPA_L4].[ModifiedDate],
               [LPA_L4].[Name],
               [LPA_L4].[ProductID],
               [LPA_L4].[ProductLine],
               [LPA_L4].[ProductModelID],
               [LPA_L4].[ProductNumber],
               [LPA_L4].[ProductSubcategoryID],
               [LPA_L4].[ReorderPoint],
               [LPA_L4].[rowguid]             AS [Rowguid],
               [LPA_L4].[SafetyStockLevel],
               [LPA_L4].[SellEndDate],
               [LPA_L4].[SellStartDate],
               [LPA_L4].[Size],
               [LPA_L4].[SizeUnitMeasureCode],
               [LPA_L4].[StandardCost],
               [LPA_L4].[Style],
               [LPA_L4].[Weight],
               [LPA_L4].[WeightUnitMeasureCode],
               1 /* @p2 */                           AS [LPFA_6],
               [LPA_L3].[ActualCost]           AS [F4_0],
               [LPA_L3].[ModifiedDate]       AS [F4_1],
               [LPA_L3].[ProductID]         AS [F4_2],
               [LPA_L3].[Quantity]           AS [F4_3],
               [LPA_L3].[ReferenceOrderID]   AS [F4_4],
               [LPA_L3].[ReferenceOrderLineID] AS [F4_5],
               [LPA_L3].[TransactionDate]     AS [F4_6],
               [LPA_L3].[TransactionID]     AS [F4_7],
               [LPA_L3].[TransactionType]     AS [F4_8],
               1 /* @p4 */                           AS [LPFA_7]
        FROM   ([AdventureWorks].[Production].[TransactionHistory] [LPA_L3]
                LEFT JOIN [AdventureWorks].[Production].[Product] [LPA_L4]
                    ON [LPA_L3].[ProductID] = [LPA_L4].[ProductID])) [LPA_L1]

[LPA_L1].[F4_1] does have an alias of AS [ModifiedDateTh] but no other field of LPA_L3 gets an second alias (when I try), why is that?

If I'd have to guess it's because one is a real field object from an entity (and gets an alias) and another is a field object that's created on the fly and doesn't belong to an entity (but refers to another field in a derived table, so an expression field). the latter doesn't really have a name only an alias, and if that's set already it won't get another alias (as it just has 1 alias).

Not following you at all, this

 var current1 =from production_TransactionHistory in TransactionHistory
join production_Product in this.Product on production_TransactionHistory.ProductID equals production_Product.ProductID into joinresult_1
from production_Product in joinresult_1.DefaultIfEmpty()
select new { production_Product, production_TransactionHistory };
current1.Select(v => 
new  { TID = v.production_TransactionHistory.TransactionID, ModifiedDateth = v.production_TransactionHistory.ModifiedDate }).Dump();

gives

SELECT [LPA_L1].[F4_7],
       [LPA_L1].[F4_1] AS [ModifiedDateth]
FROM   (SELECT [LPA_L4].[Class],
               [LPA_L4].[Color],
               [LPA_L4].[DaysToManufacture],
               [LPA_L4].[DiscontinuedDate],
               [LPA_L4].[FinishedGoodsFlag],
               [LPA_L4].[ListPrice],
               [LPA_L4].[MakeFlag],
               [LPA_L4].[ModifiedDate],
               [LPA_L4].[Name],
               [LPA_L4].[ProductID],
               [LPA_L4].[ProductLine],
               [LPA_L4].[ProductModelID],
               [LPA_L4].[ProductNumber],
               [LPA_L4].[ProductSubcategoryID],
               [LPA_L4].[ReorderPoint],
               [LPA_L4].[rowguid]             AS [Rowguid],
               [LPA_L4].[SafetyStockLevel],
               [LPA_L4].[SellEndDate],
               [LPA_L4].[SellStartDate],
               [LPA_L4].[Size],
               [LPA_L4].[SizeUnitMeasureCode],
               [LPA_L4].[StandardCost],
               [LPA_L4].[Style],
               [LPA_L4].[Weight],
               [LPA_L4].[WeightUnitMeasureCode],
               1 /* @p2 */                           AS [LPFA_6],
               [LPA_L3].[ActualCost]           AS [F4_0],
               [LPA_L3].[ModifiedDate]       AS [F4_1],
               [LPA_L3].[ProductID]         AS [F4_2],
               [LPA_L3].[Quantity]           AS [F4_3],
               [LPA_L3].[ReferenceOrderID]   AS [F4_4],
               [LPA_L3].[ReferenceOrderLineID] AS [F4_5],
               [LPA_L3].[TransactionDate]     AS [F4_6],
               [LPA_L3].[TransactionID]     AS [F4_7],
               [LPA_L3].[TransactionType]     AS [F4_8],
               1 /* @p4 */                           AS [LPFA_7]
        FROM   ([AdventureWorks].[Production].[TransactionHistory] [LPA_L3]
                LEFT JOIN [AdventureWorks].[Production].[Product] [LPA_L4]
                    ON [LPA_L3].[ProductID] = [LPA_L4].[ProductID])) [LPA_L1]

where as if I takeaway production_Product

 var current1 =from production_TransactionHistory in TransactionHistory
join production_Product in this.Product on production_TransactionHistory.ProductID equals production_Product.ProductID into joinresult_1
from production_Product in joinresult_1.DefaultIfEmpty()
select new { production_TransactionHistory };
current1.Select(v => 
new  { TID = v.production_TransactionHistory.TransactionID, ModifiedDateth = v.production_TransactionHistory.ModifiedDate }).Dump();

and the alias is gone

SELECT [LPA_L1].[F4_7],
       [LPA_L1].[F4_1]
FROM   (SELECT [LPA_L3].[ActualCost]           AS [F4_0],
               [LPA_L3].[ModifiedDate]       AS [F4_1],
               [LPA_L3].[ProductID]         AS [F4_2],
               [LPA_L3].[Quantity]           AS [F4_3],
               [LPA_L3].[ReferenceOrderID]   AS [F4_4],
               [LPA_L3].[ReferenceOrderLineID] AS [F4_5],
               [LPA_L3].[TransactionDate]     AS [F4_6],
               [LPA_L3].[TransactionID]     AS [F4_7],
               [LPA_L3].[TransactionType]     AS [F4_8],
               1 /* @p2 */                           AS [LPFA_5]
        FROM   ([AdventureWorks].[Production].[TransactionHistory] [LPA_L3]
                LEFT JOIN [AdventureWorks].[Production].[Product] [LPA_L4]
                    ON [LPA_L3].[ProductID] = [LPA_L4].[ProductID])) [LPA_L1]

Ok, but is that a problem, as in: it crashes at runtime? The linq provider -> query engine pipeline is very long and complicated so digging into that why an alias is or isn't given while that's not a problem at runtime is a lot of work for no gain.

I was trying to get to the bottom of what was happening in my prod code but happily I've managed to repro by changing the TransactionHistory to its subclass WorkOrderHistory

 var current1 =from production_TransactionHistory in WorkOrderHistory
join production_Product in this.Product on production_TransactionHistory.ProductID equals production_Product.ProductID into joinresult_1
from production_Product in joinresult_1.DefaultIfEmpty()
select new { production_Product, production_TransactionHistory };
current1.Select(v => 
new  { TID = v.production_TransactionHistory.TransactionID, ModifiedDateth = v.production_TransactionHistory.ModifiedDate }).Dump();

gives

SELECT [LPA_L1].[F4_7],
       [LPA_L1].[ModifiedDate] AS [ModifiedDateth]
FROM   (SELECT [LPA_L4].[Class],
               [LPA_L4].[Color],
               [LPA_L4].[DaysToManufacture],
               [LPA_L4].[DiscontinuedDate],
               [LPA_L4].[FinishedGoodsFlag],
               [LPA_L4].[ListPrice],
               [LPA_L4].[MakeFlag],
               [LPA_L4].[ModifiedDate],
               [LPA_L4].[Name],
               [LPA_L4].[ProductID],
               [LPA_L4].[ProductLine],
               [LPA_L4].[ProductModelID],
               [LPA_L4].[ProductNumber],
               [LPA_L4].[ProductSubcategoryID],
               [LPA_L4].[ReorderPoint],
               [LPA_L4].[rowguid]             AS [Rowguid],
               [LPA_L4].[SafetyStockLevel],
               [LPA_L4].[SellEndDate],
               [LPA_L4].[SellStartDate],
               [LPA_L4].[Size],
               [LPA_L4].[SizeUnitMeasureCode],
               [LPA_L4].[StandardCost],
               [LPA_L4].[Style],
               [LPA_L4].[Weight],
               [LPA_L4].[WeightUnitMeasureCode],
               1 /* @p2 */                           AS [LPFA_6],
               [LPA_L3].[ActualCost]           AS [F4_0],
               [LPA_L3].[ModifiedDate]       AS [F4_1],
               [LPA_L3].[ProductID]         AS [F4_2],
               [LPA_L3].[Quantity]           AS [F4_3],
               [LPA_L3].[ReferenceOrderID]   AS [F4_4],
               [LPA_L3].[ReferenceOrderLineID] AS [F4_5],
               [LPA_L3].[TransactionDate]     AS [F4_6],
               [LPA_L3].[TransactionID]     AS [F4_7],
               [LPA_L3].[TransactionType]     AS [F4_8],
               1 /* @p4 */                           AS [LPFA_7]
        FROM   ([AdventureWorks].[Production].[TransactionHistory] [LPA_L3]
                LEFT JOIN [AdventureWorks].[Production].[Product] [LPA_L4]
                    ON [LPA_L3].[ProductID] = [LPA_L4].[ProductID])
        WHERE  ((([LPA_L3].[TransactionType] = 'W' /* @p5 */
               OR [LPA_L3].[TransactionType] IS NULL)))) [LPA_L1]
WHERE  ((([LPA_L1].[F4_8] = 'W' /* @p6 */
       OR [LPA_L1].[F4_8] IS NULL)))

Notice [LPA_L1].[F4_1] AS [ModifiedDateth] has become [LPA_L1].[ModifiedDate] AS [ModifiedDateth] which is the original problem.

Ok, but I have to be clear here: I have a clue what's wrong or what's crashing or what should be fixed, this is simply too vague, sorry. (I don't know the inheritance hierarchy you're using for instance)

Please provide a turn-key repro case which we can use and clear descriptions about what's going wrong so we can look into what is actually wrong, at least the inheritance hierarchy you're using (preferably the llblgenproj file) so we know for sure we're using the same exact situation as you to repro it and don't waste more time on this with a wrong repro case like earlier in this thread I can't repro it if I don't have the exact situation you have.

(btw, there's also a [LPA_L4].[ModifiedDate] in the query, which is the field projected in the outer projection)

TIA

Otis wrote:

I have a clue what's wrong or what's crashing or what should be fixed,

(btw, there's also a [LPA_L4].[ModifiedDate] in the query, which is the field projected in the outer projection)

That's kinda my point, in the message dated '05-Dec-2017 11:12:06' I pointed out that ModifiedDateTh = v.production_TransactionHistory.ModifiedDate ends up coming fromv.production_Product.ModifiedDate which you subsequently fixed, but in the situation where a subclass in the Hierarchy is used it isn't fixed.

Otis wrote:

wrong repro case like earlier in this thread

What wrong repro case?

Nevermind I've repro'ed using your EntityLeftJoinedWithTPEWrappedQueryTest adjusted for LINQPad and replacing Customer with Store

var current1 = from c in Store
               join soh in SalesOrderHeader on c.CustomerID equals soh.CustomerID
               where c.CustomerID == 676
               select new { c, soh };
var q = current1.Select(x => new { x.c.Rowguid, SohRG = x.soh.Rowguid, CID = x.c.CustomerID });
var results = q.ToList();
results[0].CID.Dump();
results[0].Rowguid.Dump();
results[0].SohRG.Dump();

Result

676
79b65321-39ca-4115-9cba-8fe0903e12e6
79b65321-39ca-4115-9cba-8fe0903e12e6

when the first guid should be 2aa331a0-6448-4ea9-9a92-0ebf254bdcd3 as per your test

TomDog wrote:

Otis wrote:

I have a clue what's wrong or what's crashing or what should be fixed,

(btw, there's also a [LPA_L4].[ModifiedDate] in the query, which is the field projected in the outer projection)

That's kinda my point, in the message dated '05-Dec-2017 11:12:06' I pointed out that ModifiedDateTh = v.production_TransactionHistory.ModifiedDate ends up coming fromv.production_Product.ModifiedDate which you subsequently fixed, but in the situation where a subclass in the Hierarchy is used it isn't fixed.

OK, but what hierarchy is still a blur to me. I need exact information, Jeremy, I can't just rebuild a reprocase based on a hunch or assumptions. That will take a lot of time and potentially results in either testing the wrong issue or a situation where we can't reproduce it.

Otis wrote:

wrong repro case like earlier in this thread

What wrong repro case?

The one where I tried to reproduce it but failed.

Nevermind I've repro'ed using your EntityLeftJoinedWithTPEWrappedQueryTest adjusted for LINQPad and replacing Customer with Store

var current1 = from c in Store
               join soh in SalesOrderHeader on c.CustomerID equals soh.CustomerID
               where c.CustomerID == 676
               select new { c, soh };
var q = current1.Select(x => new { x.c.Rowguid, SohRG = x.soh.Rowguid, CID = x.c.CustomerID });
var results = q.ToList();
results[0].CID.Dump();
results[0].Rowguid.Dump();
results[0].SohRG.Dump();

Result

676
79b65321-39ca-4115-9cba-8fe0903e12e6
79b65321-39ca-4115-9cba-8fe0903e12e6

when the first guid should be 2aa331a0-6448-4ea9-9a92-0ebf254bdcd3 as per your test

Ok, where Store is a subtype of customer, I presume? Please mention that kind of info, I otherwise have to guess, and these kind of issues are already annoying as hell to fix (not because of you reporting them, but of the nature of how Linq works... ) as they're very hard to debug, and if I then have to guess what details are in play I have to spend potentially many more hours on this than necessary.

I'll assume Store is a subtype (TPE hierarchy, created by the designer over the 1:1 pk-pk relationship) and start there.