Nhibernate generates an OUTER JOIN to fetch

Despite the fact that the setting will display Not.Nullable()andNot.LazyLoad()

For some reason, NH joins the table twice, once with INNER JOIN to calm WHERE, and secondly, in OUTER JOIN to select data.

Of course, since we have already joined the data, it makes sense to just use a joined table ...

SELECT
     ...Tables..
from Tasks taskentity0_,
 outer Cases caseentity1_,
 outer Grades gradeentit2_,
 Cases caseentity5_ 
WHERE
....

My LINQ query for this:

IQueryable<TaskEntity> tasks = TaskRepo.Find(
    t => t.DueDate <= DateTime.Now
      && (t.TaskInitials == userInitials || (t.TaskInitials == "" || t.TaskInitials == null))
      && t.Team.GST.Any
                    (x => x.Initials == userInitials
                        && x.WorkType.WorkTypeCode == t.WorkType.WorkTypeCode
                        && x.Team.TeamCode == t.Team.TeamCode
                    )
      && (t.Case.CaseOnHold <= DateTime.Now || t.Case.CaseOnHold == null || (t.SingleTask == "M" || t.SingleTask == "m"))
      && (t.Case.CaseMatter.StartsWith("0") || t.Case.CaseMatter.StartsWith("9"))
                ).Fetch(t => t.Case,FetchProvider)

My Reference Mapping:

        References(x => x.Case).Column("ta_c_ref").Not.Nullable();

Thoughts?

We use the repository template and redefined the Fetch extension method to work this way (hence passing FetchProvider to).

Also, QueryOver<T>here is not an option, since we need IQueryables ..

I am using NH 3.1.

For the masses:

We no longer use Fetch or LINQ, we moved to HQL ...

    /// <summary>
    /// Interfaces for Fetch() statements
    /// </summary>

    public interface IFetchingProvider
    {
        IFetchRequest<TOriginating, TRelated> Fetch<TOriginating, TRelated>(IQueryable<TOriginating> query, Expression<Func<TOriginating, TRelated>> relatedObjectSelector);

        IFetchRequest<TOriginating, TRelated> FetchMany<TOriginating, TRelated>(IQueryable<TOriginating> query, Expression<Func<TOriginating, IEnumerable<TRelated>>> relatedObjectSelector);

        IFetchRequest<TQueried, TRelated> ThenFetch<TQueried, TFetch, TRelated>(IFetchRequest<TQueried, TFetch> query, Expression<Func<TFetch, TRelated>> relatedObjectSelector);

        IFetchRequest<TQueried, TRelated> ThenFetchMany<TQueried, TFetch, TRelated>(IFetchRequest<TQueried, TFetch> query, Expression<Func<TFetch, IEnumerable<TRelated>>> relatedObjectSelector);
    }


public class NhFetchingProvider : IFetchingProvider
    {
        public IFetchRequest<TOriginating, TRelated> Fetch<TOriginating, TRelated>(IQueryable<TOriginating> query, Expression<Func<TOriginating, TRelated>> relatedObjectSelector)
        {
            var fetch = EagerFetchingExtensionMethods.Fetch(query, relatedObjectSelector);
            return new FetchRequest<TOriginating, TRelated>(fetch);
        }

        public IFetchRequest<TOriginating, TRelated> FetchMany<TOriginating, TRelated>(IQueryable<TOriginating> query, Expression<Func<TOriginating, IEnumerable<TRelated>>> relatedObjectSelector)
        {
            var fecth = EagerFetchingExtensionMethods.FetchMany(query, relatedObjectSelector);
            return new FetchRequest<TOriginating, TRelated>(fecth);
        }

        public IFetchRequest<TQueried, TRelated> ThenFetch<TQueried, TFetch, TRelated>(IFetchRequest<TQueried, TFetch> query, Expression<Func<TFetch, TRelated>> relatedObjectSelector)
        {
            var impl = query as FetchRequest<TQueried, TFetch>;
            var fetch = EagerFetchingExtensionMethods.ThenFetch(impl.NhFetchRequest, relatedObjectSelector);
            return new FetchRequest<TQueried, TRelated>(fetch);
        }

        public IFetchRequest<TQueried, TRelated> ThenFetchMany<TQueried, TFetch, TRelated>(IFetchRequest<TQueried, TFetch> query, Expression<Func<TFetch, IEnumerable<TRelated>>> relatedObjectSelector)
        {
            var impl = query as FetchRequest<TQueried, TFetch>;
            var fetch = EagerFetchingExtensionMethods.ThenFetchMany(impl.NhFetchRequest, relatedObjectSelector);
            return new FetchRequest<TQueried, TRelated>(fetch);
        }
}

public static IFetchRequest<TOriginating, TRelated> Fetch<TOriginating, TRelated>(this IQueryable<TOriginating> query, Expression<Func<TOriginating, TRelated>> relatedObjectSelector, Func<IFetchingProvider> FetchingProvider)
        {
            return FetchingProvider().Fetch(query, relatedObjectSelector);
        }