NHibernate select a list of objects with related child objects with QueryOver

Question

I'm having trouble with something that is probably very simple. In my database I have the following tables:

tblOrder
-----------------
Id
OrderStatusId

tblOrderStatus
-----------------
Id
Name

And I have made the following mappings in my project:

[Class(NameType = typeof(Order), Table = "tblOrder")
public class Order {
    [Id(-2, Name = "Id")]
    [Generator(-1, Class = "native")]
    public virtual long Id { get; set; }

    [ManyToOne]
    public virtual OrderStatus Status { get; set; }
}

[Class(NameType = typeof(OrderStatus), Table = "tblOrderStatus")]
public class OrderStatus {
    [Id(-2, Name = "Id")]
    [Generator(-1, Class = "native")]
    public virtual long Id { get; set; }

    [Property]
    public virtual string Name { get; set; }
}

The query should return a IList<OrderSummary>. I want the class OrderSummary to have a property Status where Status is an object with an Id and a Name property. This could be either with a KeyValuePair or of type OrderStatus (whichever is best and works). Fetching the orders is not a problem but adding the OrderStatus as an object with said properties is the part I'm having trouble with. I also need to return the result of the query as JSON to the client.

OrderSummary should look like this:

public class OrderSummary {
    public long Id { get; set; }
    public OrderStatus Status { get; set; }
}

In my first version OrderSummary had separate properties for OrderStatusId and OrderStatusName. This works but I'm trying to avoid these separate properties. I have also tried this with SelectSubQuery but this returns an error because it returns more than one field in a subquery.

----------------------------------- UPDATE -----------------------------

Following Fredy Treboux's advice I changed my query using Eager which result in the following query:

var query = session.QueryOver<OrderStatus>
    .Fetch(o => o.Status).Eager
    .JoinAlias(o => o.Status, () => statusAlias, JoinType.LeftOuterJoin);

The problem is, I found out, is not selecting the data but how to convert the retrieved Status and assign it to OrderSummary.Status? I have tried the following:

OrderSummary orderAlias = null;
query.SelectList(list => list
    .Select(o => o.Id).WithAlias(() => orderAlias.Id)
    .Select(() => statusAlias).WithAlias(() => orderAlias.Status)
).TransformUsing(Transformer.AliasToBean<OrderSummary>());

-------------------------------- ANSWER ----------------------------------

As I said in my last edit, the problem does not seem to be the actual selection of OrderStatus but returning it to the client. So I thought it was my lack of knowledge of NHibernate instead it was as simple as adding the [JsonObject] attribute to the OrderStatus class. How silly of me.

I have changed my query to the following:

Order orderAlias = null;
OrderSummary orderSummary = null;
OrderStatus statusAlias = null;
var query = session.QueryOver<Order>(() => orderAlias)
    .JoinAlias(() => orderAlias.Status, () => statusAlias, JoinType.LeftOuterJoin);

query = query
    .Select(
        Projections.ProjectionList()
            .Add(Projections.Property(() => orderAlias.Id).WithAlias(() => orderSummary.Id))
            .Add(Projections.Property(() => orderAlias.Status).WithAlias(() => orderSummary.Status)
    );
Result = query.TransformUsing(Tranformers.AliasToBean<OrderSummary>())
    .List<OrderSummary>()
    .ToList();

I have looked over the links you posted there have used the solution that is put into a comment there: var query = session.QueryOver<Order>() .SelectList(list => .Select(Projections.Property("OrderStatus").As("OrderStatus")) ); The result seems ok, but the json the is sent to the client the order does contain an OrderStatus property but the property contains metadata only and not the actual values of the properties. — Bunnynut
– Bunnynut, Commented May 24, 2017 at 13:18
Do you mean proxies? In this case try to disable lazyloading for Status property — Roman Koliada
– Roman Koliada, Commented May 24, 2017 at 13:48
Can I ask why are you trying to accomplish it like this instead of just querying the orders (eager fetching the statuses) and building your OrderSummary DTO by code (instead of trying to get NHibernate to map to it for you)? If I'm not missing something that seems like a much simpler option (let me know if you would like an example). — Fredy Treboux
– Fredy Treboux, Commented May 24, 2017 at 23:22
It's purely lack of knowledge. Could you give me an example of how to do that? — Bunnynut
– Bunnynut, Commented May 26, 2017 at 6:51

Roman Koliada · Accepted Answer · 2017-05-24 10:32:55Z

I'm afraid that currently it's not possible. I guess that Nhibernate transformers are not able to construct nested complex properties. You can return list of tuples and then cast it manually to your entity:

OrderStatus statusAlias = null;        

var tuples = Session.QueryOver<Order>()
            .JoinQueryOver(x => x.Status, () => statusAlias)
            .SelectList(list => list
                .Select(x => x.Id)
                .Select(x => statusAlias.Id)
                .Select(x => statusAlias.Name))
            .List<object[]>();

var result = tuples.Select(Convert);

private OrderSummary Convert(object[] item) {
            return new OrderSummary
            {
                Id = (long)item[0],
                OrderStatus = new OrderStatus { Id = (long)item[1], Name = (string)item[2] }
            };
        }

Also if you don't bother about performance much it's possible to fetch a list of you Orders and convert it to OrderSummary. You can do it by simply define casting operator or using some tool like AutoMapper or ExpressMapper.

Fredy Treboux · Accepted Answer · 2017-05-29 23:31:32Z

Sorry I didn't see your comment asking for an example before. I'm going to leave some code explaining the approach I mentioned, although it was already given as an alternative in the other response and I believe it's the easiest way to go (not using transformers at all):

string GetOrderSummaries()
{
   // First, you just query the orders and eager fetch the status.
   // The eager fetch is just to avoid a Select N+1 when traversing the returned list.
   // With that, we make sure we will execute only one query (it will be a join).
   var query = session.QueryOver<Order>()
                      .Fetch(o => o.Status).Eager;

   // This executes your query and creates a list of orders.
   var orders = query.List();

   // We map these orders to DTOs, here I'm doing it manually.
   // Ideally, have one DTO for Order (OrderSummary) and one for OrderStatus (OrderSummaryStatus).
   // As mentioned by the other commenter, you can use (for example) AutoMapper to take care of this for you:
   var orderSummaries = orders.Select(order => new OrderSummary
   {
      Id = order.Id,
      Status = new OrderSummaryStatus
      {
         Id = order.Status.Id,
         Name = order.Status.Name
      }
   }).ToList();

   // Yes, it is true that this implied that we not only materialized the entities, but then went over the list a second time.
   // In most cases I bet this performance implication is negligible (I imagine serializing to Json will possibly be slower than that).
   // And code is more terse and possibly more resilient.

   // We serialize the DTOs to Json with, for example, Json.NET
   var orderSummariesJson = JsonConvert.SerializeObject(orderSummaries);
   return orderSummariesJson;
 }

Useful links:
AutoMapper: http://automapper.org/
Json.NET: http://www.newtonsoft.com/json

Collectives™ on Stack Overflow

NHibernate select a list of objects with related child objects with QueryOver

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related