Optimize SQL Connection Query for NHibernate

Question

Take a pretty simple domain model. Orders, items, and shipments, where the Order is the root object, and shipping is the root object. I want to see all the items for this order. The request is pretty direct, but I see unwanted behavior with NHibernate.

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;

The results include 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 Submission objects are correct, but the request is loading too much data, since I am only interested in the shipping dates. Order data and line items are immediately discarded and never used. I tried to use lazy loading, as well as every retrieval strategy that I can find on the Internet, but I cannot get it to simply return the main data.

SQL-, ? - :

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 */

SQL- ()

SQL-, , . ORM. NHibernate ?

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

. : (

Answer 2

, LINQ "select shipment", "select shipment.Date"; . , ?

var shipment = = session.Linq() shipment.LineItem.Order == order {Id = shipment.Id, Date = shipment.Date, LineItemId = shipment.LineItem.Id, OrderId = shipment.LineItem.Order.Id};

Answer 3

" " . XML nhibernate mapping, .

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

Orders ( IList, IList).

linq, .

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

Answer 4

You should be able to use lamda expressions and a new object initializer in C #

I have not done this for a while, as I returned to the old school 2.0.

I know that in linq you can do this, whether Nhibernate supports linq it is not sure.

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

The link will open here, This is in C #, but on the java site. This should, we hope, only execute the expression that leaves, and selects the date when the order is equal to the specified order. But it will return you the "Shipping" object, only with the filled date.