Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> multi-part identifier could not be bound
 

Pages: 1
LLBLGen Pro Runtime Framework
multi-part identifier could not be bound
Page:1/1 

  Print all messages in this thread  
Poster Message
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 15-Oct-2015 06:37:13.  
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)...
Code:
                    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:

Code:
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:
Code:
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!

SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37561 posts
# Posted on: 15-Oct-2015 16:02:04.  
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. Dissapointed

Quote:

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)...
Code:
                    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:

Code:
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:
Code:
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:
Code:

// 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)
Quote:
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:
Code:

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


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37561 posts
# Posted on: 15-Oct-2015 17:15:33.  
Got it.
Code:

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
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37561 posts
# Posted on: 15-Oct-2015 17:18:42. Goto attachments  
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.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 15-Oct-2015 21:33:32.  
Quote:
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!

Laugh

(Thanks for the fix. I will update templates and the runtime)
SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 15-Oct-2015 21:53:29.  
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! Laugh

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! ?


SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8076 posts
# Posted on: 16-Oct-2015 07:06:19.  
WayneBrantley wrote:
BTW, I am just now learning that you have published your dlls on nuget!! That is GREAT.

Regular Smiley

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.
David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 16-Oct-2015 16:43:03.  
Quote:
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?


SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37561 posts
# Posted on: 16-Oct-2015 20:33:07.  
Next week. Regular Smiley 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.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# Posted on: 16-Oct-2015 20:42:57.  
Ok, thanks. I will go ahead and change to this nuget dependency and such at that time.

SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.