Ok, say I have an inheritance situation where SuperEntity is the supertype and SubOneEntity and SubTwoEntity are subtypes of SuperEntity. There is a field, lets call it SuperField, which is defined in SuperEntity and inherited down to SubOneEntity and SubTwoEntity. Now, there is another entity, OtherEntity, that has a 1:N relationship with SubOneEntity, and another 1:N relationship with SubTwoEntity. I want to get a collection of these OtherEntity instances, filtered a particular way, so I do:

Dim otherEntities As New EntityCollection(New OtherEntityFactory())
Dim bucket As New RelationPredicateBucket()
bucket.Relations.Add(OtherEntity.Relations.SubOneEntityUsingSubOneID)
bucket.Relations.Add(OtherEntity.Relations.SubTwoEntityUsingSubTwoID)
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(SubOneFieldIndex.SuperField, ComparisonOperator.Equal, "MyValue"))
Dim adapter As New DataAccessAdapter()
adapter.FetchEntityCollection(otherEntities, bucket)

The way the code seems to be generated, this won't work, as the SuperField predicate will not know which entity type it is working against, SubOneEntity or SubTwoEntity...right? This is because the Predicate I created will just end up getting an instance of a SuperEntity.SuperField, so it won't know whether to apply this filter to the SubOneEntity relation or the SubTwoEntity relation. This doesn't seem obvious, because when I created the predicate, I specifically said:

bucket.PredicateExpression.Add(PredicateFactory.CompareValue(SubOneFieldIndex.SuperField, ComparisonOperator.Equal, "MyValue"))

Is this correct?

Are you using a single table with a discriminator? You may just include this discriminator in your predicate expression. I believe SubOneFieldIndex.SuperField will just know which field in which table it should use to refernce, but it wouldn't add the predicate automatically.

Are you running into this issue or asking before you experiment? If you have the query that is generated that may help too.

bclubb wrote:

Are you using a single table with a discriminator? You may just include this discriminator in your predicate expression. I believe SubOneFieldIndex.SuperField will just know which field in which table it should use to refernce, but it wouldn't add the predicate automatically.

Are you running into this issue or asking before you experiment? If you have the query that is generated that may help too.

Yes, we are using single table inheritance.

In the line:

bucket.PredicateExpression.Add(PredicateFactory.CompareValue(SubOneFieldIndex.SuperField, ComparisonOperator.Equal, "MyValue"))

shouldn't the predicate that comes from the predicate factory indicate that we are filtering on the SubOneEntity and not a generic baseclass SuperEntity?

I am running into this issue because our current security mechanism relies on the filters sent to the DataAccessAdapter to contain information about specifically which entities they are for. For example, when there is a collection fetch with a filter on a given entity, our security system will check and make sure the user has the rights to filter on whichever fields are being filtered on in that specific entity. If there is no way to know the specific entity (because the field attached to the Predicate is a field from the base class) then our security system will not work correctly.

Please post the following: 1- The generated query 2- The query you want instead 3- The runtimeLibrary version

Also please check the Filtering on entity type section in the LLBLGen Pro docs "Using the generated code -> Adapter -> Filtering and sorting -> Advanced filter usage"

Walaa wrote:

Please post the following: 1- The generated query 2- The query you want instead 3- The runtimeLibrary version

Also please check the Filtering on entity type section in the LLBLGen Pro docs "Using the generated code -> Adapter -> Filtering and sorting -> Advanced filter usage"

I am using 1.0.2005.1

I can't get a query generated because of this problem, so I can't post it, but I would think it would look something like:

Select * from Other Join Super On Super.Super_ID = Other.SubOne_ID And Super.SuperType = 'One' Where Super.SuperField = 1

This is my code:

        Dim otherCollection As New HelperClasses.EntityCollection(New FactoryClasses.OtherEntityFactory)
        Dim bucket As New RelationPredicateBucket
        bucket.Relations.Add(EntityClasses.OtherEntity.Relations.SubOneEntityUsingSubOneId)
        bucket.PredicateExpression.Add(FactoryClasses.PredicateFactory.CompareValue(SubOneFieldIndex.SuperField, ComparisonOperator.Equal, 1))

        Dim daa As New DataAccessAdapter
        daa.FetchEntityCollection(otherCollection, bucket)
        MsgBox(otherCollection.Count)

I get a NullReferenceException on the FetchEntityCollection. Here is the stack trace:

   at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateParameter(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, ParameterDirection direction, Object valueToSet)
   at SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareValuePredicate.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, 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, IDbConnection 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, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
   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)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket)
... 

My table structure is as follows:

Super (base class)
    |                  |
    V                V
SubOne       SubTwo (Child classes, using single table inheritance)

Other (different table)
PK
SubOne_ID 
SubTwo_ID

I am trying to bring back all OtherEntities that have SubOneEntities with SuperField=1. SuperField is mapped in the SuperEntity. It is inherited down to SubOneEntity and SubTwoEntity.

Help!

Please submit the RuntimeLibrary version which you are using. Refer to the following thread to know how to get the RTL version: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7722

Walaa wrote:

Please submit the RuntimeLibrary version which you are using. Refer to the following thread to know how to get the RTL version: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7722

I am using 1.0.2005.1 build # 07192006

I should also add that I don't get the exception when I map the SuperField to the SubOne and SubTwo entities, instead of to the base class which SubOneEntity and SubTwoEntity inherit from.

I should also add that I don't get the exception when I map the SuperField to the SubOne and SubTwo entities, instead of to the base class which SubOneEntity and SubTwoEntity inherit from.

What is the query that this executes?

bclubb wrote:

I should also add that I don't get the exception when I map the SuperField to the SubOne and SubTwo entities, instead of to the base class which SubOneEntity and SubTwoEntity inherit from.

What is the query that this executes?

I'm not sure how to get the real, non parameterized query, but this is the one that shows up in the trace:

    Query: SELECT DISTINCT [test].[dbo].[Other].[Other_ID] AS [OtherId], [test].[dbo].[Other].[SubOne_ID] AS [SubOneId], [test].[dbo].[Other].[SubTwo_ID] AS [SubTwoId] FROM ( [test].[dbo].[Super]  INNER JOIN [test].[dbo].[Other]  ON  [test].[dbo].[Super].[Super_ID]=[test].[dbo].[Other].[SubOne_ID]) WHERE ( ( [test].[dbo].[Super].[SuperField] = @SuperField1))
    Parameter: @SuperField1 : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: 1.

Ok, it looks like I messed up the code generation before and now I don't get the Exception. Sorry about that

Ok, so I changed the code to:

        Dim otherCollection As New HelperClasses.EntityCollection(New FactoryClasses.OtherEntityFactory)
        Dim bucket As New RelationPredicateBucket
        bucket.Relations.Add(EntityClasses.OtherEntity.Relations.SubOneEntityUsingSubOneId)
        bucket.Relations.Add(EntityClasses.OtherEntity.Relations.SubTwoEntityUsingSubTwoId)
        bucket.PredicateExpression.Add(FactoryClasses.PredicateFactory.CompareValue(SubOneFieldIndex.SuperField, ComparisonOperator.Equal, 1))
        bucket.PredicateExpression.Add(FactoryClasses.PredicateFactory.CompareValue(SubTwoFieldIndex.SuperField, ComparisonOperator.Equal, 1))

        Dim daa As New DataAccessAdapter
        daa.FetchEntityCollection(otherCollection, bucket)
        MsgBox(otherCollection.Count)

and now the generated query becomes:

    Query: SELECT DISTINCT [test].[dbo].[Other].[Other_ID] AS [OtherId], [test].[dbo].[Other].[SubOne_ID] AS [SubOneId], [test].[dbo].[Other].[SubTwo_ID] AS [SubTwoId] FROM ( [test].[dbo].[Super]  INNER JOIN [test].[dbo].[Other]  ON  [test].[dbo].[Super].[Super_ID]=[test].[dbo].[Other].[SubOne_ID]) WHERE ( ( [test].[dbo].[Super].[SuperField] = @SuperField1 AND [test].[dbo].[Super].[SuperField] = @SuperField2))
    Parameter: @SuperField1 : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @SuperField2 : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: 1.

The Super table is only being joined on once, even though I have two relations to it (via 2 different subtypes) and two separate Predicates. This is returning an incorrect result. Now I add in Aliases for the two joins:

        Dim otherCollection As New HelperClasses.EntityCollection(New FactoryClasses.OtherEntityFactory)
        Dim bucket As New RelationPredicateBucket
        bucket.Relations.Add(EntityClasses.OtherEntity.Relations.SubOneEntityUsingSubOneId, "one")
        bucket.Relations.Add(EntityClasses.OtherEntity.Relations.SubTwoEntityUsingSubTwoId, "two")
        bucket.PredicateExpression.Add(FactoryClasses.PredicateFactory.CompareValue(SubOneFieldIndex.SuperField, ComparisonOperator.Equal, 1, "one"))
        bucket.PredicateExpression.Add(FactoryClasses.PredicateFactory.CompareValue(SubTwoFieldIndex.SuperField, ComparisonOperator.Equal, 1, "two"))

        Dim daa As New DataAccessAdapter
        daa.FetchEntityCollection(otherCollection, bucket)
        MsgBox(otherCollection.Count)

and the generated query is:

    Query: SELECT DISTINCT [test].[dbo].[Other].[Other_ID] AS [OtherId], [test].[dbo].[Other].[SubOne_ID] AS [SubOneId], [test].[dbo].[Other].[SubTwo_ID] AS [SubTwoId] FROM (( [test].[dbo].[Super] [LPA_o2]  INNER JOIN [test].[dbo].[Other]  ON  [LPA_o2].[Super_ID]=[test].[dbo].[Other].[SubOne_ID]) INNER JOIN [test].[dbo].[Super] [LPA_t4]  ON  [LPA_t4].[Super_ID]=[test].[dbo].[Other].[SubTwo_ID]) WHERE ( ( [LPA_o2].[SuperField] = @SuperField1 AND [LPA_t4].[SuperField] = @SuperField2))
    Parameter: @SuperField1 : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @SuperField2 : AnsiString. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: 1.

which seems to return the correct result. So is it that anytime two subtypes in the same heirarchy are being joined to and filtered, each relation has to be aliased?

We are looking into it, and will get back to you soon.

If I understand it correctly, it's that you want the filtering engine to consider the supertype field to be in one of the subtypes? If that's the case you have to add a typefilter, so a filter for the particular type. (See filtering and sorting in the manual for details on this). This is necessary because the field you're filtering on is in the supertype, so all SUBtypes, no matter what they are, are matching. If you want to limit the amount of matching subtypes, you have to add a filter for that particular type.

I know this is unnatural, though in the current architecture, the fields don't know the entity they're actually in (e.g. subtype) only the entity they're defined in (e.g. supertype). An architectural change in v2.1 will make this possible but of course that's not important for your situation as you're using 1.0.2005.1, so in your situation please add the type filter.

Walaa said:

Also please check the Filtering on entity type section in the LLBLGen Pro docs "Using the generated code -> Adapter -> Filtering and sorting -> Advanced filter usage

Otis wrote:

If I understand it correctly, it's that you want the filtering engine to consider the supertype field to be in one of the subtypes? If that's the case you have to add a typefilter, so a filter for the particular type. (See filtering and sorting in the manual for details on this). This is necessary because the field you're filtering on is in the supertype, so all SUBtypes, no matter what they are, are matching. If you want to limit the amount of matching subtypes, you have to add a filter for that particular type.

I know this is unnatural, though in the current architecture, the fields don't know the entity they're actually in (e.g. subtype) only the entity they're defined in (e.g. supertype). An architectural change in v2.1 will make this possible but of course that's not important for your situation as you're using 1.0.2005.1, so in your situation please add the type filter.

Thanks for the response, it was sort of what I was expecting...I was hoping you'd see it as a bug and so fix it in 1.0.2005.1, but I've failed!

I'm not really saying that the filtering engine should recognize that a supertype field resides in a subtype as much as the predicate should realize that it is for a particular subtype, if that makes any sense. I don't really know if it makes sense for a field to know the derived type it is in...it probably shouldn't care. But the predicate should know what subtype it is for in case there are relations against two different sibling subtypes (like in the code I have above).

I'm all for changing our templates though, and we use a derived version of your EntityField2 object (we make your EntityFieldFactory create these derived fields instead of the originals). So, do you have any suggestions on the places where this would need changing in your architecture if I were to try and fix this myself? I can see that the DatabaseSpecificCreator classes seem to use the ContainingObjectName when they are producing the query text...I'm guessing it won't be quite as easy as changing the FieldFactory to setting the ContainingObjectName to be the subtype name...I know there's also the problem with making the relations alias themselves for subtypes and using these aliases in the predicateexpressionelements that use these subtypes...

Maybe adding an EntityType filter in the Relation's custom filter as well as in the PredicateExpression would work, as Walaa suggested? I mean in the the templates so these EntityType filters are generated automatically for subtype predicates?

mikeg22 wrote:

Otis wrote:

If I understand it correctly, it's that you want the filtering engine to consider the supertype field to be in one of the subtypes? If that's the case you have to add a typefilter, so a filter for the particular type. (See filtering and sorting in the manual for details on this). This is necessary because the field you're filtering on is in the supertype, so all SUBtypes, no matter what they are, are matching. If you want to limit the amount of matching subtypes, you have to add a filter for that particular type.

I know this is unnatural, though in the current architecture, the fields don't know the entity they're actually in (e.g. subtype) only the entity they're defined in (e.g. supertype). An architectural change in v2.1 will make this possible but of course that's not important for your situation as you're using 1.0.2005.1, so in your situation please add the type filter.

Thanks for the response, it was sort of what I was expecting...I was hoping you'd see it as a bug and so fix it in 1.0.2005.1, but I've failed!

I'm not really saying that the filtering engine should recognize that a supertype field resides in a subtype as much as the predicate should realize that it is for a particular subtype, if that makes any sense. I don't really know if it makes sense for a field to know the derived type it is in...it probably shouldn't care. But the predicate should know what subtype it is for in case there are relations against two different sibling subtypes (like in the code I have above).

Exactly, so making it more usable in a sense that you can predict what happens when you write a given piece of code, it should know this information and act accordingly. Unfortunately I can't make the change for 1.0.2005.1, because it requires architectural changes and I can't make them in a released version because that would break applications.

I'm all for changing our templates though, and we use a derived version of your EntityField2 object (we make your EntityFieldFactory create these derived fields instead of the originals). So, do you have any suggestions on the places where this would need changing in your architecture if I were to try and fix this myself? I can see that the DatabaseSpecificCreator classes seem to use the ContainingObjectName when they are producing the query text...I'm guessing it won't be quite as easy as changing the FieldFactory to setting the ContainingObjectName to be the subtype name...I know there's also the problem with making the relations alias themselves for subtypes and using these aliases in the predicateexpressionelements that use these subtypes...

It's in the factories. The field factories have to inject the typename the fields are actually in. This is currently not done, and the factories have to be constructed differently as well, as they now simply call the factory of the supertype to produce the fields for the supertype, however they then have to post-process these fields for the type info.