When using this with a more complicated query (one that already has a join in it in this case) it fails.

The linq query looks something like:

from c in meta.ContentPage
join t in meta.ContentPageType on c.ContentPageTypeId equals t.Id
where t.Name.Contains(contentPageType)
select c;

So all this I have been writing tries to join ContextPermissions onto that. It fails, doesn't generate any sql:

Server Error in '/' Application.

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

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: SD.LLBLGen.Pro.ORMSupportClasses.ORMRelationException: Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

Source Error: 


Line 27:             }
Line 28: 
Line 29:             return base.CreateSelectDQ(fieldsToFetch, persistenceInfoObjects, filter, maxNumberOfItemsToReturn, sortClauses, relationsToWalk, allowDuplicates, groupByClause, pageNumber, pageSize);
Line 30:         }
Line 31: 

Source File: C:\Users\sam.critchley\Documents\Projects\MyMobileApplication\Main\Source\MyMobileApplication v2.0\app\MyMobileApplication.Core\Data\DataAccessAdapter.Designer.cs Line: 29 

Stack Trace: 


[ORMRelationException: Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?]
   SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PreprocessRelations() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QueryApiElements\RelationCollection.cs:878
   SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QueryApiElements\RelationCollection.cs:382
   SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QueryApiElements\RelationCollection.cs:324
   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) +1212
   SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:1082
   SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) +260
   SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, DbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:1150
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:4440
   MyMobileApplication.Data.DataAccessAdapter.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in C:\Users\sam.critchley\Documents\Projects\MyMobileApplication\Main\Source\MyMobileApplication v2.0\app\MyMobileApplication.Core\Data\DataAccessAdapter.Designer.cs:29
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:4563
   SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:2465
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProvider2.cs:140
   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:261
   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:93
   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:696
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProQuery.cs:87
   SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProQuery.cs:162
   System.Linq.Buffer`1..ctor(IEnumerable`1 source) +247
   System.Linq.Enumerable.ToArray(IEnumerable`1 source) +81
   MyMobileApplication.WebUI.Controllers.HomeController.Index() in C:\Users\sam.critchley\Documents\Projects\MyMobileApplication\Main\Source\MyMobileApplication v2.0\app\MyMobileApplication.WebUI\Controllers\HomeController.cs:23
   lambda_method(ExecutionScope , ControllerBase , Object[] ) +75
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +178
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +24
   System.Web.Mvc.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a() +52
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) +254
   System.Web.Mvc.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c() +19
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +192
   System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +314
   System.Web.Mvc.Controller.ExecuteCore() +105
   System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +39
   System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +7
   System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__4() +34
   System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +21
   System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +12
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +59
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +44
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +7
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +8682542
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

I don't understand the error message so I'm not really sure how to proceed. I've attached my dataaccess adapter extensions. Enabling tracing didn't yield any more detail on the problem.

Each relation in the relationcollection passed to the adapter has to connect to an element already in the join list, unless it's the first. E.g., you have: A-B, B-C, E-F. This will give this error on position 2 (E-F) as both elements don't connect to any element already in the joinlist.

It's likely you run into this because the relation you're adding is not having the proper Alias for either start/end entity to connect start or end entity to an entity in the relation on position 0 (the one added by the linq provider). You can check this in the debugger on line 29

Hi Frans,

thanks for weighing in. Debugging my massaged relations I can see I have 2 relations. One from the linq query (at index 0) and other is the one I have added as part of the security (index 1).

So rel - 0 has: AliasLeftOperand - LPLA_6 AliasRightOperand - LPLA_1

rel 1 has: AliasLeftOperand - ContextPermissions AliasRightOperand - LPLA_1 AliasEndEntity - LPLA_1 AliasFKSide - ContextPermissions AliasPKSide - LPLA_1 AliasStartEntity - ContextPermissions

I don't really get this stuff. Seems to me this means the left operand is the table I have joined and right operand is the table I am joining on to. Both relations join on to LPLA_1 (the alias for ContentPage)... which sounds right to me.

But it sounds like you are telling me one of rel[1] aliases should be LPLA_6

My expected output, minus the aliases, is something like:

FROM ContentPage
INNER JOIN ContentPageType ON ContentPage.ContentPageTypeId = ContentPageType.Id
INNER JOIN ContextPermissions On ContentPage.Id = ContextPermissions.EntityId AND ContextPermissions.EntityType = "ContentPage"
WHERE ContentPageType.Name LIKE '%type%'

Please debug these lines. Check that you are no messing the aliases in your custom code:

...
if (entityAliases.Count() > 0)
                foreach (var occurrenceAlias in entityAliases)
                    AddAuthorisationFilter(bucket, string.IsNullOrEmpty(occurrenceAlias.ObjectAlias) ? bucket.SelectListAlias : occurrenceAlias.ObjectAlias);
        }

...

        void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias)
        {
            var rowLevel = ContextPermissionsFields.EntityId != DBNull.Value;
            var permissionCheck = ContentPageFields.Id.SetObjectAlias(alias) == ContextPermissionsFields.EntityId;
...         

Hi daelmo,

it only sets the aliases on new relations it creates:

void AddAuthorisationFilter(IRelationPredicateBucket bucket, string alias, string entitySecuring)
        {
            string tableName = entitySecuring.Substring(0, entitySecuring.LastIndexOf("Entity"));
            var rowLevel = ContextPermissionsFields.EntityId.SetObjectAlias("ContextPermissions") != DBNull.Value;
            bucket.PredicateExpression.Add(rowLevel);

            var entityPk = EntityFieldFactory.Create(entitySecuring, "Id");
            EntityRelation relationToAdd = new EntityRelation(entityPk, ContextPermissionsFields.EntityId, RelationType.OneToOne);
            relationToAdd.CustomFilter = new PredicateExpression(ContextPermissionsFields.EntityType.SetObjectAlias("ContextPermissions") == tableName);
            relationToAdd.SetAliases("ContextPermissions", alias);
            //relationToAdd.SetAliases(alias, "ContextPermissions");
            //
            bucket.Relations.Add(relationToAdd);
        }

So this line: relationToAdd.SetAliases("ContextPermissions", alias); "alias" is always (in this case) the original alias specified from bucket.SelectListAlias. The dynamic relation created by the linq query is not modified. It never changes an already existing relations alias. I've just shown all the aliases as they are based to base CreateSelectDQ method. Can you see anything invalid?

See here:

string[] securedEntities = new string[] { "ContentPageEntity" };

        void CreateRowAuthorisationFilter(IEntityFieldCore[] fieldsToFetch, IRelationPredicateBucket bucket)
        {
            var entityAliases = (from entityField in fieldsToFetch
                                 where securedEntities.Contains(entityField.ActualContainingObjectName) && !entityField.ActAsDerivedTableField
                                 select
                                     new { entityField.ObjectAlias, entityField.ActualContainingObjectName }).Distinct();

            if (entityAliases.Count() > 0)
                foreach (var alias in entityAliases)
                    AddAuthorisationFilter(bucket, string.IsNullOrEmpty(alias.ObjectAlias) ? bucket.SelectListAlias : alias.ObjectAlias, alias.ActualContainingObjectName);
        }

So only entityAlias only ever has 1 item in it (at least in my test scenario). It doesn't touch the existing relation between ContentPage and ContentPageType. I set the alias explicitly on the relationship I create. It generates bad SQL without that.

If i dont add the other join my linq query this code works without any problems.

So rel - 0 has: AliasLeftOperand - LPLA_6 AliasRightOperand - LPLA_1

rel 1 has: AliasLeftOperand - ContextPermissions AliasRightOperand - LPLA_1 AliasEndEntity - LPLA_1 AliasFKSide - ContextPermissions AliasPKSide - LPLA_1 AliasStartEntity - ContextPermissions

It seems to me that this should work, if you switch the operands of the second relation. try to use the side having "LPLA_1" as the left operand (StartEntity).

I made that change and it didn't throw an error in the query engine. However it did generate broken SQL.

exec sp_executesql N'SELECT DISTINCT [LPLA_1].[ContentPageTypeId], [LPLA_1].[CreatedBy], [LPLA_1].[CreatedOn], [LPLA_1].[Description], [LPLA_1].[Id], [LPLA_1].[PageTitle], [LPLA_1].[UpdatedBy], [LPLA_1].[UpdatedOn], [ContextPermissions].[Permission] FROM (( (SELECT [LPA_L4].[Description], [LPA_L4].[Id], [LPA_L4].[Name] FROM ( [MyMobileApplication].[dbo].[ContentPageType] [LPA_L4]  CROSS JOIN [MyMobileApplication].[Security].[ContextPermissions] [LPA_L5] ) WHERE ( ( ( ( ( [LPA_L5].[EntityId] IS NOT NULL) AND ( [LPA_L5].[EntityType] = @p1))) AND ( ( [LPA_L4].[Id] = [LPA_L5].[EntityId]) OR ( [LPA_L5].[EntityId] = @p2))))) [LPA_L1]  INNER JOIN [MyMobileApplication].[Security].[ContextPermissions] [LPA_L2]  ON  [LPLA_1].[ContentPageTypeId] = [LPA_L1].[Id]) INNER JOIN [MyMobileApplication].[dbo].[ContentPage] [LPA_C3]  ON  [LPA_C3].[Id]=[LPA_L2].[EntityId] AND ( ( [ContextPermissions].[EntityType] = @p3))) WHERE ( ( ( ( ( ( [LPA_L1].[Name] LIKE @p4))))) AND [ContextPermissions].[EntityId] IS NOT NULL)',N'@p1 nvarchar(128),@p2 int,@p3 nvarchar(128),@p4 nvarchar(6)',@p1=N'ContentPageType',@p2=-1,@p3=N'ContentPage',@p4=N'%Type%'

The errors being:

The multi-part identifier "LPLA_1.ContentPageTypeId" could not be bound. The multi-part identifier "ContextPermissions.EntityType" could not be bound. The multi-part identifier "ContextPermissions.EntityId" could not be bound. The multi-part identifier "LPLA_1.ContentPageTypeId" could not be bound. The multi-part identifier "LPLA_1.CreatedBy" could not be bound. The multi-part identifier "LPLA_1.CreatedOn" could not be bound. The multi-part identifier "LPLA_1.Description" could not be bound. The multi-part identifier "LPLA_1.Id" could not be bound. The multi-part identifier "LPLA_1.PageTitle" could not be bound. The multi-part identifier "LPLA_1.UpdatedBy" could not be bound. The multi-part identifier "LPLA_1.UpdatedOn" could not be bound. The multi-part identifier "ContextPermissions.Permission" could not be bound.

I'd like to ask you to open a new thread and explain briefly what you're doing and what's the problem as it's currently cumbersome to track back all details that are still important for the problem at hand.

OR, if you can give a brief re-cap HERE what the problem is now so I can ignore the stuff which isn't relevant anymore (e.g. the relationship mismatch issue)

I've mailed a reproduction to support at llblgen.

And created a new thread here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=19444

Continued in other thread.