Any LLBLGen user worth their salt has seen the above error 100s of times. When I reported these many years ago, it was always a complex query - usually with nesting. From what little I understand from why this is 'unfixable' is in the linq specifications the 'alias' gets lost, etc.

Well, I want to bring this up again. This time, with a really simple query I am thinking should work and would not have nesting and no aliases should be lost (in theory)...

                    var answer = (from someTable in metadata.AnyTable
                                  where someTable.PkeyId == 12
                                  select new
                                  {
                                      FieldA = Convert.ToDouble(someTable.Quantity),
                                      FieldB = Convert.ToDouble(someTable.PkeyId),
                                  }).Sum(x => x.FieldA * x.FieldB);

This gives: The multi-part identifier "LPLA_4.FieldB" could not be bound. The multi-part identifier "LPLA_4.FieldA" could not be bound.

I simplified it even further with:

var answer = (from someTable in metadata.AnyTable
                                  where someTable.PkeyId == 12
                                  select new
                                  {
                                      FieldA = Convert.ToDouble(someTable.Quantity),
                                      FieldB = Convert.ToDouble(someTable.PkeyId),
                                  }).Sum(x => x.FieldA * 10);

This gives: The multi-part identifier "LPLA_1.PkeyId" could not be bound. Invalid column name 'FieldA'

It is interesting to note that if a calcuation is not done in the sum, it works. So this will work:

var answer = (from someTable in metadata.AnyTable
                                  where someTable.PkeyId == 12
                                  select new
                                  {
                                      FieldA = Convert.ToDouble(someTable.Quantity),
                                      FieldB = Convert.ToDouble(someTable.PkeyId),
                                  }).Sum(x => x.FieldA);

Is this a bug? Should be easy enough to repo on your side.

Self servicing, 4.2.14.811, Sql Server I realize I am not on the latest release. If the above unit tests on your side work with the latest I will upgrade to the latest of course.

Thanks for looking!

WayneBrantley wrote:

Any LLBLGen user worth their salt has seen the above error 100s of times.

Is this kind of text really necessary? Like our linq provider is a big mess.

When I reported these many years ago, it was always a complex query - usually with nesting. From what little I understand from why this is 'unfixable' is in the linq specifications the 'alias' gets lost, etc.

Well, I want to bring this up again. This time, with a really simple query I am thinking should work and would not have nesting and no aliases should be lost (in theory)...

                    var answer = (from someTable in metadata.AnyTable
                                  where someTable.PkeyId == 12
                                  select new
                                  {
                                      FieldA = Convert.ToDouble(someTable.Quantity),
                                      FieldB = Convert.ToDouble(someTable.PkeyId),
                                  }).Sum(x => x.FieldA * x.FieldB);

This gives: The multi-part identifier "LPLA_4.FieldB" could not be bound. The multi-part identifier "LPLA_4.FieldA" could not be bound.

I simplified it even further with:

var answer = (from someTable in metadata.AnyTable
                                  where someTable.PkeyId == 12
                                  select new
                                  {
                                      FieldA = Convert.ToDouble(someTable.Quantity),
                                      FieldB = Convert.ToDouble(someTable.PkeyId),
                                  }).Sum(x => x.FieldA * 10);

This gives: The multi-part identifier "LPLA_1.PkeyId" could not be bound. Invalid column name 'FieldA'

It is interesting to note that if a calcuation is not done in the sum, it works. So this will work:

var answer = (from someTable in metadata.AnyTable
                                  where someTable.PkeyId == 12
                                  select new
                                  {
                                      FieldA = Convert.ToDouble(someTable.Quantity),
                                      FieldB = Convert.ToDouble(someTable.PkeyId),
                                  }).Sum(x => x.FieldA);

Is this a bug? Should be easy enough to repo on your side.

Self servicing, 4.2.14.811, Sql Server I realize I am not on the latest release. If the above unit tests on your side work with the latest I will upgrade to the latest of course.

Thanks for looking!

I can reproduce it.

It appears to be related to the rewriting of the query. The problem is:

// linq
using(var adapter = new DataAccessAdapter())
{
    var metaData = new LinqMetaData(adapter);
    var s = (from o in metaData.Order
            where o.OrderId == 10254
            select new
            {
                FieldA = o.EmployeeId.Value,
                FieldB = o.OrderId
            }).Sum(x => x.FieldA * 10);
    Assert.AreEqual(30, s);
}

-- SQL
SELECT TOP 1 SUM([LPA_L1].[LPAV_]) AS [LPAV_]
FROM   (SELECT ([LPLA_2].[FieldA] * 10 /* @p2 */) AS [LPAV_]
        FROM   [Northwind].[dbo].[Orders] [LPLA_2]
        WHERE  ((([LPLA_1].[OrderID] = 10254 /* @p3 */)))) [LPA_L1]

SUM causes the query to be rewritten (transformed) as the target it works on can't be an argument directly, it first needs to be projected, hence the rewriting. However it seems that the rewriting is rewriting scopes but misses an alias. (the 'from o in metaData.Order' is not really there, only the projection of the two fields)

Looking into it.

(edit)

It is interesting to note that if a calcuation is not done in the sum, it works. So this will work:

This is indeed interesting and shows what's likely the problem. Query that works:

SELECT TOP 1 SUM([LPA_L1].[FieldA]) AS [LPAV_]
FROM   (SELECT [LPLA_1].[EmployeeID] AS [FieldA],
               [LPLA_1].[OrderID]   AS [FieldB]
        FROM   [Northwind].[dbo].[Orders] [LPLA_1]
        WHERE  ((([LPLA_1].[OrderID] = @p1)))) [LPA_L1] 

Our system doesn't use FROM clauses, it determines that from the projection as it's redundant info. The thing here is that with the calculation it replaces the projection with the actual calculation (it has to, see above), and the field in the calculation refers to the query it is going to be part of, so the alias changes of the target. We've to rewrite the aliases there. This is done already in a lot of these transformation phases, weird that this pops up after all these years in this particular case!

(edit) Error is somewhere here: http://referencesource42.llblgen.com/#SD.LLBLGen.Pro.ORMSupportClasses/Linq/ExpressionHandlers/QueryExpressionBuilder.cs,842

Got it.

SELECT TOP 1 SUM([LPA_L1].[LPAV_]) AS [LPAV_]
FROM   (SELECT ([LPA_L2].[FieldA] * @p2) AS [LPAV_]
        FROM   (SELECT [LPLA_1].[EmployeeID] AS [FieldA],
                       [LPLA_1].[OrderID]   AS [FieldB]
                FROM   [Northwind].[dbo].[Orders] [LPLA_1]
                WHERE  ((([LPLA_1].[OrderID] = @p3)))) [LPA_L2]) [LPA_L1] 

The fix was quite simple in the end: instead of replacing the projection, we're wrapping the query which is already there, as that's actually the sane thing to do.

It's slightly less optimal than when you'd have the projection replaced, but that's in this case not possible, as the fields in the projection are replaced with a different field, the correlation between the two fields (which doesn't exist other than semantical, i.e. through names and lookup tables so i.o.w. not really determinable at all times) is required to decide which aliases to rewrite (i.e. the LPLA_1 to LPLA_2 alias rewrite so the WHERE clause in the original query would work.). The change we made now works in all cases, as the query scope (the query which is wrapped) is already a full query as seen in the Linq expression tree so all elements referring to it already have the proper aliases set.

I'll attach a debug build of the runtime for you test

Attached is a release build of the runtime which should fix your problems. As you're using an older version of the designer, generated code is expected to be at the latest version (though I don't recall breaking changes in templates +runtime combinations). So if things break, first test with the latest build of the templates.

Is this kind of text really necessary? Like our linq provider is a big mess.

This was NOT meant as an insult of your product or anything. I see how it looks.

What was meant by that was it is easy to create linq code in LLBLGen that hits this 'linq provider wall'. And I officially apologize for not being clear on that!

You know as many debates as we have had and as long as I have been a customer, I am one of your biggest supporters!

(Thanks for the fix. I will update templates and the runtime)

BTW, I am just now learning that you have published your dlls on nuget!! That is GREAT.

I had my own versions of your dlls published to an internal myget feed - this is so much better. Thank you! Thank you! Thank you!

Next question is - will the fix you just did appear on nuget in a few days - if so, I would prefer to just swap everything I have over to use your packages and delete mine! ?

WayneBrantley wrote:

BTW, I am just now learning that you have published your dlls on nuget!! That is GREAT.

WayneBrantley wrote:

Next question is - will the fix you just did appear on nuget in a few days - if so, I would prefer to just swap everything I have over to use your packages and delete mine! ?

They're automatically updated with a new version when a new build is published (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=23185). If you see the update history on nuget, they all were updated in the same day of the build.

They're automatically updated with a new version when a new build is published (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=23185). If you see the update history on nuget, they all were updated in the same day of the build.

But when I look the version above was created on Oct 15th...and the version on nuget is from Oct 9th. That is why I was wondering when it would be live?

Next week. We always wait a couple of days before pushing a new build as some other issues are in flight and if we have to fix them too we can ship them in one go.

Ok, thanks. I will go ahead and change to this nuget dependency and such at that time.