NHibernate - fetching with futures

Question

I have this Fluent NHibernate mapping:

public LossMap() { Table("losses"); Id(x => x.Id).Column("id"); References(x => x.Policy).Column("pol_id"); HasMany(x => x.Statuses).KeyColumn("loss_id").Cascade.All().Inverse(); HasMany(x => x.Reserves).KeyColumn("loss_id").Cascade.All().Inverse(); HasMany(x => x.Payments).KeyColumn("loss_id").Cascade.All().Inverse(); } public LossPaymentMap() { Table("losspayments"); Id(x => x.Id).Column("id"); Map(x => x.Type).Column("type_id"); References(x => x.Reserve).Column("reserve_id"); } public LossReserveMap() { Table("lossreserves"); Id(x => x.Id).Column("id"); Map(x => x.Type).Column("type_id"); Map(x => x.Status).Column("status_id"); References(x => x.ParentReserve).Column("parent_reserve_id"); } public LossStatusMap() { Table("lossstatuses"); Id(x => x.Id).Column("id"); Map(x => x.Status).Column("status_id"); Map(x => x.ExpirationDate).Column("expirationdate"); References(x => x.Loss).Column("loss_id"); }

To summarize:

Loss has many Payments, Reserves and Statuses
Payment has one Reserve

I am trying to fetch Losses and their payments and reserves (but not statuses) with the following constraints:

Only fetch Losses which have at least one status with "status.Status not in (1,2,7)".
Only fetch Loss.Payments where "loss.Payment.Type = 2 and loss.Payment.Reserve.Status != 4)"
Only fetch Loss.Reserves where Reserve.Status != 3

As I am trying to fetch 2 parallel relations, I have to use multiqueries or futures to avoid Cartesian product (right?), as explained here: http://ayende.com/blog/4367/eagerly-loading-entity-associations-efficiently-with-nhibernate

I came up with this query (in HQL):

int[] statuslist = new int[3] {1, 2, 7}; var losses = session.CreateQuery( "from Loss l left join fetch l.Payments as payment join l.Statuses as status where l.Policy.Product.Id = :tid1 " + "and status.Status not in ( :statuslist1) " + "and payment.Type = 2 and payment.Reserve.Status != 4") .SetParameter("tid1", productid) .SetParameterList("statuslist1", statuslist) .Future<Loss>(); session.CreateQuery( "from Loss l left join fetch l.Reserves as reserve join l.Statuses as status where l.Policy.Product.Id = :tid2 " + "and status.Status not in ( :statuslist2) " + "and reserve.Status != 3 ") .SetParameter("tid2", productid) .SetParameterList("statuslist2", statuslist) .Future<Loss>(); var list = losses.ToList();

However, when executing this query, I get an error: NHibernate.HibernateException: Failed to execute multi query[..SQL query]---> System.ArgumentException: The value "System.Object[]" is not of type "Entities.Loss" and cannot be used in this generic collection.

Any clues what am I doing wrong here ?

When I remove the status constraint, the query works:

var losses = session.CreateQuery( "from Loss l left join fetch l.Payments as payment where l.Policy.Product.Id = :tid1 " + "and payment.Type = 2 and payment.Reserve.Status != 4") .SetParameter("tid1", productid) .Future<Loss>(); session.CreateQuery( "from Loss l left join fetch l.Reserves as reserve where l.Policy.Product.Id = :tid2 " + "and reserve.Status != 3 ") .SetParameter("tid2", productid) .Future<Loss>();

However, the results are not what I want (I need that constraint).

Any advice ?

Oh, and using HQL is not a "must-be", if this is possible using Linq or QueryOver, I've got not problem with that.

Thanks!

Chris Chilvers · Accepted Answer · 2011-10-11 11:35:08Z

You have a join but have not specified which object you want thus you're actually getting back a tuple.

You should specify which entities/properties you want in the select, eg:

select l from Loss l left join fetch l.Payments as payment where l.Policy.Product.Id = :tid1 and payment.Type = 2 and payment.Reserve.Status != 4

You should also note that when using a join you can get multiple results for the same entity, if you only want unique entities you should use Transformers.DistinctRootEntity

For IQuery/ICriteria: .SetResultTransformer(Transformers.DistinctRootEntity)
For QueryOver: .TransformUsing(Transformers.DistinctRootEntity)

Is there a way to do the same sort of projection using the NHibernate LINQ api?
I don't use linq so am unsure, though this seems to be what you want stackoverflow.com/questions/796889/…

Ian Gibson · Accepted Answer · 2011-10-06 11:53:26Z

I don't tend to use HQL so can't really advise on that but I'm not sure if the query above is exactly what you want. Do you want to fetch all losses regardless of the criteria on the child objects? The query above will only return losses that meet the criteria set on the child objects. It looks like you want to return all losses that match the main criteria but filter the child collections on those? By this I mean that given the current query if you have a loss which has a status 2 but has no payments with payment type 2 then the loss entity will not be returned from the query. Instead I think you need to apply the filter on the join so the loss entity is returned from the query but the payment collection is empty. For example something like this for the first query:

int[] values = new int[] { 1,2,3}; var query1 = session.CreateCriteria<Trade>() .CreateAlias("Status", "s").Add(Expression.Not(Expression.In("s.Status", values))) .CreateAlias("Reserves", "r", JoinType.LeftOuterJoin, Expression.Not(Expression.Eq("r.Status", 3)));

The criteria on the Reserves association will be added to the left outer join clause meaning only that relation has the filter applied.

For the second query you need something similar but I'm not sure you can put constraints from another table within the left outer join (Payment.Reserve.Status != 4). For this you could use a subquery. Something like:

DetachedCriteria paymentSubQuery = null; //make a query for getting all payments with type 2 and reserve.Status != 4 var query2 = session.CreateCriteria<Trade>() .CreateAlias("Status", "s").Add(Expression.Not(Expression.In("s.Status", values))) .CreateAlias("Payments", "p", JoinType.LeftOuterJoin).Add(Subqueries.PropertyIn("p.Id", paymentSubQuery));

I haven't actually run these but I think that should roughly give you what you want.

Collectives™ on Stack Overflow

NHibernate - fetching with futures

2 Answers 2

2 Comments

Comments

Hot Network Questions