StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PONhibernate Polymorphic Query - Eager Load Associations Without Polymorphic Fetch
text
Body
copied!<p>I will start by saying I have already looked thoroughly in stack overflow, nhusers and the documentation for a possible solution to my issue.</p> <p>I need to be able to query only the base class table in parts of my multi/future query when eagerly loading associations (although from the research I have done I don't think this is possible)</p> <p>I have started to map an existing schema using fluent nhibernate as a proof of concept. I have mapped an inheritance hierarchy using table per sub class (The mappings all work perfectly fine so I won't paste them all in here). The hierarchy has around 15 sub classes and the base class has some additional associations. E.g.</p> <pre><code>Base Dictionary<string, Attribute> Attributes List<EntityChange> Changes </code></pre> <p>I need to eagerly load both of the collections as in the given scenario they are required for post processing and lazily loading them causes performance issues. I am eagerly loading them by a multi / future query:</p> <pre><code> var baseQuery = session.CreateCriteria<Base>("b") .CreateCriteria("Nested", JoinType.LeftOuterJoin) .CreateCriteria("Nested2", JoinType.LeftOuterJoin) .CreateCriteria("Nested2.AdditionalNested", JoinType.LeftOuterJoin); var logsQuery = session.CreateCriteria<Base>("b").CreateAlias("Changes", "c", JoinType.LeftOuterJoin, Expression.And(Expression.Ge("c.EntryDate", changesStartDate), Expression.Le("c.EntryDate", changesEndDate))) .AddOrder(Order.Desc("c.EntryDate")); var attributesQuery = session.CreateCriteria<Base>("t").SetFetchMode("Attributes", FetchMode.Join); logsQuery.Future<Base>(); attributesQuery.Future<Base>(); var results = baseQuery.Future<Base>().ToList(); </code></pre> <p>The queries execute and return the correct results. But just to eagerly load the associations in this manner means that the attribute and changes queries have to perform a polymorphic fetch (the addition of about 15 left outer joins per query that aren't required). I know this is required for polymorphic querying but the base query will return the hierarchy that I desire. The other parts of the multi query that are issuing a polymorphic query are redundant.</p> <p>I haven't yet mapped the whole of the hierarchy so there will be additional unecessary joins being performed and there are also other associations that could be loaded up front. These two combined without the addition of an increase in volume will lead to performance issues. The performance currently of this query is about 6 seconds (which admittedly is better than the 20 it's currently taking) but by messing around a bit with the query and taking out the extra joins I can get it down to about 2 seconds (this is a common query so getting it as low as possible is beneficial not just pleasing to me. It will also be run from multiple distributed machine so I would rather not get into a discussion about caching / 2nd level caching). </p> <p>I have tried</p> <ol> <li>using the class modifier in the query 'class = base'. I initially done this blindly but believe this is for discriminator values. Even if it is for the case statement in the SQL this will not prevent the extra joins.</li> <li>Doing everything in a single query. This is slower than splitting it up as above and gives the cartesian product</li> <li>Using 'Polymorphism.Explicit();' in the fluent mappings. This has no effect as I am using ClassMap with SubclassMaps so it is ignored. I tried changing all the maps to ClassMaps and using Join but this didn't give the desired behaviour.</li> <li>Tried to trick nhibernate into joining the base class table onto itself for loading associations (basically load a more concrete type to prevent the polymorphic query) - create a derived class 'BaseOnlyLoading' which uses the same table and primary key as the base class. This was obviously a hack but I was just trying to see what's possible. NHibernate doesn't allow the class and sub class to use the same table.</li> <li>Define the BaseOnlyLoadingMap to be a classmap with the same assocations as the BaseMap with a join back onto the Base. This was hopeful as assocation collections are resolved in the context based on full type name. </li> <li>Use an interceptor which modifies the SQL that before it's execute. I wouldn't use this in production and just tried it out of interest. I passed an interceptor into a local session. This caused issues and I didn't proceed.</li> <li>The HQL 'Type' query operator as explained <a href="http://blog.eyallupu.com/2010/06/hibernate-350-cr-2jpa-20-new-query.html%20here" rel="nofollow noreferrer">here</a> although I am not sure this has been implemented in the .NET version and might behave similarly to 1.</li> </ol> <p>There is comment on highest rated answer (<a href="https://stackoverflow.com/questions/2093025/how-to-perform-a-non-polymorphic-hql-query-in-hibernate">How to perform a non-polymorphic HQL query in Hibernate?</a>) which suggest overriding the IsExplicitPolymorphism on the persister. I had a quick look and from what I remember the persister was either global per entity or created in the SessionImpl from a static factory which would prevent doing this. Even if this was possible I am not sure what sort of side effects this would have. </p> <p>I tried using some SQL to load everything but even if I use a stored proc I am not sure how nhibernate will piece the graph back together. Maybe I could specify all the entities and aliases?</p> <p>Specifying explicit per query would be nice. Any suggestions?</p> <p>Thanks in advance.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload