- Home
- LLBLGen Pro
- Bugs & Issues
Exception: Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?
Joined: 18-Sep-2008
We reproduced the issue
LLBLGen Pro v5.2.1, 26-Jun-2017 DQE.SqlServer - nuget v5.2.1 OrmSupportClasses - nuget v5.2.1
LLBLGen Pro v4.2, May 4th, 2016 DQE.SqlServer - nuget v4.2.20160415 OrmSupportClasses - nuget v4.2.20150210
Template Group - Adapter Target Framework - LLBLGen Pro Runtime Framework .Net 4.6.1
Exception: Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias? Stack Trace (from v4.2):
at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PreprocessRelations()
at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix)
at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText()
at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.CreateSelectDQ(QueryParameters parameters)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(QueryParameters parameters)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClassdb.<FetchEntityCollection>b__da()
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(QueryParameters parameters)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
Stack Trace (from v5.2):
at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PreprocessRelations()
at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix)
at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText()
at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IRetrievalQuery query, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int32 rowsToSkip, Int32 rowsToTake, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.CreateSelectDQ(QueryParameters parameters)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(QueryParameters parameters)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>n__9(QueryParameters parameters)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass10_0.<FetchEntityCollection>b__0()
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(QueryParameters parameters)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.PerformExecute(Expression expression, Type resultType)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
It fails to process the following expression:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Stream.CustomerDb.DataLayer.EntityClasses.SubLevelDocumentEntity])
.Where(x => ((x.Cancelled == Convert(True)) OrElse (x.RecordStatus.HasImages == False)))
.Join(
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Stream.CustomerDb.DataLayer.EntityClasses.AssetEntity])
.Where(x => (Not(x.IsDeleted) AndAlso Not(x.IsLockedDown))).Where(a => (a.Active == "Y"))
.Where(a => a.UserAircrafts.Any(p => (p.UserId == value(Stream.DataLayer.Repositories.UserPermissionsRepository+<>c__DisplayClass3_0).userId))),
r => r.AssetId,
a => a.Id,
(r, a) => r)
.Where(x => (value(Stream.DataLayer.Repositories.RecordRepository+<>c__DisplayClass26_1).CS$<>8__locals1.allowedObjectTldTypes.Contains(x.TopLevelDocument.TypeCode)
OrElse
value(Stream.DataLayer.Repositories.RecordRepository+<>c__DisplayClass26_1).allowedSldIds.Any(r => (r == x.Id))))
.Join(
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[Stream.CustomerDb.DataLayer.EntityClasses.ObjectsFeedRecordEntity])
.Where(x =>
((value(Stream.DataLayer.Repositories.RecordRepositorySldQueryExtensions+<>c__DisplayClass5_0).categoryObjectMapping.CategoryCodes.Contains(x.TldCode)
AndAlso
value(Stream.DataLayer.Repositories.RecordRepositorySldQueryExtensions+<>c__DisplayClass5_0).objectIds.Contains(x.ObjectId))
AndAlso
((x.ObjectType == Convert(value(Stream.DataLayer.Repositories.RecordRepositorySldQueryExtensions+<>c__DisplayClass5_0).documentType))
OrElse (x.ObjectType < 0)))),
record => record.Id,
obj => obj.RecordId,
(record, obj) => record)
.Take(100)
The entities envolved in the query don't have any inheritance, they are just mapped tables. The problem occures when the second join is applied.
Is there anything what can be done to fix it?
Joined: 18-Sep-2008
This is the minimal query extracted from our code. Record entity has a relation to Asset via AssetId and also it has Asset field but in order to reuse asset filtering logic (in this example and in the original message it's highly simplified) we are joining Records with filtered Assets, and then we also need to join by another source of item filters. Having the second join causes the slightly different exception - "Relation at index 1 doesn't...". MetaData is just a LinqMetaData instance.
var items = MetaData.Record.AsQueryable();
items = items.Where(x => x.Cancelled == false && x.RecordStatus.HasImages == true);
var assets = MetaData.Asset.Where(x => !x.IsDeleted);
items = items.Join(assets, x => x.AssetId, a => a.Id, (x, a) => x);
items = items.Where(x => x.TldTypeCode == "SB");
var feed = MetaData.ObjectsFeedItem.Where(x => x.ObjectType >= 0);
items = items.Join(feed, x => x.Id, f => f.RecordId, (x, f) => x);
var data = items.Take(5).ToArray();
Joined: 17-Aug-2003
When I try to reproduce it, I don't get your exception, though I do get a problem with the end query (it fails in the DB as the where clauses are moved which shouldn't be moved).
My test query:
var q1 = metaData.Order.AsQueryable();
q1 = q1.Where(o => o.EmployeeId > 0);
var q2 = metaData.Customer.Where(c => c.Country != "USA");
q1 = q1.Join(q2, a => a.CustomerId, b => b.CustomerId, (a, b) => a);
q1 = q1.Where(o => o.ShipCountry != "USA");
var q3 = metaData.OrderDetail.Where(od => od.ProductId > 0);
q1 = q1.Join(q3, a => a.OrderId, b => b.OrderId, (a, b) => a);
var results = q1.Take(5).ToList();
which I think represents what your query looks like.
I think it's a problem with our Linq provider, which is sadly very hard to fix. The main issue is the nested where clauses. A nested where clause can be moved if there's no projection on the query (as the where and the join are in the same scope). However, this is not really determinable without a Select() call: a Join() call always has a projection, and the C# compiler places a .Select(a=>a) in the projection of the Join call (the last argument).
So in the query above, the linq provider moves all Where clauses to the outer query, which fail with alias errors.
To work around this, I can do this:
var q1 = metaData.Order.AsQueryable();
q1 = q1.Where(o => o.EmployeeId > 0);
var q2 = metaData.Customer.Where(c => c.Country != "USA");
var q1r = q1.Join(q2, a => a.CustomerId, b => b.CustomerId, (a, b) => new { a, b });
q1 = q1r.Select(x => x.a);
q1 = q1.Where(o => o.ShipCountry != "USA");
var q3 = metaData.OrderDetail.Where(od => od.ProductId > 0);
q1 = q1.Join(q3, a => a.OrderId, b => b.OrderId, (a, b) => a);
var results = q1.Take(5).ToList();
Here I have a select call, as I project the join result. This clearly marks a scope barrier for the linq provider and will make it leave the where clause as-is.
I'd suggest to you to rewrite the joins to at least use the (a, b)=> new {a, b} format plus a .Select(x=>x.a) call following it. This might look redundant but it's converted to SQL anyway and it will then be easier to parse.
I think it goes wrong in your case due to the above issue, because it looks like everything is in the same scope but it fails to track back where the original elements are from (as they're lost in the Join() result) as the join sides aren't converted to derived tables (which was your intention with the Where clauses to begin with I think). IF they would have been converted to derived tables, the sources would be easy and things would be properly joined together.
Joined: 17-Aug-2003
zihotki wrote:
Yes, indeed using
.Join(..., (x,y) => new {x, y}).Select(g => g.x)
solves the exception. Thanks for workaround
Glad it works Sorry for the inconvenience, it's sadly something we can't fix.