Databases
StackOverflow2013
Postsdbo
PONhibernate generating OUTER JOIN for a fetch
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONhibernate generating OUTER JOIN for a fetch
    text
    copied!<p>Despite setting up mapping to be <code>Not.Nullable()</code> and <code>Not.LazyLoad()</code></p> <p>For some reason NH is joining a table twice, once with a INNER JOIN to appease the WHERE, and secondly on a OUTER JOIN to select the data.</p> <p>Surely, as we've already JOINED the data, it would make sense to just use the joined table...</p> <pre><code>SELECT ...Tables.. from Tasks taskentity0_, outer Cases caseentity1_, outer Grades gradeentit2_, Cases caseentity5_ WHERE .... </code></pre> <p>My LINQ query for this is:</p> <pre><code>IQueryable&lt;TaskEntity&gt; tasks = TaskRepo.Find( t =&gt; t.DueDate &lt;= DateTime.Now &amp;&amp; (t.TaskInitials == userInitials || (t.TaskInitials == "" || t.TaskInitials == null)) &amp;&amp; t.Team.GST.Any (x =&gt; x.Initials == userInitials &amp;&amp; x.WorkType.WorkTypeCode == t.WorkType.WorkTypeCode &amp;&amp; x.Team.TeamCode == t.Team.TeamCode ) &amp;&amp; (t.Case.CaseOnHold &lt;= DateTime.Now || t.Case.CaseOnHold == null || (t.SingleTask == "M" || t.SingleTask == "m")) &amp;&amp; (t.Case.CaseMatter.StartsWith("0") || t.Case.CaseMatter.StartsWith("9")) ).Fetch(t =&gt; t.Case,FetchProvider) </code></pre> <p>My Reference Mapping:</p> <pre><code> References(x =&gt; x.Case).Column("ta_c_ref").Not.Nullable(); </code></pre> <p>Thoughts?</p> <p>We are using the repository pattern, and have reimplemented the Fetch extension method to work this way (Hence passing the FetchProvider in).</p> <p>Also, <code>QueryOver&lt;T&gt;</code> is not an option here as we require <code>IQueryable</code>s..</p> <p>I am using NH 3.1.</p> <p>For the masses:</p> <p>We no longer use the Fetch or LINQ, we moved to HQL...</p> <pre><code> /// &lt;summary&gt; /// Interfaces for Fetch() statements /// &lt;/summary&gt; public interface IFetchingProvider { IFetchRequest&lt;TOriginating, TRelated&gt; Fetch&lt;TOriginating, TRelated&gt;(IQueryable&lt;TOriginating&gt; query, Expression&lt;Func&lt;TOriginating, TRelated&gt;&gt; relatedObjectSelector); IFetchRequest&lt;TOriginating, TRelated&gt; FetchMany&lt;TOriginating, TRelated&gt;(IQueryable&lt;TOriginating&gt; query, Expression&lt;Func&lt;TOriginating, IEnumerable&lt;TRelated&gt;&gt;&gt; relatedObjectSelector); IFetchRequest&lt;TQueried, TRelated&gt; ThenFetch&lt;TQueried, TFetch, TRelated&gt;(IFetchRequest&lt;TQueried, TFetch&gt; query, Expression&lt;Func&lt;TFetch, TRelated&gt;&gt; relatedObjectSelector); IFetchRequest&lt;TQueried, TRelated&gt; ThenFetchMany&lt;TQueried, TFetch, TRelated&gt;(IFetchRequest&lt;TQueried, TFetch&gt; query, Expression&lt;Func&lt;TFetch, IEnumerable&lt;TRelated&gt;&gt;&gt; relatedObjectSelector); } public class NhFetchingProvider : IFetchingProvider { public IFetchRequest&lt;TOriginating, TRelated&gt; Fetch&lt;TOriginating, TRelated&gt;(IQueryable&lt;TOriginating&gt; query, Expression&lt;Func&lt;TOriginating, TRelated&gt;&gt; relatedObjectSelector) { var fetch = EagerFetchingExtensionMethods.Fetch(query, relatedObjectSelector); return new FetchRequest&lt;TOriginating, TRelated&gt;(fetch); } public IFetchRequest&lt;TOriginating, TRelated&gt; FetchMany&lt;TOriginating, TRelated&gt;(IQueryable&lt;TOriginating&gt; query, Expression&lt;Func&lt;TOriginating, IEnumerable&lt;TRelated&gt;&gt;&gt; relatedObjectSelector) { var fecth = EagerFetchingExtensionMethods.FetchMany(query, relatedObjectSelector); return new FetchRequest&lt;TOriginating, TRelated&gt;(fecth); } public IFetchRequest&lt;TQueried, TRelated&gt; ThenFetch&lt;TQueried, TFetch, TRelated&gt;(IFetchRequest&lt;TQueried, TFetch&gt; query, Expression&lt;Func&lt;TFetch, TRelated&gt;&gt; relatedObjectSelector) { var impl = query as FetchRequest&lt;TQueried, TFetch&gt;; var fetch = EagerFetchingExtensionMethods.ThenFetch(impl.NhFetchRequest, relatedObjectSelector); return new FetchRequest&lt;TQueried, TRelated&gt;(fetch); } public IFetchRequest&lt;TQueried, TRelated&gt; ThenFetchMany&lt;TQueried, TFetch, TRelated&gt;(IFetchRequest&lt;TQueried, TFetch&gt; query, Expression&lt;Func&lt;TFetch, IEnumerable&lt;TRelated&gt;&gt;&gt; relatedObjectSelector) { var impl = query as FetchRequest&lt;TQueried, TFetch&gt;; var fetch = EagerFetchingExtensionMethods.ThenFetchMany(impl.NhFetchRequest, relatedObjectSelector); return new FetchRequest&lt;TQueried, TRelated&gt;(fetch); } } public static IFetchRequest&lt;TOriginating, TRelated&gt; Fetch&lt;TOriginating, TRelated&gt;(this IQueryable&lt;TOriginating&gt; query, Expression&lt;Func&lt;TOriginating, TRelated&gt;&gt; relatedObjectSelector, Func&lt;IFetchingProvider&gt; FetchingProvider) { return FetchingProvider().Fetch(query, relatedObjectSelector); } </code></pre>
 

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