NHibernate Linq Query with Projection and Count error

Question

I have the following query:

var list = repositoy.Query<MyClass>.Select(domain => new MyDto()
{ 
    Id = domain.Id,
    StringComma = string.Join(",", domain.MyList.Select(y => y.Name))
});

That works great:

list.ToList();

But if I try to get the Count I got an exception:

list.Count();

Exception

NHibernate.Hql.Ast.ANTLR.QuerySyntaxException

A recognition error occurred. [.Count[MyDto](.Select[MyClass,MyDto](NHibernate.Linq.NhQueryable`1[MyClass], Quote((domain, ) => (new MyDto()domain.Iddomain.Name.Join(p1, .Select[MyListClass,System.String](domain.MyList, (y, ) => (y.Name), ), ))), ), )]

Any idea how to fix that without using ToList ?

score 2 · Accepted Answer · answered Mar 26 '15 at 10:36

The point is, that we should NOT call Count() over projection. So this will work

var query = repositoy.Query<MyClass>;
var list = query.Select(domain => new MyDto()
{ 
    Id = domain.Id,
    StringComma = string.Join(",", domain.MyList.Select(y => y.Name))
});

var count = query.Count();

When we use ICriteria query, the proper syntax would be

var criteria = ... // criteria, with WHERE, SELECT, ORDER BY...

// HERE cleaned up, just to contain WHERE clause
var totalCountCriteria = CriteriaTransformer.TransformToRowCount(criteria);

So, for Count - use the most simple query, i.e. containing the same JOINs and WHERE part

Thanks... It sounds like a bug for me. – Paul Mar 26 '15 at 12:43 — Paul, Mar 26 '15 at 12:43

score 1 · Answer 2 · answered Mar 27 '15 at 03:35

If you really don't need the results, but only the count, then you shouldn't even bother writing the .Select() clause. Radim's answer as posted is a good way to both get the results and the count, but if your database supports it, use future queries to execute both in the same roundtrip to the database:

var query = repository.Query<MyClass>;
var list = query.Select(domain => new MyDto()
{ 
    Id = domain.Id,
    StringComma = string.Join(",", domain.MyList.Select(y => y.Name))
}).ToFuture();

var countFuture = query.Count().ToFutureValue();

int actualCount = countFuture.Value; //queries are actually executed here

Note that there in NH prior to 3.3.3, this would still execute two round-trips (see https://nhibernate.jira.com/browse/NH-3184), but it would work, and if you ever upgrade NH, you get a (minor) performance boost.

NHibernate Linq Query with Projection and Count error

2 Answers2

Linked