Bug: in Linq-To-LLBLGenPro fails to generate valid SQL query from LLBLGen

Posts   
 
    
acl
User
Posts: 91
Joined: 28-Mar-2012
# Posted on: 21-Nov-2022 16:04:19   

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
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Nov-2022 06:59:05   

Too strange. As this is very hard to reproduce, could you please provide a small repro solution, maybe on Northwind db?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Nov-2022 09:37:33   

This doesn't make any sense. The casing shouldn't matter, as all elements are used properly (you're not mixing casing which happens to compile on vb.net because it's case insensitive). My suspicion is that the compiler makes a mistake somewhere (usually blaming a compiler is silly, but in vb.net it's not) where it emits the wrong expression tree.

Could you check if the expression trees of both are the same? I.e. by using tostring on the IQueryable?

Frans Bouma | Lead developer LLBLGen Pro
acl
User
Posts: 91
Joined: 28-Mar-2012
# Posted on: 22-Nov-2022 10:26:08   

Indeed, it does not make sense. FYI, removing the ().Sum() at the end does not change anything.

What do you mean by ToString on the IQueryable? Simply calling .ToString() instead of .Sum() yields nothing of interest (just the type name as a string).

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Nov-2022 09:45:27   

acl wrote:

Indeed, it does not make sense. FYI, removing the ().Sum() at the end does not change anything.

What do you mean by ToString on the IQueryable? Simply calling .ToString() instead of .Sum() yields nothing of interest (just the type name as a string).

Remove the Sum() call and then in the debugger, put a break point on the statement after the Dim e=.... If you now hover over the 'e' variable it should show a popup (at least in vs) which offers you a debug string which represents the expression tree as string. Please give these for both.

Frans Bouma | Lead developer LLBLGen Pro
acl
User
Posts: 91
Joined: 28-Mar-2012
# Posted on: 23-Nov-2022 11:18:49   

Thanks. I did that and attached these expressions as text files in the main post.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Nov-2022 11:29:17   

I think it's vb.net's that's messing up. I see something's wrong here:

.Lambda #Lambda3<System.Func`3[Our.Project.Library.EntityClasses.TLDEntity,Our.Project.Library.EntityClasses.TLAEntity,VB$AnonymousType_0`2[Our.Project.Library.EntityClasses.TLDEntity,Our.Project.Library.EntityClasses.TLAEntity]]>(
    Our.Project.Library.EntityClasses.TLDEntity $tLD,
    Our.Project.Library.EntityClasses.TLAEntity $tLA) {
    .New VB$AnonymousType_0`2[Our.Project.Library.EntityClasses.TLDEntity,Our.Project.Library.EntityClasses.TLAEntity](
        $tLD,      <<<<<<<<<<<< lower case!
        $tLA)
}

.Lambda #Lambda4<System.Func`2[VB$AnonymousType_0`2[Our.Project.Library.EntityClasses.TLDEntity,Our.Project.Library.EntityClasses.TLAEntity],System.Boolean]>(VB$AnonymousType_0`2[Our.Project.Library.EntityClasses.TLDEntity,Our.Project.Library.EntityClasses.TLAEntity] $$VB$It)
{
    .Call Microsoft.VisualBasic.CompilerServices.Operators.CompareString(
        ($$VB$It.TLD).TArb_Cod,          <<<<<<<<< Upper case!
        (.Constant<Our.Project.Class+_Closure$__121-1>(Our.Project.Class+_Closure$__121-1).$VB$Local_elem).TArb_Cod,
        False) == 0 && ($$VB$It.TLD).TLK_Peri == (.Constant<Our.Project.Class+_Closure$__121-1>(Our.Project.Class+_Closure$__121-1).$VB$Local_elem).TLK_Peri
    && .Call Microsoft.VisualBasic.CompilerServices.Operators.CompareString(
        ($$VB$It.TLD).TLK_Typ,
        (.Constant<Our.Project.Class+_Closure$__121-1>(Our.Project.Class+_Closure$__121-1).$VB$Local_elem).TLK_Typ,
        False) == 0 && ($$VB$It.TLD).TLK_Nr == (.Constant<Our.Project.Class+_Closure$__121-1>(Our.Project.Class+_Closure$__121-1).$VB$Local_elem).TLK_Nr
    && .Call System.Linq.Enumerable.Contains(
        (System.Collections.Generic.IEnumerable`1[System.String]).NewArray System.String[] {
            .Call Our.Helper.Library.EnumsExtensionModule.StringValue((System.Enum).Constant<Our.Project.Library.TTypLAEnum>(ValA))
            ,
            .Call Our.Helper.Library.EnumsExtensionModule.StringValue((System.Enum).Constant<Our.Project.Library.TTypLAEnum>(ValB))
            ,
            .Call Our.Helper.Library.EnumsExtensionModule.StringValue((System.Enum).Constant<Our.Project.Library.TTypLAEnum>(ValC))
            ,
            .Call Our.Helper.Library.EnumsExtensionModule.StringValue((System.Enum).Constant<Our.Project.Library.TTypLAEnum>(ValD))
            ,
            .Call Our.Helper.Library.EnumsExtensionModule.StringValue((System.Enum).Constant<Our.Project.Library.TTypLAEnum>(ValE))
        },
        ($$VB$It.TLA).TTypLA_Cod) && ($$VB$It.TLA).TLA_CalcYN
}

.Lambda #Lambda5<System.Func`2[VB$AnonymousType_0`2[Our.Project.Library.EntityClasses.TLDEntity,Our.Project.Library.EntityClasses.TLAEntity],System.Decimal]>(VB$AnonymousType_0`2[Our.Project.Library.EntityClasses.TLDEntity,Our.Project.Library.EntityClasses.TLAEntity] $$VB$It)
{
    ($$VB$It.TLD).TLD_Ges
}

Creating an anonymous type by specifying tLD and tLA, creates 2 fields in the anonymous type with those names. However the lambda exposes it in the call with upper case characters. (see my markings). This creates a problem as they're different and therefore get a different alias assigned to them.

I have honestly no idea why the VB.NET compiler makes this mistake. Perhaps because everything is case insensitive but for handlers of the lambda expression, case sensitivity is essential.

Frans Bouma | Lead developer LLBLGen Pro
acl
User
Posts: 91
Joined: 28-Mar-2012
# Posted on: 23-Nov-2022 11:35:53   

Wow, that's interesting. What is strange is that I have lots of queries with lowercase letters aliases, but the query above is the only one that actually throws an error.

So nothing both of us can do, right?

Seems that I need to file a bug with Microsoft, not sure that's worth the effort.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Nov-2022 11:38:51   

acl wrote:

Wow, that's interesting. What is strange is that I have lots of queries with lowercase letters aliases, but the query above is the only one that actually throws an error.

So nothing both of us can do, right?

Seems that I need to file a bug with Microsoft, not sure that's worth the effort.

We can't do much about this, no. The VB team has been abandoned so I doubt there's anyone fixing stuff at this point. But, perhaps it's something else, could you test this?

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

As your 3rd example works fine, but that would create the same problem, so it might be the difference I marked above isn't the real problem. It's the one difference I could spot in the trees which are otherwise identical. Perhaps the VB.NET compiler creates an all caps reference as soon as there's 1 character in upper case.

Frans Bouma | Lead developer LLBLGen Pro
acl
User
Posts: 91
Joined: 28-Mar-2012
# Posted on: 23-Nov-2022 17:22:02   

I did what you asked. So your query variant works.

I also tried the following, which does not work (its the same as the other query that didn't work, but with uppercase and lowercase inverted):

Dim b = (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)

I believe that the problem occurs only when an alias is used, whose name is identical to the table name, but not using the same case as the table name. That's what happens in the two "bad" queries. It's not happening in the others.

I replaced the original attachment by a zip file with 4 queries and expressions. The two from my first post, the one from your post and the one from this post.

Very strange problem, we can leave it at that. simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Nov-2022 21:29:30   

Ok, so as long as the variable name is equal to the entity property name but with different casing it won't work, but if you pick another name that's not equal to the entity property name it will work.... weird! smile

If you can live with it, we can leave it with this, as I have no idea what to do about it, sorry ...

Frans Bouma | Lead developer LLBLGen Pro
acl
User
Posts: 91
Joined: 28-Mar-2012
# Posted on: 24-Nov-2022 14:49:32   

I can live with it. Thanks for taking your time to look into it.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Nov-2022 15:25:38   

acl wrote:

I can live with it. Thanks for taking your time to look into it.

I think it might even be related to compiler version or something. I use Rider 2022.1.2, which uses C:\Program Files\Microsoft Visual Studio\2022\Professional\MSBuild\Current\Bin\Roslyn\vbc.exe on .NET FX:

I tried to reproduce it, but here things are done alright, see the expression tree, where the names are correct now, including the casing.

value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[NW26.Adapter.EntityClasses.OrderEntity])
    .Join(value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[NW26.Adapter.EntityClasses.CustomerEntity]), 
        oRDER => oRDER.CustomerId, 
        cUSTOMER => cUSTOMER.CustomerId, 
        (oRDER, cUSTOMER) => new VB$AnonymousType_7`2(oRDER = oRDER, cUSTOMER = cUSTOMER))
    .Where($VB$It => ((($VB$It.oRDER.EmployeeId == ConvertChecked(2)) 
        AndAlso Convert(Convert(new [] {"USA", "Germany"}).Contains($VB$It.cUSTOMER.Country))) ?? False))
    .Select($VB$It => $VB$It.oRDER.OrderId)

original query:

<Test()>
Public Sub WeirdNameCasingIssueTest()
    Using adapter as New DataAccessAdapter()
        Dim metaData as new LinqMetaData(adapter)
        Dim q = From oRDER in metaData.Order
                Join cUSTOMER in metaData.Customer
                on cUSTOMER.CustomerId equals oRDER.CustomerId
                Where oRDER.EmployeeId = 2 _
                    AndAlso { "USA", "Germany" }.Contains(cUSTOMER.Country) _
                Select oRDER.OrderId
        Dim r = q.ToList()
    End Using
End Sub
SELECT [LPA_L1].[OrderID] AS [OrderId]
FROM   ([Northwind].[dbo].[Orders] [LPA_L1]
        INNER JOIN [Northwind].[dbo].[Customers] [LPA_L2]
            ON [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID])
WHERE  (((((([LPA_L1].[EmployeeID] = @p1)
        AND ([LPA_L2].[Country] IN (@p2, @p3))))))) 

Perhaps worth looking into, if you try to use the 2022 vb.net compiler. Also compiles and runs fine on .net core 3.1

Frans Bouma | Lead developer LLBLGen Pro