Exception: Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?

Posts   
 
    
zihotki
User
Posts: 10
Joined: 18-Sep-2008
# Posted on: 06-Jul-2017 14:00:55   

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?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Jul-2017 16:01:32   

Could you cut it down to a smaller query that reproduces the issue (so the minimal query that produces the issue)? Thanks.

Frans Bouma | Lead developer LLBLGen Pro
zihotki
User
Posts: 10
Joined: 18-Sep-2008
# Posted on: 10-Jul-2017 14:52:07   

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();
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Jul-2017 17:06:50   

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.

Frans Bouma | Lead developer LLBLGen Pro
zihotki
User
Posts: 10
Joined: 18-Sep-2008
# Posted on: 10-Jul-2017 17:20:58   

Yes, indeed using

.Join(..., (x,y) => new {x, y}).Select(g => g.x)

solves the exception. Thanks for workaround

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Jul-2017 17:58:29   

zihotki wrote:

Yes, indeed using

.Join(..., (x,y) => new {x, y}).Select(g => g.x)

solves the exception. Thanks for workaround

Glad it works simple_smile Sorry for the inconvenience, it's sadly something we can't fix.

Frans Bouma | Lead developer LLBLGen Pro