locked
A possible skip/take operator bug RRS feed

  • Question

  • Using aggregate functions inside the Skip or Take operators seem to throw an exception saying "Count (or limit) must be a DbConstantExpression or a DbParameterReferenceExpression". See the simplified example below. (Where conditions have been removed for simplicity so don't try to make sense out of the query.. it is purely to demonstrate the problem). If I refactor "ctx.Contacts.Count() - 1" into a variable, it works fine. The same expression works if it applies to the main query so the behavior seems inconsistent.


    var c = (from company in ctx.Companies
             select new
             {
                 company,
                 lastContact = ctx.Contacts.OrderBy(contact => contact.ID).Skip(ctx.Contacts.Count() - 1).Take(1)
             }).ToList();

    Thank you,
    Dmitry
    Thursday, June 11, 2009 12:37 AM

Answers

  • Hi *,

    I know, it's a solution for (maybe) bug. But as a workaround, you may consider using OrderByDescending and then directly Take(1). This will produce same result.
    Jiri {x2} Cincura
    Friday, June 12, 2009 1:19 PM
  • Dmitry, Skip can only take either a constant value of a parameter (, which can be done by using vaiables in LINQ). This limitation comes from the fact that DB translation for a Skip operator uses TOP, and TOP can only take either a constant or a parameter value.

    I agree that this is annoying. A workaround will be to compute the value ahead of time and then use in your query.

    var contactCount = ctx.Contacts.Count() - 1;
    var c = (from company in ctx.Companies select new { company, lastContact = ctx.Contacts.OrderBy(contact => contact.ID).Skip(contactCount ).Take(1) }).ToList();
    Hope this helps,
    Sushil.
    Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, June 12, 2009 4:01 PM

All replies

  • Hi *,

    I know, it's a solution for (maybe) bug. But as a workaround, you may consider using OrderByDescending and then directly Take(1). This will produce same result.
    Jiri {x2} Cincura
    Friday, June 12, 2009 1:19 PM
  • Jim, thanks for you reply but the query is was not meant to be taken literally. It's just an example of how a value that is not a constant or came from a separate variable breaks Skip and Take in projections and subqueries. The workaround is assigning the subquery scalar result to a variable and use the variable inside the Skip/Take operation.

    I hope someone can take a look at this as it really seems like a bug.

    Here is a very long stack trace from the exception:

    System.ArgumentException: Limit must be a DbConstantExpression or a DbParameterReferenceExpression.
    Parameter name: limit
       at System.Data.Common.CommandTrees.ExpressionBuilder.Internal.ArgumentValidation.ValidateLimit(DbExpression argument, DbExpression limit)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TakeTranslator.TranslatePagingOperator(ExpressionConverter parent, DbExpression operand, DbExpression count)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.PagingTranslator.TranslateUnary(ExpressionConverter parent, DbExpression operand, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.UnarySequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.NewTranslator.TypedTranslate(ExpressionConverter parent, NewExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
       at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
       at System.Data.Objects.ELinq.ExpressionConverter.Convert()
       at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
       at System.Collections.Generic.HashSet`1.UnionWith(IEnumerable`1 other)
       at System.Collections.Generic.HashSet`1..ctor(IEnumerable`1 collection, IEqualityComparer`1 comparer)
       at Leaf.Tests.ContextQueryTest.test_math_data_functions() in C:\Users\dmitry01\Documents\Visual Studio 10\Projects\Leaf\Tests\DataAccess\ContextQueryTest.cs:line 782

    Thank you,
    Dmitry
    Friday, June 12, 2009 2:35 PM
  • Dmitry, Skip can only take either a constant value of a parameter (, which can be done by using vaiables in LINQ). This limitation comes from the fact that DB translation for a Skip operator uses TOP, and TOP can only take either a constant or a parameter value.

    I agree that this is annoying. A workaround will be to compute the value ahead of time and then use in your query.

    var contactCount = ctx.Contacts.Count() - 1;
    var c = (from company in ctx.Companies select new { company, lastContact = ctx.Contacts.OrderBy(contact => contact.ID).Skip(contactCount ).Take(1) }).ToList();
    Hope this helps,
    Sushil.
    Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, June 12, 2009 4:01 PM
  • Thank you for the answer. What is strange is the following query works because Take/Skip is not a part of the projection or a subquery? This is the part I find inconsistent.

    var contactList = (from c in context.Contacts select c).Take(context.Contacts.Count() - 1).ToList();

    Also, LINQ-to-SQL does not have any problems with the query in the first post. It does generate 2 queries out of it on the database side.
    Friday, June 12, 2009 4:54 PM
  • Dmitry, Skip can only take either a constant value of a parameter (, which can be done by using vaiables in LINQ). This limitation comes from the fact that DB translation for a Skip operator uses TOP, and TOP can only take either a constant or a parameter value.


    Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

    Hi *,

    this is not true in general. There are databases able to use in "top" operator expression. For instance Firebird.
    Jiri {x2} Cincura
    Friday, June 12, 2009 8:25 PM