Entity Framework 4 simple Compiled Query internal exception on compilation

Question

I want to retrieve one page from a sorted table. I want the sorting and paging to be done on the server. For this I created the following compiled query:

internal static readonly Func<MyEntities, string, int, int, IQueryable<Model.Message>> MessagesPagedSortedByDateQuery =
        CompiledQuery.Compile((MyEntities db, string folderId, int pageSize, int pageIndex) =>
        (
            db.Messages.Where(m => m.FolderId == folderId).OrderBy(m => m.Date).Skip(pageSize * pageIndex).Take(pageSize)
        ));

This seems a very straightforward query to me. However, when I execute it with the following statement:

var messages = MessageCompiledQueries.MessagesPagedSortedByDateQuery(myEntities, folderId, pageSize, pageIndex).ToList();

I get the following exception from source System.Data.Entity:

Count must be a DbConstantExpression or a DbParameterReferenceExpression. Parameter name: count

With this stacktrace:

at System.Data.Common.CommandTrees.ExpressionBuilder.Internal.ArgumentValidation.ValidateSkip(DbExpressionBinding input, IEnumerable'1 sortOrder, DbExpression count) at System.Data.Common.CommandTrees.ExpressionBuilder.DbExpressionBuilder.Skip(DbExpressionBinding input, IEnumerable'1 sortOrder, DbExpression count) at System.Data.Objects.ELinq.ExpressionConverter.OrderByLifter.ApplySortOrderToSkip(DbExpression input, DbSortExpression sort, DbExpression k) at System.Data.Objects.ELinq.ExpressionConverter.OrderByLifter.SortLifter.Skip(DbExpression k) at System.Data.Objects.ELinq.ExpressionConverter.Skip(DbExpressionBinding input, DbExpression skipCount) at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SkipTranslator.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.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.Convert() at System.Data.Objects.ELinq.CompiledELinqQueryState.GetExecutionPlan(Nullable'1 forMergeOption) at System.Data.Objects.ObjectQuery'1.GetResults(Nullable'1 forMergeOption) at System.Data.Objects.ObjectQuery'1.System.Collections.Generic.IEnumerable.GetEnumerator() at System.Collections.Generic.List'1..ctor(IEnumerable'1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable'1 source) at MyApp.Data.Repository.MessageRepository.GetByFolder(String folderId, Int32 pageSize, Int32 pageIndex, String sortField) in C:\Projects\MyApp\MyApp.Data\Repository\MessageRepository.cs:line 40 at MyApp.WebClient.Controllers.FolderController.Messages(GridCommand command, String folderId) in C:\Projects\MyApp\MyApp.WebClient\Controllers\FolderController.cs:line 53 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary'2 parameters) at System.Web.Mvc.ControllerActionInvoker.<>c_DisplayClassd.b_a() at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func'1 continuation)

It looks like the exception occurs when compiling the query for the first time. If I remove the orderby from the query it works just fine. But obviously I would like the ordering and paging to occur on the server, so I don't want to do that after retrieving the full table. Is this a bug in the entity framework? I can't find anything about it on the web. Does anybody know how to work around this? I use the .Net Framework 4 final release with Visual Studio 2010.

Thanks!

Answer 1

See if this fixes it:

internal static readonly Func<MyEntities, string, int, int, int, IQueryable<Model.Message>> 
    MessagesPagedSortedByDateQuery =
        CompiledQuery.Compile((MyEntities db, string folderId, int pageSize, int pageIndex, int skipCount) =>
        (
            db.Messages.Where(m => m.FolderId == folderId).OrderBy(m => m.Date).Skip(skipCount).Take(pageSize)
        ));

Note that I've changed the signature. This is obviously not an ideal solution, but I haven't seen how you're using this in the context of the code. If it gets you around the error, you can tweak it into something more useful.