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?

try by debugging one query at a time to get the exact problem..with the current code,it is difficult to predict. — Nikita Shrivastava
– Nikita Shrivastava, Commented Sep 29, 2015 at 6:19
Yes, I did it @Nikita, When I use only query, It's ok and return correct data. — Anh Ta
– Anh Ta, Commented Sep 29, 2015 at 6:24

renklus · Accepted Answer · 2016-11-02 13:20:09Z

5

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

answered Nov 2, 2016 at 13:20

renklus

8437 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Psddp Over a year ago

This is not a answer, it is a comment. Plus, by reading this comment, one can conclude that NHibernate is not a serious solution because it not clear what it can do or not; also, writing the same instructions in different syntaxe can make it work or not. Is that correct?

stackh34p · Accepted Answer · 2016-05-10 06:59:17Z

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.

Collectives™ on Stack Overflow

NHibernate Specified method is not supported

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related