NHibernate left join with linq

Question

I need to change join in linq which is using NHibernate into left outer join. My code look like this

IQueryable<DimServicePoint> spq =
                            from sp in session.Query<DimServicePoint>()
                            join spm in session.Query<DimServicepointMeter>() on sp.ServicePointKey equals spm.ServicePointKey into gj
                            from subsp in gj.DefaultIfEmpty()
                            where (sp.ServicePointID == servicePointID && sp.DimAccount.AccountKey != 0 && sp.StartDayID <= currentDateId && sp.EndDayID >= currentDateId)
                            select sp;

Now my requirement is join DimServicepointMeter with left join in this query. Equivalent SQL query is :

select * from dw.dim_servicepoint sp
left join dw.dim_servicepoint_meter spm on sp.servicepointkey = spm.servicepointkey
where sp.servicepointid = @ServicePointID  and sp.accountkey != 0
and sp.startdayid <= @currentDateId  and sp.enddayid >= @currentDateId 

I have not work much in NHibenate or linq so don't have much idea how to do left join in NHibernate or linq. Any help is appreciated

Answer 1

Arbitrary left join are not currently (v4.1) supported in linq-to-nhibernate. They translate to a LINQ GroupJoin which throws NotImplementedException when used with linq-to-nhibernate.

As written in the answer linked by David, you may use queryover instead. Plain hql may do too (using theta style joins).

You may instead map your meters on your service points. It would looks like (without your DimAccount property):

public class DimServicePoint
{
    public virtual int ServicePointID { get; set; }
    public virtual int StartDayID { get; set; }
    public virtual int EndDayID { get; set; }
    public virtual int ServicePointKey { get; set; }
    public virtual ISet<DimServicePointMeter> ServicePointMeters { get; set; }
}

public class DimServicePointMeter
{
    public virtual int ServicePointMeterID { get; set; }
    public virtual int ServicePointKey { get; set; }
}

Mapped with:

<class name="DimServicePoint">
    <id name="ServicePointID">
        <generator class="assigned" />
    </id>
    <property name="StartDayID" />
    <property name="EndDayID" />
    <property name="ServicePointKey" />

    <set name="ServicePointMeters" inverse="true" batch-size="20">
        <key column="ServicePointKey" property-ref="ServicePointKey" />
        <one-to-many class="DimServicePointMeter" />
    </set>
</class>
<class name="DimServicePointMeter">
    <id name="ServicePointMeterID">
        <generator class="assigned" />
    </id>
    <property name="ServicePointKey" />
</class>

And then you could get your data with:

var spq = session.Query<DimServicePoint>()
    .Where(sp => sp.ServicePointID == servicePointID && sp.DimAccount.AccountKey != 0 &&
        sp.StartDayID <= currentDateId && sp.EndDayID >= currentDateId);

Accessing .ServicePointMeters on the query results would trigger a lazy-load of meters collections for at most 20 collection of the loaded service points. This is due to batch-size attribute in my proposed mappings. Without it (and without a globally configured batch-size), it would trigger the lazy-load of only one collection at a time, potentially leading to n+1 performance issues.

If you want to eagerly load them instead, just add the fetch:

var spq = session.Query<DimServicePoint>()
    .Where(sp => sp.ServicePointID == servicePointID && sp.DimAccount.AccountKey != 0 &&
        sp.StartDayID <= currentDateId && sp.EndDayID >= currentDateId)
    .FetchMany(sp => sp.ServicePointMeters);

The resulting query will use a left-join.

Notes:
I personally avoid the LINQ sql-like syntax, I favor usage of LINQ extension methods as illustrated in my answer. I find them much more readable.
I favor lazy loading over eager loading with NHibernate, since it can batch load lazy loads. It tends to simplify the code while maintaining good performances, as I have explained here.