SELECT t.BAmount, t.FAmount, acc.SettlementCurrencyID
FROM [dbo].[TransactionDetail] t
INNER JOIN Acquirer a ON a.ID = t.AcquirerID
INNER JOIN AcquirerCard ac ON ac.AcquirerID = a.ID and ac.CardID = t.CardID
INNER JOIN AcquirerCardCurrency acc ON acc.AcquirerCardID = ac.ID and acc.FCurrencyID = t.FCurrencyID

This is my native sql to group base on SettlementCyrrencyID.

I know how to do Criteria with Grouping. But my problem is that I dont know how to join TransactionDetail to AcquirerCardCurrency table.

How can I create NHibernate Criteria with Grouping?

Please let me know..

Thanks you so much.

If you've mapped all the associations, it would look like this:

session.CreateCriteria(typeof(TransactionDetail), "t")
.CreateAlias("t.Acquirer", "a")
.CreateAlias("a.AcquirerCard", ac)
.CreateAlias("ac.AcquirerCard", acc)
.Add(Expression.PropertyEqual("ac.CardId", "t.CardId")
.Add(Expression.PropertyEqual("acc.FCurrencyID", "t.FCurrencyID")


Remeber that you need classnames and propertynames here.