Hi There,

I'm using LLBLGen Pro Runtime Framework version 5.5.3 with MS SQL Server.

I just came across the following strange bug in Linq to LLBLGen Pro. I think it is best to illustrate it with a very easy example.

Consider the following Linq query in VB.NET syntax:

Dim test = (From TChild In linq.TChild
            Select TChild.TChild_Id,
                   TParent_Id = (From TParent In linq.TParent
                                 Where TParent.TParent_Id = TChild.TChild_Id _
                                  AndAlso TParent.TParent_State <> "O"
                                 Select TChild.TParent_Id).FirstOrDefault).ToArray

Note that the SELECT Clause of this query selects a field of the **Child **table, and not a field of the **Parent **table (as one might expect in a query FROM TParent).

This example might not make much sense, but I think it should still generate a valid query. (In my actual application, the query is way more complicated.)

I would expect something like this to be generated:

SELECT TChild_Id,
       (SELECT TOP 1 TChild.TParent_Id
        FROM TParent
        WHERE TParent.TParent_Id = TChild.TParent_Id AND TParent.TParent_State <> 'O') AS TParent_Id
FROM TChild

But actually, the following code gets generated:

SELECT [LPLA_1].[TChild_Id],
       (SELECT TOP(@p2) [LPLA_1].[TParent_Id]
        FROM [database].[dbo].[TChild] [LPLA_1]
        WHERE [LPLA_2].[TParent_Id] = [LPLA_1].[TChild_Id] AND [LPLA_2].[TParent_State] <> @p3) AS [TParent_Id]
FROM [database].[dbo].[TChild] [LPLA_1]

As you can see, the subquery references the wrong table, TChild instead of TParent.

This is not an urgent matter, I rewrote the query. Still I thought you should know.

Best,

andreas

If you want to play around with it, here are the table declarations:

CREATE TABLE TParent (
   TParent_Id bigint PRIMARY KEY IDENTITY,
   TParent_State char(1)
)

CREATE TABLE TChild (
   TChild_Id bigint PRIMARY KEY IDENTITY,
   TParent_Id bigint,
   CONSTRAINT FK_TParent_Id FOREIGN KEY (TParent_Id) REFERENCES TParent(TParent_Id)
)

Thanks, we'll look into it!

I know the sql query works (the one you were expecting), however it's still odd that a field from the outer scope (TChild) is referred to in an inner scope's projection (The `Select TChild.TParent_Id).FirstOrDefault' part) as the output. It's ok to refer to it in an expression/where clause but to place it in the projection is the problem.

Our runtime can't deal with this, as the projection dictates the initial from clause (there's no 'from' specified, you define the projection, the source follows from that internally) and here that would be 'TChild' but the where clause refers to 'TParent', as that's the source of the from of the inner query. So this results in the TParent not being there in the inner query's from clause.

There's no way for the runtime to specify this at this moment. In general this isn't a problem, as it's rare that a subquery in the projection projects a field from the outer scope's source (there's no reason to: the outer scope's projection where the subquery is part of can do that too), however in a query like yours, it runs into a problem: it doesn't know better than that the field in the projection refers to the source of the query, which is 'TChild' and happily emits that as the from clause source.

So at this moment, this particular construct is not supported. In practice you won't run into this, as the outer query's field can be projected into the outer query's projection.

Ok; thanks anyway!