Runtime build 01052011 (latest as of this post). Selfservicing Northwind database

I just made up a query that shows the problem...

Using this WORKING linq query as a base....

var test = from employee in Employee
           join manager in Employee on employee.ReportsTo equals manager.EmployeeId into managerJoined
           from manager in managerJoined.DefaultIfEmpty()
           select new {employee.FirstName, Region=manager==null ? employee.Region : manager.Region,
               managedList = (from managed in Employee
                             where managed.ReportsTo==employee.EmployeeId
                             select new {managed.FirstName} )
           };

If you throw a new field in the subselect result from the top query, give error: The multi-part identifier "LPLA_4.Region" could not be bound..

var test = from employee in Employee
           join manager in Employee on employee.ReportsTo equals manager.EmployeeId into managerJoined
           from manager in managerJoined.DefaultIfEmpty()
           select new {employee.FirstName, Region=manager==null ? employee.Region : manager.Region,
               managedList = (from managed in Employee
                             where managed.ReportsTo==employee.EmployeeId
                             select new {managed.FirstName, manager.Region} )  //new field here...
           };

Even if you do not do that and just do a join in the subselect (same query as first working one) you get the error: The multi-part identifier "LPLA_8.Region" could not be bound..

var test = from employee in Employee
           join manager in Employee on employee.ReportsTo equals manager.EmployeeId into managerJoined
           from manager in managerJoined.DefaultIfEmpty()
           select new {employee.FirstName, Region=manager==null ? employee.Region : manager.Region,
               managedList = (from managed in Employee
                             join managedEmployees in Employee on managed.ReportsTo equals managedEmployees.EmployeeId into mmmJoined
                             from managedEmployees in mmmJoined.DefaultIfEmpty()
                             where managed.ReportsTo==employee.EmployeeId
                             select new {managed.FirstName, managedEmployees.Region} )
           };

It seems that in the subselect if you reference anything other than the main table, you get an error. I am guessing you will be able to easily reproduce the above behavior, but here is a stacktrace if that helps:

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 132
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsDataReader(ITransaction transactionToUse, IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:line 434
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:line 666
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetAsProjection(List`1 valueProjectors, IGeneralDataProjector projector, ITransaction transactionToUse, IEntityFields fields, IPredicateExpression filter, IRelationCollection relations, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\DaoBase.cs:line 629
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteHierarchicalValueListProjection(QueryExpression toExecute, IPredicate additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProvider.cs:line 204
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteHierarchicalValueListProjection(QueryExpression toExecute, IPredicate additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProvider.cs:line 248
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider.ExecuteValueListProjection(QueryExpression toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProvider.cs:line 162
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 264
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 93
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 696
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProQuery.cs:line 179  

Hi Wayne,

In your last subquery, take it as if it is independent. This sub query is executed separately. So you need to include a join or make the query a little bit clever. For instance, you are not really using the join 'manager', the subquery also can be simplified. This query is equivalent:

var test = from employee in metaData.Employee
           select new
                      {
                          Employee = employee.FirstName,
                          Region = (employee.ReportsTo == null) ? employee.Region : employee.Manager.Region,
                          ManagedList = (from managed in employee.Employees
                                         select new
                                                    {
                                                        Managed = managed.FirstName,
                                                        Region = managed.Manager.Manager.Region
                                                    })
                      };

Daelmo, The query is contrived to produce the error, this is not my real query as I don't use northwind.

Anyway, suffice to say the join is necessary in my real query as the relationship can be blank, so what I have for the linq query is all needed. The query I have shown should work....(I can make up a scenario if you have to see it, but I really did not want to go into the query and what it is doing, etc...since I think it should produce a valid query..)

Otis?

The first query:

If you throw a new field in the subselect result from the top query, give error: The multi-part identifier "LPLA_4.Region" could not be bound.. Code: var test = from employee in Employee join manager in Employee on employee.ReportsTo equals manager.EmployeeId into managerJoined from manager in managerJoined.DefaultIfEmpty() select new {employee.FirstName, Region=manager==null ? employee.Region : manager.Region, managedList = (from managed in Employee where managed.ReportsTo==employee.EmployeeId select new {managed.FirstName, manager.Region} ) //new field here... };

IMHO, the error is expected as the manager is not known in the managedList subselect. Hope I'm not missing anything obvious.

This is unfortunate a known issue which is a very hard problem to solve. The cause is the Region reference in the ?: expression. It contains two 'Region' references, on two entities which are the same entity type. The linq provider therefore sees the two as the same thing. This isn't the case of course, as one is on employee and the other is on manager. So the mapper for aliases on references has to distinct instances of the same type. This isn't done in the current linq provider, as it's extremely hard to track these instances, simply because they're always different in each extension method (and wrapped, e.g. a Join() creates a new anonymous type, wrapping the input). It's more complex than I explained above, and you can be sure I die a little inside every time I see these kind of little edge case bugs popping up, it's to me however a big unsolved mystery how to solve this properly without re-writing the linq provider. To overcome this, please write the query in our native query api, how unfortunate that might be.

Daelmo - consider the final linq query which does not do the reference (plus that should be legal - look at the where clause..)

Otis - did you know I still get the error if I remove the ?: expression? The error is in the subquery reference to 'managedEmployees.Region'

var test = from employee in Employee
         join manager in Employee on employee.ReportsTo equals manager.EmployeeId into managerJoined
         from manager in managerJoined.DefaultIfEmpty()
         select new {employee.FirstName, Region=manager.Region,
             managedList = (from managed in Employee
                             join managedEmployees in Employee on managed.ReportsTo equals managedEmployees.EmployeeId into mmmJoined
                             from managedEmployees in mmmJoined.DefaultIfEmpty()
                             where managed.ReportsTo==employee.EmployeeId
                             select new {managed.FirstName, managedEmployees.Region} )
         };

It's related to the fact you have a 'manager' named element which is of type 'Employee' and another element which is also of type 'Employee'. It sees a member reference 'Region' and it checks the type (not the name of the element!) and this is 'employee'. it therefore thinks it's a member of the 'employee' named element, not of the 'manager' named element. This is the error, and as I explained, very hard to fix (as in: this is perhaps solved in a future release, but it will take a lot of work)

Otis, Thanks for the reply and I understand you completely. However, I have tried to do something similar to the above first query and it does not work either. This does not seem to be the same 'edge case' you are referring to. I am not debating with you - but this one does give a different error.

This works...note the use of 'employee.EmployeeId' in the where clause of the subselect - not an issue, works fine.

var test2 = from employee in Employee
         select new {employee.FirstName, 
             managedList = (from managed in Employee
                             where managed.ReportsTo==employee.EmployeeId
                             select new {managed.FirstName} )
         };

However, reference the same employee.EmployeeId in what you select and you get an error of: "A nested query in the projection has no correlation filter to tie its set to the containing parent row."

var test2 = from employee in Employee
         select new {employee.FirstName, 
             managedList = (from managed in Employee
                             where managed.ReportsTo==employee.EmployeeId
                             select new {managed.FirstName, employee.EmployeeId} )
         };

WayneBrantley wrote:

Otis, Thanks for the reply and I understand you completely. However, I have tried to do something similar to the above first query and it does not work either. This does not seem to be the same 'edge case' you are referring to.

The alias error is the same error. The error you describe below isn't an alias error, it's different.

I can explain why this happens too, I'll do that below your code

I am not debating with you - but this one does give a different error. This works...note the use of 'employee.EmployeeId' in the where clause of the subselect - not an issue, works fine.

var test2 = from employee in Employee
         select new {employee.FirstName, 
             managedList = (from managed in Employee
                             where managed.ReportsTo==employee.EmployeeId
                             select new {managed.FirstName} )
         };

However, reference the same employee.EmployeeId in what you select and you get an error of: "A nested query in the projection has no correlation filter to tie its set to the containing parent row."

var test2 = from employee in Employee
         select new {employee.FirstName, 
             managedList = (from managed in Employee
                             where managed.ReportsTo==employee.EmployeeId
                             select new {managed.FirstName, employee.EmployeeId} )
         };

Yes, and it looks odd at first, after all 'Employee' is in scope . But it really isn't. This can be complicated, so stick with me:

See the nested query is executed separately, like a prefetch path node. The 'where' clause you specify is a where clause which won't bring the 'employee' entity in the from clause, but will filter 'managed'. You specify 'employee' in the projection of the nested query, but ... that entity isn't available in that query: it's executed separately.

So first the outer query is executed, then the nested query. The where clause is used in the nested query to limit the rows, and is also used for merging the two sets. However in the second query, where you have the 'employee.EmployeeId' in the projection, there's a problem: the only fields available in the SQL query are the ones of 'managed'. The where clause might refer to it, but that is likely an IN query, so not a list of values.

So you can't refer to the outer entity in the nested query, as it's not available to you. 'But the pk field is available in the where' is perhaps your question, and that's correct, but the where clause is either becoming an IN clause with values or an IN clause with a subquery. To specify the PK fieldvalue from employee, specify the FK field value from managed (which is the same ).

ok, that is messy and seems like it should work. I guess no other linq drivers support this kind of a scenaro either? (Using parameters or something else crazy?)

I solved my scenario by just breaking the link up into several linq queries to populate it - that way I can reference whatever I want. (After all, the one linq statement produces multiple queries - so no big deal).

WayneBrantley wrote:

ok, that is messy and seems like it should work. I guess no other linq drivers support this kind of a scenaro either? (Using parameters or something else crazy?)

Most linq providers either don't support nested queries or execute them one at a time per parent (like EF and L2S do).