Optimize SQL join query for NHibernate

Question

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>();

Answer 1

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};

Answer 2

You could add a many-to-many relationship to your domain model Order class. If you were using an nhibernate mapping xml file, this could be achieved by adding the following to the Order mapping.

<bag name="Shipments" table="LineItem" lazy="true">
    <key column="id"/>
    <many-to-many class="Shipment" column="lineitem_id" />
</bag>

You'd have to add a Shipments property to your Orders class (should be of type IList. otherwise you might need the non-generic IList interface).

You can then query the Shipments property in your linq, which should result in a cleaner join.

var shipments = from shipment in order.Shipments select shipment.Date;

Answer 3

You should be able to use lamda expressions and the new object initializer within C#

I haven't done this for a while as I've been back in old school 2.0.

I know in linq you can do this, whether or not Nhibernate linq supports this I'm not sure.

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

Check out this link here, It is in c# but on a java site. This should hopefully only execute a statement which goes away and selects the Date where the order is equal to the specified order. But it will return you a Shipment object, with only the date populated.

Answer 4

Based on further research and the posts here. The answer is you can't :(