R – Optimize SQL join query for NHibernate

join;nhibernate

Take a fairly simple domain model Orders, items and shipments where the Order is a root entity and shipment is a root entity. I want to look up all shipments for a given order. The query is pretty straight forward but I'm seeing undesired behavior with NHibernate.

The Model

public class Order
{
    public Order(){ Items = new List<LineItem>(); }
    public virtual int Id { get; private set; }
    public virtual DateTime Created { get; set; }
    public virtual IList<LineItem> Items { get; private set; }
}

public class LineItem
{
    public virtual int Id { get; private set; }
    public virtual int Quantity { get; set; }
    public virtual Order Order { get; set; }
}

public class Shipment
{
    public virtual int Id { get; private set; }
    public virtual DateTime Date { get; set; }
    public virtual LineItem LineItem { get; set; }
}

LINQ

Using NHibernate.Linq with this query:

var shipments = from shipment in session.Linq<Shipment>()
                where shipment.LineItem.Order == order
                select shipment;

Results int the following SQL Query:

SELECT this_.Id            as Id5_2_,
       this_.Date          as Date5_2_,
       this_.LineItem_id   as LineItem3_5_2_,
       lineitem1_.Id       as Id4_0_,
       lineitem1_.Quantity as Quantity4_0_,
       lineitem1_.Order_id as Order3_4_0_,
       order2_.Id          as Id3_1_,
       order2_.Created     as Created3_1_,
       order2_.IsClosed    as IsClosed3_1_
FROM   [Shipment] this_
       left outer join [LineItem] lineitem1_
         on this_.LineItem_id = lineitem1_.Id
       left outer join [Order] order2_
         on lineitem1_.Order_id = order2_.Id
WHERE  lineitem1_.Order_id = 1 /* @p0 */

The resulting Shipment objects are correct, but the query loads far too much data since I'm only interested in the shipment dates. The order and line item data is discarded immediately and never used. I've tried using lazy loading as well as every fetch strategy I can find online but I can't get it to simply return the basic data.

How can I reduce the noise in the SQL query so that it only loads the shipment data and the primary key of the line item to support lazy loading? Something more like this:

SELECT this_.Id            as Id5_2_,
       this_.Date          as Date5_2_,
       this_.LineItem_id   as LineItem3_5_2_,
       lineitem1_.Id       as Id4_0_,
FROM   [Shipment] this_
       inner outer join [LineItem] lineitem1_
         on this_.LineItem_id = lineitem1_.Id
WHERE  lineitem1_.Order_id = 1 /* @p0 */

Custom SQL Query (Update)

Using a custom SQL query like the following results in the desired performance and the correct behavior. However it kinda defeats the purpose of the ORM. Why can't NHibernate produce such a simple query?

Session
    .CreateSQLQuery(
            @"SELECT SH.*, LI.Id FROM Shipment SH
              INNER JOIN LineItem LI ON LI.Id = SH.LineItem_id
              WHERE LI.Order_id = ?" )
    .SetInt32( 0, order.Id )
    .List<Shipment>();

Best Answer

If you're only interested in the dates, rather than ending your LINQ statement with "select shipment;", you can end it with "select shipment.Date;" so that you don't return the full object hierarchy. And if you want a few extra details, you can create an anonymous type?

var shipments = from shipment in session.Linq() where shipment.LineItem.Order == order select new {Id = shipment.Id, Date = shipment.Date, LineItemId = shipment.LineItem.Id, OrderId = shipment.LineItem.Order.Id};

Related Topic