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();
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
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
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
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
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
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.
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
