Hey all, we are currently working on a fincial system using NHibernate
2.0 and would really appreciate some help on some queries we are
trying to implement.
The simplified context of what's going on is that we have deals with
stock items and securities and I need to get the query to return all
deals where the sum total of all the stock items does not match the
sum total of all the securities, on each deal.
So atm i was thinking of using correlated sub queries that will end up
looking something like this:
select [whole bunch of deal fields]
from Deal [and other tables]
where
[whole bunch of deal constraints]
and (select sum(StockItem.Quantity) from StockItem where
StockItem.DealId = Deal.Id))
<> (select sum(Security.Quantity) from Security where Security.DealId
= Deal.Id))
So using NH 2.1 i could add:
.Add(Restrictions.EqProperty(Projections.SubQuery(totalSecurity),
Projections.SubQuery(totalStock)))
where i would use the Projections factory to get an IProjection from a
DetachedCriteria and then just compare them using the equals property
EqProperty.
The detached criteria would look something like this:
DetachedCriteria totalSecurity= DetachedCriteria.For(typeof(Security),
"security")
.SetProjection(Projections.Sum("Quantity"))
.Add(some more relevant restrictions)
.Add(Restrictions.EqProperty("security.Deal.Id",
"deal.Id"));
So the actual query would look something like:
return Session.CreateCriteria(typeof(Deal), "deal")
.CreateAlias(some more aliases for table joins)
.Add(some more restrictions on deal and on table joins
where appropriate)
.Add(Restrictions.EqProperty(Projections.SubQuery(totalSecurity),
Projections.SubQuery(totalStock)))
.List<Deal>();
In NH 2.0 not quite sure if there is another way to do this because i
can't seem to find a way to create an IProjection from a
DetachedCriteria. All i can do is use Subqueries.Eq(0, totalSecurity)
where i compare the detached criteria to an number and can't do
Subqueries.Eq(totalStock, totalSecurity)
So my questions are as follows:
1) Is there a better way to do the actual query, ie. is the use of 2
correlated queries going to seriously impact performance or is there
another preferred method.
2) Is it possible to create IProjection from a detached critera in NH
2.0 so that i don't need to use NH 2.1 alpha.
Any advice would be greatly appreciated.
Thanks
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
