Hello,
I've got two entities, Token and Contract. A single contract can reference a single token.
The goal of my query is to create a projection of token data and also include how many contracts reference that token.
I've managed to get this working:
Code:
TokenItem tokenDto = null;
Token token = null;
Contract contractAlias = null;
var subquery = QueryOver.Of<Contract>(() => contractAlias)
.Where(() => contractAlias.Token.Id == token.Id)
.ToRowCountQuery();
var tokenItems = session.QueryOver<Token>(() => token);
if (criteria != null)
criteria(tokenItems);
tokenItems = tokenItems.SelectList(list => list
.Select(c => c.Id).WithAlias(() => tokenDto.TokenId)
.Select(c => c.TokenString).WithAlias(() => tokenDto.Name)
.Select(c => c.DiscountPercent).WithAlias(() => tokenDto.Discount)
.Select(c => c.TermExpiry).WithAlias(() => tokenDto.DiscountDate)
.Select(c => c.Expiry).WithAlias(() => tokenDto.ExpiryDate)
.Select(c => c.Terms).WithAlias(() => tokenDto.Terms)
.SelectSubQuery(subquery).WithAlias(() => tokenDto.NumberOfUses)
).TransformUsing(Transformers.AliasToBean<TokenItem>());
return tokenItems.OrderByAlias(() => tokenDto.ExpiryDate)
.Desc
.Skip(skip).Take(pageSize)
.List<TokenItem>();
This does the job and produces this SQL (targeting mysql):
Code:
SELECT
this_.Id as y0_,
this_.TokenString as y1_,
this_.DiscountPercent as y2_,
this_.TermExpiry as y3_,
this_.Expiry as y4_,
this_.Terms as y5_,
(SELECT
count(*) as y0_
FROM
Contract this_0_
WHERE
this_0_.TokenId = this_.Id) as y6_
FROM
Token this_
ORDER BY
y4_ desc limit ?p0;
?p0 = 20 [Type: Int32 (0)]
To my mind this is pretty inefficient and would execute a subquery per token. If i were writing sql I would select from the token table and join on to a subquery from the contract table that grouped by tokenid and selected the rowcount. Try as I might cannot figure out how to express this in nHibernate. To be clear this is like the sql i want:
Code:
SELECT
Token.id, Token.TokenString, Token.DiscountPercent, Token.Termexpiry, Token.Expiry, Token.Terms, Contracts.RowCount
FROM
Token
LEFT OUTER JOIN (SELECT COUNT(1) [RowCount], TokenId FROM Contract GROUP BY TokenId) [Contracts] ON Contracts.TokenId = Token.Id
omitting the limit and ordering. I just hand typed that into this post window so there may be syntax errors. One thread I read was a bit vague but seemed to imply that such a query is not possible in nHibernate. Can anyone confirm that?
Can someone help please? I'm specifically looking for a "QueryOver" solution.
Login
FAQ
Search