NHibernate Linq where clause: value in collection

Topics: General, Writing modules
Jul 25, 2014 at 6:17 PM
Basically I have this:
var viewers = _voteRepository.Table.Where(c => records.Any(r => r == c.ContentItemRecord) && c.Dimension == "Detail").Select(x => x.Username).Distinct().ToList().Count;
records is a list of ContentItemRecords. However, I get an error:
An unhandled exception has occurred and the request was terminated. Please refresh the page. If the error persists, go back
Specified method is not supported.
System.NotSupportedException: Specified method is not supported. at NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.GetClassName(IASTNode querySource) at NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.Process(IASTNode tree) at NHibernate.Hql.Ast.ANTLR.AstPolymorphicProcessor.Process() at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(String queryIdentifier, IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters) at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow) at NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression) at NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery) at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) at Remotion.Linq.QueryableBase`1.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at Mod.Statistics.Controllers.TownhallController.Index() at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass13.<InvokeActionMethodWithFilters>b__10() at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass13.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation) at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass13.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
Apparently the nh provider doesn't know about the variable "records" in the .Any(...) clause and hence, blows up. So I can write this:
var viewers = _voteRepository.Table.ToList().Where(c => records.Any(r => r == c.ContentItemRecord) && c.Dimension == "Detail").Select(x => x.Username).Distinct().ToList().Count;
which works but is far from optimal... Do I need to write hql/sql for this query to be relatively decent in performance?
Jul 28, 2014 at 11:17 AM
I replaced the .Any with a .Contains (thanks to @jon_x on jabbr) and it works fine. I also replaced the .Table.Where(..) with .Fetch(..), this allowed me to change .Distinct().ToList().Count to .Distinct().Count(), which has given another small perf boost