- Home
- LLBLGen Pro
- Bugs & Issues
Designer generated LINQ generates wrong SQL when table is joined multiple times
Joined: 25-Oct-2005
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
Joined: 17-Aug-2003
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.
Joined: 25-Oct-2005
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,
};
}
Joined: 17-Aug-2003
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
Joined: 25-Oct-2005
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};
Joined: 25-Oct-2005
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
Joined: 17-Aug-2003
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.
Joined: 17-Aug-2003
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)
Joined: 25-Oct-2005
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.
Joined: 17-Aug-2003
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 ?
Joined: 25-Oct-2005
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.
Joined: 17-Aug-2003
'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.
Joined: 25-Oct-2005
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
Joined: 17-Aug-2003
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]
Joined: 25-Oct-2005
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 ?
Joined: 25-Oct-2005
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?
Joined: 17-Aug-2003
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).
Joined: 25-Oct-2005
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]
Joined: 17-Aug-2003
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.
Joined: 25-Oct-2005
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.
Joined: 17-Aug-2003
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
Joined: 25-Oct-2005
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
Joined: 17-Aug-2003
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.