Hi guys,

I have a strange error when using prefetch path with this code:

            using (var adapter = ...)
            {
                var bucket = new RelationPredicateBucket(GroupFields.Deleted == 0);
                var collection = new EntityCollection<GroupEntity>();
                var path = GroupEntity.PrefetchPathMembers;
                adapter.FetchEntityCollection(collection, bucket, new PrefetchPath2(EntityType.GroupEntity) { path });

Description about the model:

I have these entities: 1. Enity Subject with these fields: ID int PK Deleted bit Name nvarchar MemberOf (M:N) with Groups

1. Enity Group(inherited from Subject FK by ID field) with these fields: ID int PK Members (M:N) with Subjects

2. Entity GroupMembership ID int PK GroupID FK to Group SubjectID FK to Subjcet

The error is:

Tests.FetchGroups : Failed SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: The multi-part identifier "net.sysSubject.Deleted" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  ----> System.Data.SqlClient.SqlException : The multi-part identifier "net.sysSubject.Deleted" could not be bound.
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs: line 136
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo) in DataAccessAdapterBase.cs: line 790
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) in DataAccessAdapterBase.cs: line 3067
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.MergeManyToMany(IPrefetchPathElement2 currentElement, IRelationPredicateBucket elementFilter, Int64 maxNumberOfItemsToReturn, IEntityCollection2 rootEntities) in DataAccessAdapterBase.cs: line 5293
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath) in DataAccessAdapterBase.cs: line 5223
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) in DataAccessAdapterBase.cs: line 2467
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, IPrefetchPath2 prefetchPath) in DataAccessAdapterBase.cs: line 2305
at Intelex.Business.Tests.PerformanceTests.FetchLocationRolesFramework() in PerformanceTests.cs: line 64
--SqlException
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs: line 112 

If there is one or two records for Groups it works fine, if there is a lot of records it tries to use a sub query to filter m:n relation and the whole query fails. I'm not completely sure what is going on and what is the problem, as for me the code looks ok. Can you please help me with sorting it out?

LLBlgen version: 3.1.11.0907

The query which fails:

exec sp_executesql N'
SELECT DISTINCT [LPA_G2].[ID] AS [Id0], [LPA_L4].[ID] AS [Id1] 
FROM ((( [net].[sysSubject] [LPA_G1]  
    INNER JOIN [net].[sysGroup] [LPA_G2]  ON  [LPA_G1].[ID]=[LPA_G2].[ID]) 
    INNER JOIN [net].[sysGroupMembership] [LPA_G3]  ON  [LPA_G2].[ID]=[LPA_G3].[GroupID]) 
    INNER JOIN [net].[sysSubject] [LPA_L4]  ON  [LPA_L4].[ID]=[LPA_G3].[SubjectID]) 
WHERE ( ( [LPA_G3].[GroupID] IN (
    SELECT [net].[sysGroup].[ID] AS [Id] 
    FROM ( [net].[sysSubject] [LPA_L4]  
        INNER JOIN [net].[sysGroup]  ON  [LPA_L4].[ID]=[net].[sysGroup].[ID]) 
    WHERE ( ( ( [net].[sysSubject].[Deleted] = @p1) AND ( [net].[sysGroup].[ID] IS NOT NULL))))))',N'@p1 bit',@p1=0
go

Thanks, Anton

It seems it happens because of the RemoveHierarchyRelations in MergeManyToMany method:

protected void MergeManyToMany(IPrefetchPathElement2 currentElement, IRelationPredicateBucket elementFilter, long maxNumberOfItemsToReturn, 
                IEntityCollection2 rootEntities)
{
.....           
   DataTable pkpkFields = new DataTable();
   if(elementFilter != null)
   {
      ((RelationCollection)elementFilter.Relations).RemoveHierarchyRelations();
   }
   FetchTypedList(manyToManyPkPkFields, pkpkFields, elementFilter, 0, false);
   ...

If I skip that method in debugger, everything seems to work correctly.

Is strange that the error says that it cannot bound "[net].[sysSubject].[Deleted] = @p1" where it looks pretty well in the executed sql. We will try to reproduce this.

Please provide a simple repro case for this.

Here is a simple project with everything in it:

I didn't put a database structure you can generate it from the model, plus you need to add some data, and either put more than 50 records(threshold for ppath to use subquery) or set a lower limit for it.

Plus, I didn't put libs, I've used the latest version and the error is still there.

Anton

Thanks Anton. If possible, please attach a script for the inserts.

Were you able to reproduce it, guys? How long would it take to fix it? We have a release coming in 2-3 days.

The script is really simple just insert 51 records(you can insert less record and just set the ParameterisedPrefetchPathThreshold) into the Subject table, and that's it, data really is not that important. If you still need a script I can send one :-).

Anton

We reproduced it. We are looking into this.

(Edit) Attached is a reproducible project which include data insert.

In the meantime, you can workaround this by filtering on the parent entity:

var path = GroupEntity.PrefetchPathMembers;
var subjectField = SubjectFields.Deleted;
var bucket = new RelationPredicateBucket(subjectField == 0);
var collection = new EntityCollection<GroupEntity>();

adapter.FetchEntityCollection(collection, bucket, new PrefetchPath2(EntityType.GroupEntity) { path });

.. we are looking into this.

I think it's a problem with auto-aliasing. Our framework doesn't force you to use aliases, but for inheritance hierarchies this is a problem as two siblings in the same hierarchy use the same parent table but with different aliases. I.o.w.: aliases are introduced automatically behind the scenes.

This all goes well, until something isn't receiving the proper auto-alias. I think this is what's happening in this particular setup. I'll see if I can find the cause of this and hopefully be able to fix it for you today.

(edit). The m:n intermediate query goes wrong, caused by an odd alias in the filter, which is the exact same filter used by the path node fetch which succeeded, as it doesn't use the alias there.

Looking into why this happens...

Pretty edge case here, but nevertheless, it's a problem.

The main issue is indeed auto-aliasing. The problem is that the subject-group hierarchy relationship is causing this, namely the one in the join in the WHERE clause. When the Subject field in the ON clause is passed to the method to obtain the real alias (of the table, as subtypes refer to multiple tables), it actually matches the table in the outer query's from clause, namely the LPA_L4 one. The find-aliases method doesn't know whether this is allowed or not, it simply finds it in a valid scope (as it is a valid scope) and returns it. This is necessary because correlated subqueries refer to outer scope's tables as well and this could be done in an ON clause in a join in the subquery, theoretically.

This is the reason the subquery's sysSubject table is aliased again with the same alias. This is not correct however, as the join is inside a new scope, and therefore should be able to use new aliases, not re-use old ones. The problem however with fixing this is the ON clause following it. The field sysSubject.ID, which isn't aliased (like the outer sysSubject isn't and the subquery's one isn't either, no aliases were specified!), which table does it refer to, the inner or the outer query's table? Unknown. So either if the subquery's join wouldn't re-use the alias of the outer query, there would still be a problem which is unsolvable without specified aliases.

This all would work though if the sysSubject.Deleted field in the where clause would use the same alias, after all it refers to the same table. But this isn't done as the field is a Group entity's field, not a Subject entity's field, at least in the filter. So it's seen as a field from a different entity as the field in the ON clause, meaning it won't re-use the alias for sysSubject.

When you change var bucket = new RelationPredicateBucket(GroupFields.Deleted == 0);

into var bucket = new RelationPredicateBucket(SubjectFields.Deleted == 0);

it works, because the field in the where clause of the subquery will be of the same entity (subject) as which is auto-aliased inside the from clause of the subquery.

This only occurs with relationships between siblings in the same hierarchy and when no aliases are specified. As in general it's possible to set aliases but not here, this is I think the only case where this occurs.

Please let me know whether this workaround works for you. As said, it's very difficult to fix this, if not impossible, because the framework has to cook up the aliases and there's not enough information to do that correctly in all cases. This is the only case where you can't specify the alias. Not having to specify an alias for all sources is nice, but has a downside, this is the one.

I totally understand that it is complicated - I've looked at the sources a lot. Plus I did know that if I replace the field to SubjectFields.Deleted, it works.

Anyway, it is not really a workaround for me, however it could be a temporary solution. The problem is that we have our framework that is built on top and handles all the stuff with Deleted field, custom fields, etc, etc, and it is really hard to identify that specific case and do something to fix it. Another thing, I can't always create SubjectFields.Deleted instead of GroupFields.Deleted, because it may break some other queries(not completely sure, but it is possible).

I've already put a temporary fix, but I still think that it would be better to provide a fix for this problem, because as for me it is a very common case, to filter entity and prefetch related, may be it is not that common to prefetch an M:N relation and have an inheritance :-) though.

Anton

We could try to specify more info to the FindRealAlias method but it's not clear whether that will not break existing code. The thing we'll try is that when the aliases are requested inside the relationcollection for building the FROM clause, the elements aren't referring to outside elements (you can't join a subquery to an outer query's element). If we tell the method only to use the top scope, i.e. the scope it's defined in, it should work.

Please be aware that it might not be fixable, as there are situations where this isn't fixable. For example fetch 2 fields from 2 siblings in a dynamic list without specifying aliases. This could go wrong in some situations.

We'll see what we can do for you.

Adding the suggested change to FindRealAlias (by adding an overload) fixed it. I can run your original query with the same result as the 'workaround' query. Also all our tests pass.

Please use the attached dll to see if it is really fixed.

Hi Otis,

Yes, it works for me too. Thanks for the quick fix for it. Are you guys going to release it? Is it safe to use attached file in prod?

Anton

kievBug wrote:

Hi Otis,

Yes, it works for me too. Thanks for the quick fix for it. Are you guys going to release it? Is it safe to use attached file in prod?

Anton

It's a release build, so it's included in the next public build. We didn't find any tests which caused a problem with this, nor do we foresee a problem with other cases we didn't have a test for (as there are unlimited queries/cases possible... ), as the fix simply requests no alias lookups beyond the current scope for aliases inside a join which are always only for the current scope. All other code will use the behavior as it was before so will work as before.