Nhibernate Linq - select most rated items

Question

Maybe the question covers with an existing one, but I still can not find a solution.

I have 2 tables A and B. B has rating - it is very basic rating, so it keeps just reference to user and reference to item in table A. I want to select top rated items from table A.

So I tried this:

var innerQuery = from csr in session.Query<Rating>()
                                 group csr by csr.ItemId into scrg
                                 orderby scrg.Count() descending
                                 select scrg.Key;

            var result = from cs in session.Query<Item>() where innerQuery.Contains(cs.Id) select cs;

            return result.Take(maxToReturn).ToList<Item>();

It is important for me to return List<Item>

But I have an exception:

Action: Index Exception: System.NotImplementedException: Cannot use group by with the ContainsResultOperator result operator.
at NHibernate.Linq.GroupBy.AggregatingGroupByRewriter.FlattenSubQuery(QueryModel queryModel, QueryModel subQueryModel)
at NHibernate.Linq.GroupBy.AggregatingGroupByRewriter.ReWrite(QueryModel queryModel)
at NHibernate.Linq.Visitors.QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root)
at NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitSubQueryExpression(SubQueryExpression expression)
at ............

Any ideas how to do it?

UPDATE:

My Item class is not related with the rating at all, I just count it during displaying the item, because it can be rated by anyone (so should be updated quite often), but I can add relationship if it will help?

Thanks guys,

Can you post Item class? Is it related to Rating class in code? — Ufuk Hacıoğulları, Jan 06 '14 at 22:13
It seems you can't do that all in the DB. You need to actually select the inner query with a ToList or something. — i3arnon, Jan 06 '14 at 22:49

score 2 · Accepted Answer · edited May 23 '17 at 12:15

The error message is actually pretty clear. As we all know for a long time the Linq to Nhibernate provider has some limitations. This seems to be one of those limitations.

Meaning you cannot use .Query for this.

Either, as already mentioned in the comments, get the inner query into memory by calling ToList this should work although it might be slower then running just one query.

 var innerQuery = (from csr in session.Query<Rating>()
                             group csr by csr.ItemId into scrg
                             orderby scrg.Count() descending
                             select scrg.Key).ToList();

Or you could try to get it working with one of the other query techniques nhibernate provides. The criteria API actually seems to support this kind of sub queries within the where clause containing groupings. Have a look here for an example.

Yes, you are right if I use ToList() on the inner query everything works. You are right it is not the best solution, but at least it returns exactly what I need, and it is enough for me right now. — canimbenim, Jan 07 '14 at 19:58

score 1 · Answer 2 · answered Jan 07 '14 at 20:02

My solution for just now is:

            var innerQuery = (from csr in session.Query<Rating>()
                         group csr by csr.Id into items
                         orderby items.Count()
                         select items.Key).ToList();

            var result = (from cs in session.Query<Item>()
                       where innerQuery.Contains(cs.Id)
                       select cs).ToList<Item>();

If I will found any other solution to do it more efficiently I will update it. If you have any better solution which does exactly what I need, please feel free to add ;)

Nhibernate Linq - select most rated items

2 Answers2