CODEWITHSUNDEEP
c#nhibernatefluent-nhibernate
Raymund
Raymund

Reputation: 7892

nHibernate Expression.In throwing an Exception

I have this nHibernate Critera which works fine

var amount = (decimal)Session.CreateCriteria<Transaction>()
    .Add(Expression.Eq("Account.Id", accountId))
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("Account.Id"))
        .Add(Projections.Sum("InvoiceGross"), "total"))
    .SetMaxResults(1)
    .SetResultTransformer(Transformers.AliasToEntityMap)
    .UniqueResult<IDictionary>()["total"];

but when I start to add Expression.In like this 

var amount = (decimal)Session.CreateCriteria<Transaction>()
    .Add(Expression.Eq("Account.Id", accountId))
    .Add(Expression.In("StatusType.Id", statusTypes))
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("Account.Id"))
        .Add(Projections.Sum("InvoiceGross"), "total"))
    .SetMaxResults(1)
    .SetResultTransformer(Transformers.AliasToEntityMap)
    .UniqueResult<IDictionary>()["total"];

Any ideas why is it happening, by the way statusTypes is an int[] type

it starts to throw an exception "Object reference not set to an instance of an object."

This is the SQL Query I want to achieve

SELECT SUM(InvoiceGross) AS total
FROM  Transactions
WHERE (AccountID = @accountId) (statusTypeID IN (1,2,3,4))
GROUP BY AccountID

Upvotes: 0

Views: 1045

Answers (1)

Baz1nga
Baz1nga

Reputation: 15579

The error ie being thrown cos you are not getting an unique result from the database. Can you monitor the generated sql using SQL profiler or NHProf and then run that against the db to see if the query is indeed returning data.

Also before you start using the ResultTransformer just cast it to a simple List and see if you are getting an array list with the Account.Id as the first item of the array and the sum is the second item i.e try 

var amount = (decimal)Session.CreateCriteria<Transaction>()
    .Add(Expression.Eq("Account.Id", accountId))
    .Add(Expression.In("StatusType.Id", statusTypes))
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("Account.Id"))
        .Add(Projections.Sum("InvoiceGross"), "total"))
    .SetMaxResults(1)
    .List();

var sum=amount[0][1];

Upvotes: 2

Related Questions