The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,534 other followers

NHibernate, LINQ, Oracle and the placement of Take: avoid “Specified method is not supported.”

Posted by jpluimers on 2016/03/22

Even though as of 12c R1, Oracle supports a row limiting clause, NHibernate 4.2 with Oracle.DataAccess.dll 2.112.3.0 does not support that.

When you let it generate the SQL for a LINQ Take call to limit the number of results, you get an exception like this (full exception and stack trace are below):

System.NotSupportedException was unhandled by user code
  HResult=-2146233067
  Message=Specified method is not supported.

The place where you Take is important, as this does fail:

        public ResultType[] SelectByFilter(int? foreignKey,
            DateTime? startDateTime,
            DateTime? endDateTime)
        {
            if (!((foreignKey.HasValue) || (startDateTime.HasValue) || (endDateTime.HasValue)))
                throw new ArgumentException("At least one of foreignKey, startDateTime or endDateTime must have a value");

            IQueryable entities = this.repository.GetEntities();

            if (foreignKey.HasValue)
                entities = entities.Where(e => e.foreignKey == foreignKey.Value);
            if (startDateTime.HasValue)
                entities = entities.Where(e => e.CreationDateTime >= startDateTime.Value);
            if (endDateTime.HasValue)
                entities = entities.Where(e => e.CreationDateTime <= endDateTime.Value);
            entities = entities.Take(MaxSelectByFilterCount); // cause of the exception

            IQueryable resultTypes = entities.Select(
                e =>
                    new ResultType
                    {
                        ResultTypeId = e.ResultTypeEntityId,
                        ForeignKey = e.ForeignKey,
                        CreationDateTime = e.CreationDateTime,
                        CreationUserId = e.CreationUserId,
                        UpdateUserId = e.UpdateUserId
                    });

            return resultTypes.ToArray(); // line at which the Exception is fired
        }

But this succeeds:

        public ResultType[] SelectByFilter(int? foreignKey,
            DateTime? startDateTime,
            DateTime? endDateTime)
        {
            if (!((foreignKey.HasValue) || (startDateTime.HasValue) || (endDateTime.HasValue)))
                throw new ArgumentException("At least one of foreignKey, startDateTime or endDateTime must have a value");

            IQueryable entities = this.repository.GetEntities();

            if (foreignKey.HasValue)
                entities = entities.Where(e => e.foreignKey == foreignKey.Value);
            if (startDateTime.HasValue)
                entities = entities.Where(e => e.CreationDateTime >= startDateTime.Value);
            if (endDateTime.HasValue)
                entities = entities.Where(e => e.CreationDateTime <= endDateTime.Value);

            IQueryable resultTypes = entities.Select(
                e =>
                    new ResultType
                    {
                        ResultTypeId = e.ResultTypeEntityId,
                        ForeignKey = e.ForeignKey,
                        CreationDateTime = e.CreationDateTime,
                        CreationUserId = e.CreationUserId,
                        UpdateUserId = e.UpdateUserId
                    });

            resultTypes = resultTypes.Take(MaxSelectByFilterCount); // put Take here to let LINQ bypass NHibernate
            return resultTypes.ToArray(); // line at which the Exception is fired
        }

The workaround generates an Oracle query that is not limit the result set, but LINQ only fetches MaxSelectByFilterCount entries (in my case 10k). Not as optimal as it could be, but good enough.

–jeroen

System.NotSupportedException was unhandled by user code
  HResult=-2146233067
  Message=Specified method is not supported.
  Source=NHibernate
  StackTrace:
       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.Linq.Buffer`1..ctor(IEnumerable`1 source)
       at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: