Hi,
I'm using the LLBLGen Runtime Version 5.9.2 using the adapter template.
I have noticed a very troubling bug in the Linq-To-LLBLGenPro functionality. I'm using VB.NET to formulate a Linq query, and depending on the alias chosen for the tables it generates a different query, one of them incorrect.
Here is query variant 1. This one works fine:
Dim e = (From TLD In dbLinq.TLD
Join TLA In dbLinq.TLA
On TLA.TLA_Cod Equals TLD.TLA_Cod
Where TLD.TArb_Cod = elem.TArb_Cod _
AndAlso TLD.TLK_Peri = elem.TLK_Peri _
AndAlso TLD.TLK_Typ = elem.TLK_Typ _
AndAlso TLD.TLK_Nr = elem.TLK_Nr _
AndAlso {"A", "B", "C", "D", "E"}.Contains(TLA.TTypLA_Cod) _
AndAlso TLA.TLA_CalcYN
Select TLD.TLD_Ges).Sum
Generated query:
SELECT TOP(@p2) SUM([LPA_L1].[TLD_Ges]) AS [LPAV_]
FROM (SELECT [LPA_L2].[TLD_Ges]
FROM ([mydb].[dbo].[TLD] [LPA_L2]
INNER JOIN [mydb].[dbo].[TLA] [LPA_L3] ON [LPA_L2].[TLA_Cod] = [LPA_L3].[TLA_Cod])
WHERE ( ( ( ( ( ( ( ( [LPA_L2].[TArb_Cod] = @p3)
AND ( [LPA_L2].[TLK_Peri] = @p4))
AND ( [LPA_L2].[TLK_Typ] = @p5))
AND ( [LPA_L2].[TLK_Nr] = @p6))
AND ( [LPA_L3].[TTypLA_Cod] IN (@p7, @p8, @p9, @p10, @p11)))
AND ( [LPA_L3].[TLA_CalcYN] = @p12))))
) [LPA_L1]
Here is query variant 2. The only change is the lowercase letter for the table name alias in the linq query. This one fails:
Dim o = (From tLD In dbLinq.TLD
Join tLA In dbLinq.TLA
On tLA.TLA_Cod Equals tLD.TLA_Cod
Where tLD.TArb_Cod = elem.TArb_Cod _
AndAlso tLD.TLK_Peri = elem.TLK_Peri _
AndAlso tLD.TLK_Typ = elem.TLK_Typ _
AndAlso tLD.TLK_Nr = elem.TLK_Nr _
AndAlso {"A", "B", "C", "D", "E"}.Contains(tLA.TTypLA_Cod) _
AndAlso tLA.TLA_CalcYN
Select tLD.TLD_Ges).Sum
Generated query:
SELECT TOP(@p2) SUM([LPA_L1].[TLD_Ges]) AS [LPAV_]
FROM (SELECT [LPLA_3].[TLD_Ges]
FROM ([mydb].[dbo].[TLD] [LPA_L2]
INNER JOIN [mydb].[dbo].[TLA] [LPA_L3] ON [LPA_L2].[TLA_Cod] = [LPA_L3].[TLA_Cod])
WHERE ( ( ( ( ( ( ( ( [LPLA_3].[TArb_Cod] = @p3)
AND ( [LPLA_3].[TLK_Peri] = @p4))
AND ( [LPLA_3].[TLK_Typ] = @p5))
AND ( [LPLA_3].[TLK_Nr] = @p6))
AND ( [LPLA_3].[TTypLA_Cod] IN (@p7, @p8, @p9, @p10, @p11)))
AND ( [LPLA_3].[TLA_CalcYN] = @p12))))
) [LPA_L1]
You can see that the alias for TLD is still LPA_L2 as before, but the SELECT and the WHERE clauses all reference an alias LPLA_3 that does not exist.
This throws a SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException with the following text:
An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_3.TArb_Cod" could not be bound.
The multi-part identifier "LPLA_3.TLK_Peri" could not be bound.
The multi-part identifier "LPLA_3.TLK_Typ" could not be bound.
The multi-part identifier "LPLA_3.TLK_Nr" could not be bound.
The multi-part identifier "LPLA_3.TTypLA_Cod" could not be bound.
The multi-part identifier "LPLA_3.TTypLA_Cod" could not be bound.
The multi-part identifier "LPLA_3.TTypLA_Cod" could not be bound.
The multi-part identifier "LPLA_3.TTypLA_Cod" could not be bound.
The multi-part identifier "LPLA_3.TTypLA_Cod" could not be bound.
The multi-part identifier "LPLA_3.TLA_CalcYN" could not be bound.
The multi-part identifier "LPLA_3.TLD_Ges" could not be bound..
What's troubling is that the only difference is one lowercase letter in the alias.
For good measure, here another query variant that works fine:
Dim y = (From det In dbLinq.TLD
Join la In dbLinq.TLA
On la.TLA_Cod Equals det.TLA_Cod
Where det.TArb_Cod = elem.TArb_Cod _
AndAlso det.TLK_Peri = elem.TLK_Peri _
AndAlso det.TLK_Typ = elem.TLK_Typ _
AndAlso det.TLK_Nr = elem.TLK_Nr _
AndAlso {"A", "B", "C", "D", "E"}.Contains(la.TTypLA_Cod) _
AndAlso la.TLA_CalcYN
Select det.TLD_Ges).Sum
Could you please look into that.
Thanks,
andreas
Attachments
| Filename |
File size |
Added on |
Approval |
|
Queries.zip
|
3,745 |
23-Nov-2022 17:15.37 |
Approved |
