I've been banging my head against the wall on this one for a while now. I'm not sure if asking for assistance with particular coding problems is frowned upon, but since its so closely related with using the generated code here goes...

Background: I have built a few overloaded shortcut methods that build children based on an object hierarchy. The entities look like this.

Super-type -> Sub-type

ObjectEntity ObjectEntity->NtsShowEntity ObjectEntity->NtsExhibitorListEntity ObjectEntity->... etc. ObjectRelationEntity (no subtypes)

Definitions:

ObjectRelationEntity (ObjectId, ParentId, and others) The ObjectId denotes the Child ObjectEntity in the relationship, the ParentId relates to the Parent ObjectEntity in the relationship.

Therefore ObjectEntity can have a number of related ObjectRelationEntity(s) which are "Child Relations"(one's where its the ParentId) and "Parent Relations"(one's where its the ObjectId)

The Code:

I've created some methods in the ObjectEntity class to build the child relationships. The first three GetChildren() methods feed into the last one that does all the real work.:

        public struct GetChildrenAliases
        {
            public static string ChildObjectEntity = "";
            public static string ObjectRelations = "ChildObjectRelations";
            public static string ParentObjectEntity = "ParentObjectEntity";
        }

        /// <summary>
        /// Gets all children related through ObjectRelations (published, draft, deleted)
        /// </summary>
        public ObjectCollection GetChildren()
        {
            return GetChildren(false);
        }

        /// <summary>
        /// Gets children related through ObjectRelations, optionally excluding unpublished
        /// </summary>
        /// <param name="MustBePublished">if true, then the ObjectEntity.PublishedPredicate (aliased version) will be applied.</param>
        /// <returns></returns>
        public ObjectCollection GetChildren(bool MustBePublished)
        {
            if (MustBePublished)
                return GetChildren(ObjectEntity.GetAliasedPublishedPredicate(GetChildrenAliases.ChildObjectEntity));
            else
                return GetChildren(null);
        }

        /// <summary>
        /// Gets children related through ObjectRelations, optionally excluding unpublished
        /// </summary>
        /// <param name="selectFilter">MUST refer to Aliased Entities in the filter using GetChildrenAliases struct</param>
        /// <param name="MustBePublished">if true, then the ObjectEntity.PublishedPredicate (aliased version) will be applied.</param>
        /// <returns></returns>
        public ObjectCollection GetChildren(IPredicate selectFilter, bool MustBePublished)
        {
            
            if (MustBePublished)
            {
                PredicateExpression filter = ObjectEntity.GetAliasedPublishedPredicate(GetChildrenAliases.ChildObjectEntity);
                if (selectFilter == null)
                    return GetChildren(filter);
                else
                    return GetChildren(filter.AddWithAnd(selectFilter));

            }
            else
                return GetChildren(selectFilter);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="selectFilter">MUST refer to Aliased Entities in the filter using GetChildrenAliases struct</param>
        /// <returns></returns>
        public ObjectCollection GetChildren(IPredicate selectFilter)
        {           
            ObjectCollection children = new ObjectCollection();

            //Optimized retrieval of child objects from ObjectRelations, using Prefetch Paths.
            PrefetchPath childrenPath = new PrefetchPath((int)EntityType.ObjectEntity);
            childrenPath.Add(ObjectEntity.PrefetchPathParentRelations);
            childrenPath.Add(ObjectRelationEntity.PrefetchPathParent);

            RelationCollection relationsToUse = new RelationCollection();

            //Child to Relations
            relationsToUse.Add(ObjectEntity.Relations.ObjectRelationEntityUsingObjectId, 
                GetChildrenAliases.ChildObjectEntity,  
                GetChildrenAliases.ObjectRelations, 
                JoinHint.None);

            //Relations to Parent
            relationsToUse.Add(ObjectRelationEntity.Relations.ObjectEntityUsingParentId,
                GetChildrenAliases.ObjectRelations,
                GetChildrenAliases.ParentObjectEntity,
                JoinHint.None);

            PredicateExpression parentChildFilter = new PredicateExpression();

            parentChildFilter.AddWithAnd(
                ObjectFields.ObjectId.SetObjectAlias(GetChildrenAliases.ParentObjectEntity) == this.ObjectId);
            parentChildFilter.AddWithAnd(
                ObjectFields.ObjectId.SetObjectAlias(GetChildrenAliases.ParentObjectEntity) == ObjectRelationFields.ParentId.SetObjectAlias(GetChildrenAliases.ObjectRelations) );
            parentChildFilter.AddWithAnd(
                ObjectFields.ObjectId.SetObjectAlias(GetChildrenAliases.ChildObjectEntity) == ObjectRelationFields.ObjectId.SetObjectAlias(GetChildrenAliases.ObjectRelations));

            if (selectFilter != null)
                parentChildFilter.AddWithAnd(selectFilter);

            if (Transaction != null)
                Transaction.Add(children);
            children.GetMulti(parentChildFilter, 0, null, relationsToUse, childrenPath);
            
            return children;   
        }

I have this set of test cases, I've noted which ones won't work.

            //ObjectCollection children = show.GetChildren(); //Broken
            //ObjectCollection children = show.GetChildren(false); //Broken
            //ObjectCollection children = show.GetChildren(true);
            ObjectCollection children = show.GetChildren(NtsExhibitorListEntity.GetEntityTypeFilter()); //Broken
            //ObjectCollection children = show.GetChildren(NtsExhibitorListEntity.GetEntityTypeFilter(true)); //WORKS, Quite ODD
            //ObjectCollection children = show.GetChildren(null); //Broken
            //ObjectCollection children = show.GetChildren(null, false); //Broken
            //ObjectCollection children = show.GetChildren(null, true);
            //ObjectCollection children = show.GetChildren(NtsExhibitorListEntity.GetEntityTypeFilter(), false); //Broken
            //ObjectCollection children = show.GetChildren(NtsExhibitorListEntity.GetEntityTypeFilter(), true);

You'll notice that all the cases where the PublishedPredicate is added to the selectFilter (the ones that pass "true" as the first or second param) work whether or not the additional selectFilter has anything in it. The only one that works without the PublishedPredicate is the one that NtsExhibitorListEntity.GetEntityTypeFilter(true) (the negated entity filter).

The Error for all cases marked "broken": System.Data.SqlClient.SqlException: The column prefix 'CMS!NASDA!TradeShows.dbo.ObjectRelations' does not match with a table name or alias name used in the query. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 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 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)

And of course, this happens on the GetMulti() call.

Now, the really annoying thing is, these all did work before, but it appears as though I've done something to mess them up. I honestly can't think of anything I've changed since they all worked, but maybe the exception will help shed some light on the problem. Its a very simple and common SQL Server error, but it just doesn't make sense why it shows up sometimes, and not others. The fact that : ObjectCollection children = show.GetChildren(NtsExhibitorListEntity.GetEntityTypeFilter()); doesn't work, but: ObjectCollection children = show.GetChildren(NtsExhibitorListEntity.GetEntityTypeFilter(true)); does, seems quite impossible.

If you can think of any reason for this seemingly inconsistent behavior, I'd appreciate your input. Thanks

Shawn

(sorry for the long post)

OK! I fixed it, but... I'm not sure why it didn't work before.

When I change the ObjectRelations alias in GetChildrenAliases to string.Empty, the darn thing works, but... Why didn't it work before? Shouldn't I be able to assign an alias for ObjectRelation in both places I've used it (the end entity of the first relation, and the start relation of the second) and have it work??

Kind of confusing. I'm sure I'm missing something pretty important here. My guess is I can't reuse Aliases? I figured I had to, so that the engine would know how to build the query, I guess I misunderstand how they're supposed to work.

-Shawn

Before I'll try to answer you, I have to say that I have no idea what is going on really in your code. The code is IMHO complex to read and understand. You also have ObjectEntity.GetAliasedPublishedPredicate(GetChildrenAliases.ChildObjectEntity) in your code, what does it do?

In GetChildren you do:

   relationsToUse.Add(ObjectEntity.Relations.ObjectRelationEntityUsingObjectId,
                GetChildrenAliases.ChildObjectEntity,
                GetChildrenAliases.ObjectRelations,
                JoinHint.None);

this aliasses 'ObjectEntity' to "", which means no alias is specified, thus it will KEEP its full name it also aliases ObjectRelationEntity to "ChildObjectRelations"

Now, you also do:

relationsToUse.Add(ObjectRelationEntity.Relations.ObjectEntityUsingParentId,
                GetChildrenAliases.ObjectRelations,
                GetChildrenAliases.ParentObjectEntity,
                JoinHint.None);

This aliases ObjectRelationEntity to ChildObjectRelations again, and it aliases ObjectEntity as "ParentObjectEntity".

The ObjectRelationEntity alias is unnecessary. Only alias an entity if it's joined multiple times.

Relations between entities in a hierarchy were a problem, as in some bizarre constructs it could happen inheritance info wasn't read. As you don't say which runtime lib version and buildnr you're using I can't answer further why it doesn't work and if it's patched if it's a bug.

Also, if a test fails with a SQL error, ALWAYS turn on DQE tracing and check what query is generated. This way you can check if the specified aliases are propagated into the query or not.

Another tip: don't overdo inheritance with entity types. You seem to use a central entity which is the base entity for a lot of other entities. Don't do that, because it will cost a lot of performance because every time you pull an entity from the db, there will be joins. As all entities in the model are subtypes, every entity will pull its own supertype rows and joins. This will create big queries which are in a lot of cases unnecessary. If you're using V2.0 and you want a central base class between the EntityBase class and every generated entity class, use the additional templates in the 3rd party section which generate a class between the generated entity classes and EntityBase for shared code for example.

Thanks for taking the time to reply Otis. I'm sorry for the confusing code. You're absolutely right, I was not clear, and I appreciate you trying. I'll elaborate.

First, I am using V2.0.0.0 (Demo) We'll be buying it soon; Its a great tool. And .NET 2.0

A little more background: This system is a Custom CMS for controlling web content that the company I work for developed 5 years ago (I didn't work for them then), and I've taken it upon myself to try to make it "good"(ha). So, you're right, all of the 'CMS Object' Entities are a subtype of 'Object'. Thankfully not everything is a subtype of Object, permissions and other uninteresting lookup tables aren't (so that's pretty good, right? ~sarcasm~)

The ObjectEntity holds the common information for all CMS Objects of the system. It has most of the key relationships for things like: Which Users and Groups of the CMS have access to the Object, What other Objects are children of this Object, What other Objects are Parents of this Object, Audit Trail, Etc,

And other information like, whether its a draft, deleted, published, which object is the Primary Parent, etc.

GetAliasedPublishedPredicate is just a method that builds a standard where clause to bring back only "published" CMS Objects. As I mentioned, the fields which determine if its "published" are part of the ObjectEntity. This method also adds the supplied alias passed into into it.

At any rate, what you said regarding the ObjectRelation Alias was exactly the problem. And it makes sense now, thanks for explaining that.

Also, I realize by aliasing something to "" that I'm not really aliasing it. I was just doing this for the sake of completeness since it does matter for ParentObjectEntity.

So basically the code I attached previously, (the GetChildren methods) would simply build a collection of ObjectEntities based on a given ObjectEntity's related ObjectRelationEntities where it was participating as the "Parent" in the relationship. Hopefully that makes more sense now.

Another tip: don't overdo inheritance with entity types. You seem to use a central entity which is the base entity for a lot of other entities. Don't do that, because it will cost a lot of performance because every time you pull an entity from the db, there will be joins. As all entities in the model are subtypes, every entity will pull its own supertype rows and joins. This will create big queries which are in a lot of cases unnecessary.

This is true. And I have cursed the inefficiency of this system since shortly after I began working with it, however, since I can't radically change the datamodel (only make enhancements to make it work with the object mapper, while remaining backwards compatible -- not fun, by the way ) I think I'm kind of stuck using this central entity because it holds all information regarding an ObjectEntity's relationship to other ObjectEntities' in the CMS Hierarchy. Since there are no theoretical restrictions on those relationships, any ObjectEntity can be a child or parent of any other ObjectEntity, and an ObjectEntity can exist as a child of 1 or many other ObjectEntities.

If you're using V2.0 and you want a central base class between the EntityBase class and every generated entity class, use the additional templates in the 3rd party section which generate a class between the generated entity classes and EntityBase for shared code for example.

If I understand what you're suggesting, this might be a good idea performance wise, but it would take a lot of time to integrate this kind of approach. It would require putting the common base fields of the ObjectEntity directly in their subtypes, and then creating relating tables for each combination of object relationship. So then the OR mapper could generate collections of all the related Children and Parents of a given type for each ObjectEntity subtype. Of course where we currently have 1 Join table for all ObjectEntity relationships we'd have about SUM( 2(i-1) + 1 ) for i = 1 to n , for n elements (closed form anyone? ) number of join tables (YIKES!)... And then I could create shared code, as you suggest, in the Intermediate EntityBase class that could manually join all of these entities together, if I ever needed them all together in one collection. Is that kind of what you're suggesting?

I realize this is difficult for you to comment, because you don't have most of the details of our system. But in light of the information I've given you with respect to how ObjectEntities can be related to one another in the web content hierarchy, would you still recommend abandoning the central object entity? (Don't feel obligated to comment on this, its probably a bit beyond the scope of general LLBLGen discussion)

A specific question regarding the query engine.

If I create an ObjectCollection (which is a collection of central ObjectEntities), but in the GetMulti call I give it the GetEntityTypeFilter() of one of its subtypes, shouldn't it be possible for it to avoid joining in all of the other subtypes? I guess maybe a better question would be, can I specifically request only certain subtypes be joined in automatically (when using GetMulti)?

Thanks

Shawn

If I create an ObjectCollection (which is a collection of central ObjectEntities), but in the GetMulti call I give it the GetEntityTypeFilter() of one of its subtypes, shouldn't it be possible for it to avoid joining in all of the other subtypes? I guess maybe a better question would be, can I specifically request only certain subtypes be joined in automatically (when using GetMulti)?

Please refer to Filtering on entity type in the LLBLgen Pro manual under "Using the generated code -> SelfServicing-> Filtering and sorting -> Advanced filter usage"

Not sure if you have that implemented, but just in case, Inheritence pattern should imply exclusive inherited entities. If not, then how do you perform a delete when multiple children are involved?

That said, you should be able to request several subtypes withe the EntityType Filters since they're regular IPredicate you can add with on "OR" to a parent expression.

Thanks for the responses.

I am using exclusive inheritence from the perspective of subtypes. But the entities' instances themselves can be related to one another in a parent/child hierarchy created by the {ObjectEntity} relationship to {ObjectRelationEntity}. An {ObjectEntity}(or one of its many subtypes) can exist as the Parent or the Child in the {ObjectRelationEntity}(ies) it's related to. It's for that reason that I wrote the "GetChildren()" methods. For finding an {ObjectEntity}'s children based on this hierarchy.

When I asked about filtering on subtypes I was more so asking for the sake of optimization, because I often only want children of a certain type (or small set of types). So I guess my question was if there's a way to optimize the query engine so that it doesn't join in all the subtypes when building the {ObjectEntityCollection}, and only the ones I've specified. But the more I think about it, SQL Server may optimize the query enough on its own. Actually, before I continue spouting bs, I should probably take a look at what it generates when I specify an entity filter, but the query is so massive, its quite painful to read through.

Shawn

Specifying a type filter won't make the query less big: the filter will be appended as a where clause. It will make the rdbms be able to optimize the execution plan better, but it won't make llblgen pro emit less joins for example.

Right. I figured that was the best I could hope for.

Thanks.