NHibernate Specified method is not supported

Question

I'm developed webapp using S#arpLite to build a query get a list from many tables. that using NHibernate version 3.3.1.4000

I got a error from app when it's running time such as

 `NHibernate System.NotSupportedException Specified method is not supported. {Name = "PolymorphicQuerySourceDetector" FullName = "NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector"}

   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.AstPolymorphicProcessor.Process(IASTNode ast, ISessionFactoryImplementor factory)
   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.HQLExpressionQueryPlan.CreateTranslators(String expressionStr, IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.HQLExpressionQueryPlan..ctor(String expressionStr, IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.HQLExpressionQueryPlan..ctor(String expressionStr, IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, 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 EzLife.Tasks.EmployeeCudTasks.GetEmployees(List`1 listSkill, List`1 listDepartment, List`1 listAssignment, String searchTerm, String jobtitle, String tier, String competency, Nullable`1 startDate, Nullable`1 endDate, Int32 effort, Nullable`1 active, Int32 currentPage, Int32 pageSize, Int32 sortId, Int32 sortName, Int32 sortTitle, Int32 sortTier, Int32 sortJoinedDate, Int32 sortDepartment) in d:\Projects\EzLife\_source\Ezlife\app\EzLife.Tasks\EmployeeCudTasks.cs:line 206`

Here is my code

 `public static IQueryable GetEmployeesQ(this IQueryable employees, IQueryable employeeTitles, int currentPage,int pageSize)
        {
           var query = from employee in employees
                        join employeeTitle in employeeTitles
                        on employee.Id equals employeeTitle.Employee.Id
                        select new EmployeeDto()
                        {
                            Id = employee.Id,
                            CustomCode = employee.CustomCode,
                            FirstName = employee.FirstName,
                            LastName = employee.LastName,
                            MiddleName = employee.MiddleName,
                            FullName = string.Empty,
                            JoinedDate = employee.JoinedDate,
                        };
            return query;
        }

public static IQueryable GetEmployeeTitlesQ(this IQueryable employeeTitles)
        {
            return from et1 in employeeTitles
                   join et2 in
                       (
                           from et in employeeTitles
                           orderby et.Employee.Id, et.StartDate
                           group et by et.Employee.Id into etmax
                           select new { Id = etmax.Max(et => et.Id) }
                           ) on et1.Id equals et2.Id
                   select et1;
}`



I call GetEmployeeTitlesQ in GetEmployeesQ as :

'
public IList GetEmployees(int currentPage = 1, int pageSize = 20)
        {

        IList<EmployeeDto> employees = new List<EmployeeDto>();

        IQueryable<EmployeeTitle> employeeTitles = employeeTitleRep.GetAll().GetEmployeeTitlesQ();

        IQueryable<EmployeeDto> employeeDto = employeeRep.GetAll().GetEmployeesQ(
            employeeTitles
            , jobTitles
            , currentPage
            , pageSize);

        try
        {
            employees = employeeDto.ToList();
        }
        catch (Exception ex) {
            var mess = ex.Message.ToString();
        }

        return employees;
    }

' I guess there is a problem from Max() function but I don't why. Is there any way work around to resolve it?

Answer 1

NHibernate cannot translate every LINQ query into SQL. For me it was enough to just rearrange the order of my where and join statements.

Answer 2

I would suggest the following changes in your code, which helped me get over the very same exception:

public static IQueryable GetEmployeeTitlesQ(this IQueryable employeeTitles)
{
    var employeeTitlesEnumerable = employeeTitles.ToArray();
    return from et1 in employeeTitlesEnumerable 
           join et2 in (
               from et in employeeTitlesEnumerable 
               orderby et.Employee.Id, et.StartDate
               group et by et.Employee.Id into etmax
               select new { Id = etmax.Max(et => et.Id) }
           ) on et1.Id equals et2.Id
           select et1;
}

I have added a call to .ToArray() on the queryable. My experience with FluentNHibernate is that sometimes the returned IQueryable is trying to translate the whole LINQ expression to SQL. When the query involves objects that cannot be represented as SQL, the error you see will be thrown. So, my wild guess here is that the Max call is not recognized as something that NHibernate can properly translate.

Using .ToArray() (or if you prefer .ToList()) converts the NHibernate queryable to an instance of IEnumerable which is then processed entirely by the .NET framework. This means that LINQ expressions applied to the IEnumerable will no longer get converted to SQL.

A side effect of the above, is that you won't be able to perform the whole select as an SQL query against the database.

The bottom line (at least for me) is that you should carefully order all database-related operations within the NHibernate layer, and when the query is done, convert it to enumerable to prevent potential further LINQ operations from business logic calls to interfere with the IQueryable's internal specifics.