Getting a NotSupportedException: Specified method is not supported. NHibernate C#

Question

I have the following LINQ query:

var coverageQuery = _repository.LogicalEcus
    .Where(ecu => ecu.Alias != null)
    .Select(ecu => ecu.Alias)
    .Where(ecuAlias => searchTerm == "" || ecuAlias.Mnemonic.Contains(searchTerm.ToUpper()))
    .OrderBy(ecuAlias => ecuAlias.Mnemonic)
    .Select(x => x.Mnemonic)
    .Distinct()
    .Select(mnemonic => new Select2Result(mnemonic));

The query is executed successfully but when passing the result to a Select2PagedResult constructor:

var result = new Select2PagedResult(coverageQuery, pageNum, pageSize);

It will fail with the following error:

[NotSupportedException: Specified method is not supported.] NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.GetClassName(IASTNode querySource) +117 NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.Process(IASTNode tree) +188 NHibernate.Hql.Ast.ANTLR.AstPolymorphicProcessor.Process() +51 NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary2 filters, ISessionFactoryImplementor factory) +154 NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary2 enabledFilters) +396 NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow) +149 NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression) +133 NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery) +119 NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) +38 NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) +15 Continental.INDiuM.WebUi.Models.Select2PagedResult..ctor(IQueryable`1 list, Int32 pageNumber, Int32 itemsPerPage) in C:\Projects\INDiuM\WebUi\Models\Select2PagedResult.cs:11 Continental.INDiuM.WebUi.Controllers.CoverageManagementController.GetCoverageAliases(String id, String searchTerm, Int32 pageSize, Int32 pageNum) in C:\Projects\INDiuM\WebUi\Controllers\CoverageManagementController.cs:124 lambda_method(Closure , ControllerBase , Object[] ) +301

The Select2PagedResult's constructor is looking like this:

public Select2PagedResult(IQueryable<Select2Result> list, int pageNumber, int itemsPerPage)
{
    Total = list.Count();
    Results = list
        .Skip((pageNumber - 1) * itemsPerPage)
        .Take(itemsPerPage)
        .ToList();
}

And it will fail exactly at this line, inside Select2PagedResult's constructor:

Removing the .Distinct() from the LINQ query above would solve the problem, the exception will not be thrown again.

Also, if I would change the Select2PagedResult's constructor to accept a List instead of IQueryable would work as well:

public Select2PagedResult(List<Select2Result> list, int pageNumber, int itemsPerPage)

I also tried to rearrange the LINQ query order but no success.

It is like .Count() doesn't exist on NhQueryable.

I didn't found an explanation for this and I really don't understand what it is the problem, especially because it was working a couple months ago...

Any suggestions would really be appreciated.

It seems that NhQueryable does not provide Count() - just for checking: Have you tried to convert to a list first? Eg. Total = list.ToList().Count; — Carsten Gehling
Yep, tried that as well and seems that NhQueryable doesn't have the .ToList() either unfortunately. — Adrian Chiritescu
It is strange because it was working a couple months ago. I don't know what could be changed in the project so that would lead to this failure. Weird also because removing the .Distinct() from the LINQ, it won't fail anymore.. — Adrian Chiritescu
It's failing at Count() because that's where the query is being 'materialised' and executed. There's something in the LINQ query that NH cannot convert into a SQL query. — David Osborne
How does the Distinct() method know which Mnemonics are unique? — David Osborne

David Osborne David Osborne · Accepted Answer · 2018-07-12T16:10:51

If you add AsEnumerable() to each stage of your query and then re-try, you can narrow your focus:

var coverageQuery = 
    _repository.LogicalEcus
        .Where(ecu => ecu.Alias != null)
        .Select(ecu => ecu.Alias)
        .Where(ecuAlias => searchTerm == "" || ecuAlias.Mnemonic.Contains(searchTerm.ToUpper()))
        .OrderBy(ecuAlias => ecuAlias.Mnemonic)
        .Select(x => x.Mnemonic)

        // Materialises and runs query. Rest is processed against objects.
        // Any method that runs after this line but not before can be
        // transformed into SQL equivalent
        .AsEnumerable()    

        .Distinct()
        .Select(mnemonic => new Select2Result(mnemonic));

You can move the call to AsEnumerable up and down the list to find the culprit.

Getting a NotSupportedException: Specified method is not supported. NHibernate C#

1 Answers