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