Error with fetching typed list with inheritance

Posts   
 
    
kievBug
User
Posts: 105
Joined: 09-Jan-2009
# Posted on: 14-Sep-2014 05:25:09   

Hi guys,

I can't figure out my self what the problem is with this code:


            using (var adapter = new DataAccessAdapter())
            {
                var fields = new ResultsetFields(6);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(ObjectCFields.TextC, 3, "TextC", "a0");
                fields.DefineField(ObjectBFields.TestB, 4, "TestB", "a0");
                fields.DefineField(ObjectBFields.Caption, 5, "ACaption", "a0");

                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a0", JoinHint.Left);

                var dataTable = new DataTable();
                adapter.FetchTypedList(fields, dataTable, bucket, 0, null, false);

                Console.WriteLine("Record count: " + dataTable.Rows.Count);
                Console.ReadLine();
            }

It throws this error:


SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled
  HResult=-2146232832
  Message=An exception was caught during the execution of a retrieval query: The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.ID" could not be bound.
The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.Caption" could not be bound.
The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.Deleted" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20
  RuntimeBuild=09032014
  RuntimeVersion=3.1.0.0
  QueryExecuted=
    Query: SELECT DISTINCT [llblgen_bug].[net].[cusAntoinheritance_RelatedA].[ID] AS [Id], [llblgen_bug].[net].[cusAntoinheritance_RelatedA].[Caption], [llblgen_bug].[net].[cusAntoinheritance_RelatedA].[Deleted], [LPA_a5].[TextC], [LPA_a4].[TestB], [LPA_a3].[Caption] AS [ACaption] FROM (((( [llblgen_bug].[net].[cusAntoinheritance_RelatedA] [LPA_L1]  LEFT JOIN [llblgen_bug].[net].[cusAntoinheritance_RelatedB] [LPA_L2]  ON  [LPA_L1].[ID]=[LPA_L2].[ID]) LEFT JOIN [llblgen_bug].[net].[cusAntoinheritance_ObjectB] [LPA_a4]  ON  [LPA_L2].[ID]=[LPA_a4].[RelatedBId]) LEFT JOIN [llblgen_bug].[net].[cusAntoinheritance_ObjectA] [LPA_a3]  ON  [LPA_a3].[ID]=[LPA_a4].[ID]) LEFT JOIN [llblgen_bug].[net].[cusAntoinheritance_ObjectC] [LPA_a5]  ON  [LPA_a4].[ID]=[LPA_a5].[ID])

  StackTrace:
       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 132
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\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 c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 3066
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 3008
       at Runner.Program.Main(String[] args) in e:\Crap\InheritanceLlblgen\Runner\Program.cs:line 35
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.SqlClient.SqlException
       HResult=-2146232060
       Message=The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.ID" could not be bound.
The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.Caption" could not be bound.
The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.Deleted" could not be bound.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=16
       LineNumber=1
       Number=4104
       Procedure=""
       Server=lincoln
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
            at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
            at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
            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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
            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 System.Data.Common.DbCommand.ExecuteReader(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
       InnerException: 

I use inheritance for entities: Chain 1: Related A, Related B and Related C Chain 2: Object A, Object B and Object C

Related B has a relation to Object B - One to Many.

I've attached a project and a database backup for you.

I'm using LLblgen 3.1 (3.1.14.0903), Adapter, and SQL Server.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 15-Sep-2014 07:14:02   

As you are on inheritance, you should refer to the final type you want to retrieve. Remember that all the properties and relations are inherited in the subtypes. So, if you do this little modification, it should work:

// you also could change this to RelatedCFields, if you need RelatedC objects in the result
fields.DefineField(RelatedBFields.Id, 0);
fields.DefineField(RelatedBFields.Caption, 1);
fields.DefineField(RelatedBFields.Deleted, 2);

fields.DefineField(ObjectCFields.TextC, 3, "TextC", "a0");
fields.DefineField(ObjectCFields.TestB, 4, "TestB", "a0");
fields.DefineField(ObjectCFields.Caption, 5, "ACaption", "a0");

I tested it and it works. If that is not what you need, please tells us what do you expect in the results.

(Edit) I reattached your files, as there are some unnecessary info (large backup and .dlls). We have the dlls and the DB script could be generated from the .llblgenproj file wink

Attachments
Filename File size Added on Approval
InheritanceTest.zip 76,222 15-Sep-2014 07:14.18 Approved
David Elizondo | LLBLGen Support Team
kievBug
User
Posts: 105
Joined: 09-Jan-2009
# Posted on: 15-Sep-2014 16:02:16   

Well, it seems your query will return a bit different results - it returns records of related B, while I want to get all records of related A and also of related B (left join), same thing applies to Object C.

I've checked the query and it actually has left joins for Related A and Related B, but for Object C it has a filter by entity type, so records from Object B are not included.

I've noticed that if I change the initial code to include two more fields, it works:


                var fields = new ResultsetFields(8);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(ObjectCFields.TextC, 3, "TextC", "a0");
                fields.DefineField(ObjectBFields.TestB, 4, "TestB", "a0");
                fields.DefineField(ObjectBFields.Caption, 5, "ACaption", "a0");

                fields.DefineField(RelatedBFields.TextB, 6);
                fields.DefineField(RelatedCFields.TextC, 7, "BTextC");

Also this code works just fine:


                var fields = new ResultsetFields(4);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);
                fields.DefineField(RelatedBFields.TextB, 3);

But if I just add a join with another object it stops working?! why?

I'm implementing a query engine that will generate ResultsetFields, set aliases and add relations based on the user configuration. I tried different things but can not get it working. So I'm looking for a set of rules, guidelines to correctly create ResultsetFields, relations and set object aliases when the inheritance is used.

Plus, I'm not sure what is wrong with the

Anton

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 15-Sep-2014 19:44:10   

I think part of the issue of subtype filters added was explained here: https://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=110688&ThreadID=19586

kievBug
User
Posts: 105
Joined: 09-Jan-2009
# Posted on: 15-Sep-2014 19:56:10   

Well, what I was trying to explain you is that your query is different from what I tried to build, and it doesn't solve the problem.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 15-Sep-2014 20:06:50   

Please provide a simple SQL query that describes exactly what you need to execute.

kievBug
User
Posts: 105
Joined: 09-Jan-2009
# Posted on: 15-Sep-2014 20:24:51   

Well, based on this


 var fields = new ResultsetFields(6);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(ObjectCFields.TextC, 3, "TextC", "a0");
                fields.DefineField(ObjectBFields.TestB, 4, "TestB", "a0");
                fields.DefineField(ObjectBFields.Caption, 5, "ACaption", "a0");

                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a0", JoinHint.Left);

It seems to me it should generate something like this or similar:


SELECT [RelatedA].ID, [RelatedA].Caption, [RelatedA].Deleted,
  [ObjectC].TextC, [ObjectB].TestB, [ObjectA].Caption
FROM 
  [RelatedA] LEFT JOIN [RelatedB] ON [RelatedA].ID = [RelatedB].ID
  [RelatedB] LEFT JOIN [ObjectB] ON [RelatedB].ObjectBID = [ObjectB].ID
  [ObjectB] LEFT JOIN [ObjectA] ON [ObjectB].ID = [ObjectA].ID
  [ObjectB] LEFT JOIN [ObjectC] ON [ObjectB].ID = [ObjectC].ID

The idea is that I want to select ALL records from Related A, and if there is a record in Related B join those. After that for those that has a record in Related B, I want to join (using left join) with Object B, selecting fields from the parent Object A and also if it is an instance of Object C select fields from C.

Anton

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 16-Sep-2014 08:14:02   

When you include an entity in a query that is involved in inheritance, LLBLGen will produce sql code that match that object and it's parents (which is called a type filter). But in your example you expect to retrieve all records, and if some of they are B, then fetch related objects to B, and if they are C, fetch some related objects to C. That means that you want all the possibilities.

If you want that, you should add the relations yourself in the relation collections. So those relations that are added automatically by LLBLGen will be overriden by yours. I advise to test with the more simple set of fields and then add complexity to it, and see what is the generated sql. As I said before, you can add the relations yourself, but that is not precisely an inheritance query. That is what the link posted by Walaa is about.

Be aware that not all 1:1 DB relations are indicate inheritance. So it might be that inheritance is not the best solution, as it should be natural. Please check this out.

BTW, I suspect that those are not your real entities, but just a test case that shows your situation. Isn't it?

David Elizondo | LLBLGen Support Team
kievBug
User
Posts: 105
Joined: 09-Jan-2009
# Posted on: 16-Sep-2014 15:17:29   

When I add all relations like this:


                var fields = new ResultsetFields(7);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(RelatedBFields.TextB, 3, "TextB", "a1");

                fields.DefineField(ObjectCFields.TextC, 4, "TextC", "a3");
                fields.DefineField(ObjectBFields.TestB, 5, "TestB", "a0");
                fields.DefineField(ObjectAFields.Caption, 6, "ACaption", "a2");


                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedAEntity.Relations.GetSubTypeRelation("RelatedBEntity"), "a1", JoinHint.Left);
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a1", "a0", JoinHint.Left);
                bucket.Relations.Add(ObjectBEntity.Relations.GetSuperTypeRelation(), "a0", "a2", JoinHint.Left);
                bucket.Relations.Add(ObjectBEntity.Relations.GetSubTypeRelation("ObjectCEntity"), "a0", "a3", JoinHint.Left);

Then the query starts to work as expected, but there is another problem if I go further:


var fields = new ResultsetFields(7);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(RelatedBFields.TextB, 3, "TextB", "a1");

                fields.DefineField(ObjectCFields.TextC, 4, "TextC", "a3");
                fields.DefineField(ObjectBFields.TestB, 5, "TestB", "a0");
                fields.DefineField(ObjectAFields.Caption, 6, "ACaption", "a2");


                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedAEntity.Relations.GetSubTypeRelation("RelatedBEntity"), "a1", JoinHint.Left);
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a1", "a0", JoinHint.Left);
                bucket.Relations.Add(ObjectBEntity.Relations.GetSuperTypeRelation(), "a0", "a2", JoinHint.Left);
                bucket.Relations.Add(ObjectBEntity.Relations.GetSubTypeRelation("ObjectCEntity"), "a0", "a3", JoinHint.Left);

                var derivedTable = new DerivedTableDefinition(fields, "dt", bucket.PredicateExpression, bucket.Relations, null, null, 0, false);
                var relation = new DynamicRelation(derivedTable, JoinHint.None, null, null);

                var summaryBucket = new RelationPredicateBucket();
                summaryBucket.Relations.Add(relation);

                var countField = new EntityField2("Id", "dt", typeof(int));
                countField.SetAggregateFunction(AggregateFunction.Count);
                countField.SetFieldAlias("cnt");

                var summaryFields = new ResultsetFields(1);
                summaryFields.DefineField(countField, 0);

                var dataTable = new DataTable();
                adapter.FetchTypedList(summaryFields, dataTable, summaryBucket, 0, null, false);

The query just counts number of records of the previous. Not sure why but it fails with exception:


SD.LLBLGen.Pro.ORMSupportClasses.ORMRelationException was unhandled
  HResult=-2146232832
  Message=Relation at index 6 doesn't contain an entity already added to the FROM clause. Bad alias?
  Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20
  RuntimeBuild=09032014
  RuntimeVersion=3.1.0.0
  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QueryApiElements\RelationCollection.cs:line 523
       at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QueryApiElements\RelationCollection.cs:line 324
       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) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\SqlServerDQE\DynamicQueryEngine.cs:line 610
       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) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:line 1088
       at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DbSpecificCreatorBase.cs:line 626
       at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\SqlServerDQE\SqlServerSpecificCreator.cs:line 379
       at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PrepareJoinableFragments(StringBuilder queryText, IEntityRelation relationAsEntityRelation, IDynamicRelation relationAsDynamicRelation, Boolean relationIsDynamicRelation, Boolean& isSingleOperandDynamicRelation, String& pkElement, String& fkElement, String& aliasPKSide, String& aliasFKSide, String& pkElementReference, String& fkElementReference) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QueryApiElements\RelationCollection.cs:line 1071
       at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QueryApiElements\RelationCollection.cs:line 404
       at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\QueryApiElements\RelationCollection.cs:line 324
       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) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\SqlServerDQE\DynamicQueryEngine.cs:line 610
       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) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:line 1088
       at 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) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\SqlServerDQE\DynamicQueryEngine.cs:line 679
       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, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\DynamicQueryEngineBase.cs:line 1156
       at 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.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 4445
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize, IFieldPersistenceInfo[]& persistenceInfo, IRetrievalQuery& selectQuery) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 4606
       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 c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 3062
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 3008
       at Runner.Program.Main(String[] args) in e:\Crap\InheritanceLlblgen\Runner\Program.cs:line 55
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

What is wrong now?

Anton

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 17-Sep-2014 07:31:15   

Could you please provide a simple repro solution so we can debug and track it down? Also if you can test it with the latest release of v.3.5 it would be great.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Sep-2014 09:54:42   

Don't use RelatedAEntity.Relations.GetSubTypeRelation("RelatedBEntity") kind of calls, you should never have to call GetSubTypeRelation or GetSuperTypeRelation. That's done for you, just add the relationships you want to add and avoid adding those, as these aren't to be add by you.

Frans Bouma | Lead developer LLBLGen Pro
kievBug
User
Posts: 105
Joined: 09-Jan-2009
# Posted on: 17-Sep-2014 15:23:58   

Well, I'm confused a bit - so do I need to add all relations manually or not?

I have to execute two queries:

Query 1: Get data Query 2: Get count

So I provided both of them, if I do not add relations the first one doesn't work, if I add relations manually second doesn't work. What should I do after all?

Query 1 No inheritance relations - doesn't work:


            using (var adapter = new DataAccessAdapter())
            {
                var fields = new ResultsetFields(6);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(ObjectCFields.TextC, 3, "TextC", "a0");
                fields.DefineField(ObjectBFields.TestB, 4, "TestB", "a0");
                fields.DefineField(ObjectBFields.Caption, 5, "ACaption", "a0");

                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a0", JoinHint.Left);

                var dataTable = new DataTable();
                adapter.FetchTypedList(fields, dataTable, bucket, 0, null, false);

                Console.WriteLine("Record count: " + dataTable.Rows.Count);
                Console.ReadLine();
            }

Query 1 Inheritance relations added manually - works:


                var fields = new ResultsetFields(7);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(RelatedBFields.TextB, 3, "TextB", "a1");

                fields.DefineField(ObjectCFields.TextC, 4, "TextC", "a3");
                fields.DefineField(ObjectBFields.TestB, 5, "TestB", "a0");
                fields.DefineField(ObjectAFields.Caption, 6, "ACaption", "a2");


                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedAEntity.Relations.GetSubTypeRelation("RelatedBEntity"), "a1", JoinHint.Left);
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a1", "a0", JoinHint.Left);
                bucket.Relations.Add(ObjectBEntity.Relations.GetSuperTypeRelation(), "a0", "a2", JoinHint.Left);
                bucket.Relations.Add(ObjectBEntity.Relations.GetSubTypeRelation("ObjectCEntity"), "a0", "a3", JoinHint.Left);

Query 2 No inheritance relations - works:


                var fields = new ResultsetFields(7);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(RelatedBFields.TextB, 3, "TextB");

                fields.DefineField(ObjectCFields.TextC, 4, "TextC", "a0");
                fields.DefineField(ObjectBFields.TestB, 5, "TestB", "a0");
                fields.DefineField(ObjectAFields.Caption, 6, "ACaption", "a0");


                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a0", JoinHint.Left);

                var derivedTable = new DerivedTableDefinition(fields, "dt", bucket.PredicateExpression, bucket.Relations, null,
                    null, 0, false);
                var relation = new DynamicRelation(derivedTable, JoinHint.None, null, null);

                var summaryBucket = new RelationPredicateBucket();
                summaryBucket.Relations.Add(relation);

                var countField = new EntityField2("Id", "dt", typeof (int));
                countField.SetAggregateFunction(AggregateFunction.Count);
                countField.SetFieldAlias("cnt");

                var summaryFields = new ResultsetFields(1);
                summaryFields.DefineField(countField, 0);

                var dataTable = new DataTable();
                adapter.FetchTypedList(summaryFields, dataTable, summaryBucket, 0, null, false);

Query 2 inheritance relations added manually - doesn't work:


                var fields = new ResultsetFields(7);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(RelatedBFields.TextB, 3, "TextB", "a1");

                fields.DefineField(ObjectCFields.TextC, 4, "TextC", "a3");
                fields.DefineField(ObjectBFields.TestB, 5, "TestB", "a0");
                fields.DefineField(ObjectAFields.Caption, 6, "ACaption", "a2");


                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedAEntity.Relations.GetSubTypeRelation("RelatedBEntity"), "a1", JoinHint.Left);
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a1", "a0", JoinHint.Left);
                bucket.Relations.Add(ObjectBEntity.Relations.GetSuperTypeRelation(), "a0", "a2", JoinHint.Left);
                bucket.Relations.Add(ObjectBEntity.Relations.GetSubTypeRelation("ObjectCEntity"), "a0", "a3", JoinHint.Left);

                var derivedTable = new DerivedTableDefinition(fields, "dt", bucket.PredicateExpression, bucket.Relations, null, null, 0, false);
                var relation = new DynamicRelation(derivedTable, JoinHint.None, null, null);

                var summaryBucket = new RelationPredicateBucket();
                summaryBucket.Relations.Add(relation);

                var countField = new EntityField2("Id", "dt", typeof(int));
                countField.SetAggregateFunction(AggregateFunction.Count);
                countField.SetFieldAlias("cnt");

                var summaryFields = new ResultsetFields(1);
                summaryFields.DefineField(countField, 0);

                var dataTable = new DataTable();
                adapter.FetchTypedList(summaryFields, dataTable, summaryBucket, 0, null, false);
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 18-Sep-2014 03:25:14   

You should not add the inheritance relations.

Otis wrote:

Don't use RelatedAEntity.Relations.GetSubTypeRelation("RelatedBEntity") kind of calls, you should never have to call GetSubTypeRelation or GetSuperTypeRelation. That's done for you, just add the relationships you want to add and avoid adding those, as these aren't to be add by you.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Sep-2014 10:15:53   

Additionally: - add the fields of the type you want to retrieve, so Subtype.Field, not Supertype.Field, even if Field is defined in Supertype but inherited by Subtype

  • You said:

kievBug wrote:

Well, it seems your query will return a bit different results - it returns records of related B, while I want to get all records of related A and also of related B (left join), same thing applies to Object C.

But that's not possible, as you either define fields of RelatedA (but then no relationship is present) OR you define fields of RelatedB (which is-a RelatedA) and a relationship is present. It's not both. It also doesn't make sense: RelatedA has no relationship with ObjectC/B, RelatedB has. While all RelatedB entities are RelatedA instances, the RelatedA instances which aren't RelatedB instances have no relationship with ObjectB/C, and therefore shouldn't be part of the resultset.

I.o.w.:

fields.DefineField(RelatedAFields.Id, 0);
fields.DefineField(RelatedAFields.Caption, 1);
fields.DefineField(RelatedAFields.Deleted, 2);

should be

fields.DefineField(RelatedBFields.Id, 0);
fields.DefineField(RelatedBFields.Caption, 1);
fields.DefineField(RelatedBFields.Deleted, 2);

If you want all 'RelatedA' instances, you simply have to cross join that to the set, but as I said, that doesn't make any sense, as there's no relationship between ObjectB/C and RelatedA.

Frans Bouma | Lead developer LLBLGen Pro
kievBug
User
Posts: 105
Joined: 09-Jan-2009
# Posted on: 18-Sep-2014 16:22:55   

Guys, I do want to retrieve all records from Related A, not from Related B.

Related A may have another object inherited from it, it is a base entity and I want all records from it. Those will include instances of only Related A, plus instances of Related B, and instances of other entities inherited from Related A.

I will try to go step by step:

So in this case this code will work:


                var fields = new ResultsetFields(3);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

Now, to the same results, I want to add several fields from inherited entities, specifically from Related B, but please note that I want to still display all records from Related A, which includes instances of Related A and Related B and other entities inherited from A if any. So this is the code I come up with:


                var fields = new ResultsetFields(4);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(RelatedBFields.TextB, 3, "TextB");

This code works as expected.

Now, I want to add fields from Object B based on the 1:M relation from RelatedB to ObjectB. This is similar to the previous one, I do want to select all records from Related A. I want to use left join to Object B. So the sample sql query that I want to execute may look similar to this one:


SELECT [RelatedA].Id, [RelatedA].Caption, [RelatedA].Deleted,
      [RelatedB].TextB, [ObjectB].TestB
FROM RelatedA 
   LEFT JOIN RelatedB ...
   LEFT JOIN ObjectB ...

So I make changes to the code and now it looks like this:


                var fields = new ResultsetFields(5);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(RelatedBFields.TextB, 3, "TextB");
                fields.DefineField(ObjectBFields.TestB, 4, "TestB", "a0");

                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a0", JoinHint.Left);

                var dataTable = new DataTable();
                adapter.FetchTypedList(fields, dataTable, bucket, 0, null, false);

And this code throws exception:


SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled
  HResult=-2146232832
  Message=An exception was caught during the execution of a retrieval query: The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.ID" could not be bound.
The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.Caption" could not be bound.
The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.Deleted" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  Source=SD.LLBLGen.Pro.ORMSupportClasses.NET20
  RuntimeBuild=09032014
  RuntimeVersion=3.1.0.0
  QueryExecuted=
    Query: SELECT DISTINCT [llblgen_bug].[net].[cusAntoinheritance_RelatedA].[ID] AS [Id], [llblgen_bug].[net].[cusAntoinheritance_RelatedA].[Caption], [llblgen_bug].[net].[cusAntoinheritance_RelatedA].[Deleted], [LPA_L2].[TextB], [LPA_a4].[TestB] FROM ((( [llblgen_bug].[net].[cusAntoinheritance_RelatedA] [LPA_L1]  LEFT JOIN [llblgen_bug].[net].[cusAntoinheritance_RelatedB] [LPA_L2]  ON  [LPA_L1].[ID]=[LPA_L2].[ID]) LEFT JOIN [llblgen_bug].[net].[cusAntoinheritance_ObjectB] [LPA_a4]  ON  [LPA_L2].[ID]=[LPA_a4].[RelatedBId]) LEFT JOIN [llblgen_bug].[net].[cusAntoinheritance_ObjectA] [LPA_a3]  ON  [LPA_a3].[ID]=[LPA_a4].[ID])

  StackTrace:
       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 132
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\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 c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 3066
       at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.1\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 3008
       at Runner.Program.TestFetch() in e:\Crap\InheritanceLlblgen\Runner\Program.cs:line 71
       at Runner.Program.Main(String[] args) in e:\Crap\InheritanceLlblgen\Runner\Program.cs:line 21
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.SqlClient.SqlException
       HResult=-2146232060
       Message=The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.ID" could not be bound.
The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.Caption" could not be bound.
The multi-part identifier "llblgen_bug.net.cusAntoinheritance_RelatedA.Deleted" could not be bound.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=16
       LineNumber=1
       Number=4104
       Procedure=""
       Server=lincoln
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
            at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
            at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
            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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
            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 System.Data.Common.DbCommand.ExecuteReader(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
       InnerException: 

So what is wrong with my code? What needs to be changed?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 18-Sep-2014 18:57:27   
SELECT [RelatedA].Id, [RelatedA].Caption, [RelatedA].Deleted,
     [RelatedB].TextB, [ObjectB].TestB
FROM RelatedA
LEFT JOIN RelatedB ...
LEFT JOIN ObjectB ...

is different from:

                var fields = new ResultsetFields(5);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

as the sql query simply does a left join between RelatedA and RelatedB over a relationship that doesn't exist: the problem is that the join with ObjectB is over a relationship defined between RelatedB and ObjectB, but to be able to define RelatedA left join RelatedB (which means, I want all RelatedA's) you have to specify RelatedAFields, but then there's no notion of the subtype RelatedB. That's like using the subtype relation with a different kind of projection.

Now, to the same results, I want to add several fields from inherited entities, specifically from Related B, but please note that I want to still display all records from Related A, which includes instances of Related A and Related B and other entities inherited from A if any.

This is the main issue. The RelatedA rows are also RelatedB rows, but might not be. you want the join to be done for rows which are relatedB but ignored when they're not RelatedB, which doesn't work. It's like you want all Employee rows and all CompanyCars, which are joined over Boardmember if the Employee is a boardmember, and otherwise it's null. This doesn't make sense, as a 'NULL' means 2 things: either there's no related element OR the type isnt a BoardMember and therefore doesn't have a CompanyCar.

kievBug
User
Posts: 105
Joined: 09-Jan-2009
# Posted on: 18-Sep-2014 19:15:01   

First of all, this code


                var fields = new ResultsetFields(5);
                fields.DefineField(RelatedAFields.Id, 0);
                fields.DefineField(RelatedAFields.Caption, 1);
                fields.DefineField(RelatedAFields.Deleted, 2);

                fields.DefineField(RelatedBFields.TextB, 3, "TextB");
                fields.DefineField(ObjectBFields.TestB, 4, "TestB", "a0");

                var bucket = new RelationPredicateBucket();
                bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a0", JoinHint.Left);

                var dataTable = new DataTable();
                adapter.FetchTypedList(fields, dataTable, bucket, 0, null, false);

is the code that supposed to generate the mentioned query, but not the one you specified.


SELECT [RelatedA].Id, [RelatedA].Caption, [RelatedA].Deleted,
     [RelatedB].TextB, [ObjectB].TestB
FROM RelatedA
LEFT JOIN RelatedB ...
LEFT JOIN ObjectB ...

Second of all why the query


SELECT [RelatedA].Id, [RelatedA].Caption, [RelatedA].Deleted,
     [RelatedB].TextB, [ObjectB].TestB
FROM RelatedA 
LEFT JOIN RelatedB ...
LEFT JOIN ObjectB ...

doesn't make sense?

And yes that is true record of Related A might be instances of Related A or Related B or other inherited from Related A entity and that is exactly the reason why I want to show all records from the base entity - Related A.

Please pay attention that I'm using LEFT JOIN for the relation:

bucket.Relations.Add(RelatedBEntity.Relations.ObjectBEntityUsingRelatedBid, "a0", JoinHint.Left);
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Sep-2014 19:24:42   

To give an example of what Walaa described is the following. We have a test DB with Employee - Manager - BoardMember, where BoardMember has a relationship with CompanyCar.

Your query would come down to: fetch all Employees and the related CompanyCars, if the employee is a BoardMember.

The problem is that this isn't possible to do with an ORM because of the inheritance. This sounds lame and a shortcoming, but it's a result of the fact an inheritance layer is in place over the tables Employee - Manager - BoardMember.

When specifying BoardMember fields and a left join with CompanyCar we'd get SELECT ... FROM Employee INNER JOIN Manager ON.. INNER JOIN BoardMember ON ... LEFT JOIN CompanyCar ON ...

while you want to change these INNER joins into LEFT joins. When using inheritance this is only possible if the relationship is on the supertype, which isn't the case (it's on a subtype).

Try to formulate the query in Linq, it's not possible simple_smile (I tried, as Linq has a cast / is / as statements which should be able to do this, but they either introduce a filter on the results (which isn't what you want) or the query won't compile. )

I know it's odd to not be able to formulate a simple query like this, but it's due to the inheritance in place. (also the resulting rows don't make much sense, considering the NULL values in the resultset: what do they mean?)

Even using a cross join between Employee and CompanyCar, it runs into a snag: where to specify the where clause which defines the relationship between the subtype BoardMember and CompanyCar?

In my example, with Employee, Manager, BoardMember and CompanyCar, it doesn't make sense to select the query you proposed (as the data in the CompanyCar fields make no sense when the employee fields are filled as I don't know whether that's a Boardmember's fields or not). I don't know what RelatedA and ObjectC are, so if you have a situation in which it does make sense, I'm all ears.

Sorry that inheritance makes this so difficult, as the query is indeed very simple... disappointed With normal entities it is no problem.

Frans Bouma | Lead developer LLBLGen Pro
kievBug
User
Posts: 105
Joined: 09-Jan-2009
# Posted on: 18-Sep-2014 19:57:04   

Well, this does sound lame because IMHO it is a very frequent scenario.

Example from our system is - Incidents. There is an Incident entity, which is the parent of the hierarchy. There are different types of incidents which are implemented as sub types of the Incident entity. For example: Vehicle Incident, Injury Incident, etc.

Each of the sub types may have different fields, and navigators to other objects. For example: Vehicle Incident may have a relation to the Car that participated in the incident, and a driver that was in the car, etc.

And user want to see an inventory of Incidents, but one of the types is very important for them for example Vehicle Incidents, and only for those they want to see what Driver was injured, and this information is part of the Vehicle Incident type.

For Drivers, we also have inheritance - and Driver is a sub type of Employee, which is sub type of Subject. Subject has two inherited classes from it - Employee and Group.

This is exactly the case and the query I need you to implement. I need to show the records in the inventory and with paging. Thus I need to have two queries total count, and the data for the page.

And you are saying that it is not supported, right?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Sep-2014 20:48:44   

kievBug wrote:

Well, this does sound lame because IMHO it is a very frequent scenario.

No, that's not the case simple_smile Let me explain:

Your query:

SELECT [RelatedA].Id, [RelatedA].Caption, [RelatedA].Deleted,
     [RelatedB].TextB, [ObjectB].TestB
FROM RelatedA
LEFT JOIN RelatedB ...
LEFT JOIN ObjectB ...

is very reasonable, and simple. The thing is though: RelatedA.Id is part of the RelatedB entity and not part of anything else: A RelatedB entity's fields are not solely stored in the RelatedB table, but in RelatedA and RelatedB. Your query in sql bypasses what's defined in the inheritance hierarchy: it assumes 'RelatedA is stored in the RelatedA table, RelatedB is stored in the RelatedB table', but that's not the case: RelatedB is also stored in the RelatedA table.

In my example I used Employee - Manager - BoardMember. Employee has another subtype, Clerk. To obtain a BoardMember field, I have to (inner) join Employee, Manager and Boardmember. The Employee rows in that join set are ONLY part of BoardMember entities, and of nothing else. If I want also Clerks and Managers, I have to join the tables of those entities (Employee twice, Manager once, Clerk once) again.

The reason is that you work with inheritance entities and not with the raw tables. To get the query you formulated above, you need entities mapped onto those tables which don't share an inheritance hierarchy. then you can simply define the query and it works as-is. This is supported btw: you can map extra entities onto the same tables with relationships and use those e.g. solely in fetch scenarios where you want to have flat lists bypassing the inheritance.

With inheritance it's simply a fact that if you specify a subtype field in a projection the supertypes are joined in the query as well, and those rows are not part of any other entity (as a row in a supertype table isn't shared among subtypes!).

I'll now look into your model (please, next time start with this instead of after x posts. Names like 'RelatedA' and 'ObjectC' have no real meaning, and obfuscate what you want to do so we can't make suggestions to alter the query to get what you're really after).

Example from our system is - Incidents. There is an Incident entity, which is the parent of the hierarchy. There are different types of incidents which are implemented as sub types of the Incident entity. For example: Vehicle Incident, Injury Incident, etc.

Each of the sub types may have different fields, and navigators to other objects. For example: Vehicle Incident may have a relation to the Car that participated in the incident, and a driver that was in the car, etc.

And user want to see an inventory of Incidents, but one of the types is very important for them for example Vehicle Incidents, and only for those they want to see what Driver was injured, and this information is part of the Vehicle Incident type.

For Drivers, we also have inheritance - and Driver is a sub type of Employee, which is sub type of Subject. Subject has two inherited classes from it - Employee and Group.

This is exactly the case and the query I need you to implement. I need to show the records in the inventory and with paging. Thus I need to have two queries total count, and the data for the page.

And you are saying that it is not supported, right?

What would the resultset look like? I mean, if you simply return incident fields and driver fields, what does it mean if there's no driver data (null) ? Does that mean there's no driver known, or that the incident isn't a vehicle incident?

That's the problem with the query you want to formulate. In SQL which bypasses the inheritance hierarchy, you can join whatever you want together, even if the ON clause predicate doesn't make sense simple_smile but that doesn't mean the resultset makes sense.

E.g. when the incident is an Injury incident, the 'Car' fields in the resultset are NULL, however what does that mean? No driver (obviously, Injury doesn't have a relationship with car) but as the Injury fields are just injury fields, this is unclear.

I hope it is now more clear why the query isn't definable (it's not as if we deliberately added code to stop you from writing this query, or that we deliberately didn't add code to support this scenario, it's simply not possible to formulate the query, like I said, try it in linq, you'd be surprised how hard it is (I couldn't find a way to do it)).

So it's very well possible to define all kinds of projections over the data in your database, it's not possible to avoid the extra joins of the RelatedA rows again, as I tried to describe above. The way you formulated the sql query is logical, however it falls in the same category as 'I could change the PK / FK field in the subtype row to point it to a different supertype row' which in theory is logical and doable (it's just an UPDATE) but it too bypasses inheritance and therefor not supported.

Is it necessary that all incidents are shown in one flat list with the car data joined to it? Or that you show the Vehicle incident rows with the car data and the other incident rows separately? (as 'car' data is only useful in the vehicle incident set)

Hopefully this makes a bit of sense now simple_smile

Frans Bouma | Lead developer LLBLGen Pro