Base solution for your next web application
Open Closed

InvalidOperationException when sorting DateTime field #9442


User avatar
0
timmackey created

I have the following code:

MyAppService.cs

        [AbpAuthorize(AppPermissions.Pages_QuestionBanks)]
        public async Task<PagedResultDto<QuestionBankDto>> GetAll(GetAllQuestionBanksInput input)
        {
            List<QuestionBankDto> questionBankList;
            int totalCount;

            try
            {
                using (CurrentUnitOfWork.DisableFilter(AbpDataFilters.MayHaveTenant,
                                                       AbpDataFilters.MustHaveTenant,
                                                       AbpDataFilters.SoftDelete))
                {
                    var filteredQuestionBanks = _questionBankRepository.GetAll()
                        .WhereIf(!string.IsNullOrWhiteSpace(input.Filter), e => false || e.QuestionBankName.Contains(input.Filter));

                    var course_query = _courseRepository.GetAll();
                    var courses = course_query.ToList();

                    var questionBanks = from o in filteredQuestionBanks

                                        join o1 in _userRepository.GetAll() on o.CreatorUserId equals o1.Id into j1
                                        from s1 in j1.DefaultIfEmpty()

                                        join o2 in _courseRepository.GetAll() on o.CourseId equals o2.Id into j2
                                        from s2 in j2.DefaultIfEmpty()

                                        select new QuestionBankDto()
                                        {
                                            CreateTicks = o.CreationTime.Ticks,
                                            CreateTime = o.CreationTime.ToLocalTime().ToString(),
                                            QuestionsCount = o.QuestionsCount,
                                            Flags = o.Roster,
                                            IsSet = (long)o.IsSet_,
                                            QuestionBankName = o.QuestionBankName,
                                            UserName = s1.UserName,
                                            CourseName = s2.CourseName,
                                            Id = o.Id
                                        };

                    var pagedAndFilteredQuestionBanks = questionBanks
                        .OrderBy(input.Sorting ?? "id asc")
                        .PageBy(input)
                        ;

                    questionBankList = await pagedAndFilteredQuestionBanks.ToListAsync();

                    totalCount = questionBankList.Count();
                }

                return new PagedResultDto<QuestionBankDto>(
                    totalCount,
                    questionBankList
                );
            }
            catch (Exception ex)
            {
                ExceptionUtility.LogException(AbpSession.UserId, ex);
                return null;
            }
        }

QuestionBankDto.cs

using Abp.Application.Services.Dto;

namespace ngTTM.TtmDataModel.Dtos
{
    public class QuestionBankDto : EntityDto
    {
        public string UserName { get; set; }

        public string CourseName { get; set; }

        public string QuestionBankName { get; set; }

        public int QuestionsCount { get; set; }

        public string Flags { get; set; }

        public long IsSet { get; set; }

        public string CreateTime { get; set; }

        public long CreateTicks { get; set; }
    }

Where public class QuestionBank : AuditedEntity

The statement questionBankList = await pagedAndFilteredQuestionBanks.ToListAsync(); throws an error when input.Sorting is "createTicks ASC" or "createTicks DESC". All other fields sort without raising an Exception. Exception Detail:

7/30/2020 11:27:42 PM (PST)  
1
Message: 
Exception Type: System.InvalidOperationException
Exception: The LINQ expression 'OrderByDescending<TransparentIdentifier<TransparentIdentifier<QuestionBank, User>, Course>, long>(
    source: LeftJoin<TransparentIdentifier<QuestionBank, User>, Course, int, TransparentIdentifier<TransparentIdentifier<QuestionBank, User>, Course>>(
        outer: LeftJoin<QuestionBank, User, Nullable<long>, TransparentIdentifier<QuestionBank, User>>(
            outer: DbSet<QuestionBank>, 
            inner: Where<User>(
                source: DbSet<User>, 
                predicate: (u) => (Unhandled parameter: __ef_filter__p_0) || !(((ISoftDelete)u).IsDeleted) && (Unhandled parameter: __ef_filter__p_1) || ((IMayHaveTenant)u).TenantId == (Unhandled parameter: __ef_filter__CurrentTenantId_2)), 
            outerKeySelector: (q) => q.CreatorUserId, 
            innerKeySelector: (u) => (Nullable<long>)u.Id, 
            resultSelector: (q, u) => new TransparentIdentifier<QuestionBank, User>(
                Outer = q, 
                Inner = u
            )), 
        inner: Where<Course>(
            source: DbSet<Course>, 
            predicate: (c) => (Unhandled parameter: __ef_filter__p_3) || ((IMayHaveTenant)c).TenantId == (Unhandled parameter: __ef_filter__CurrentTenantId_4)), 
        outerKeySelector: (ti) => ti.Outer.CourseId, 
        innerKeySelector: (c) => c.Id, 
        resultSelector: (ti, c) => new TransparentIdentifier<TransparentIdentifier<QuestionBank, User>, Course>(
            Outer = ti, 
            Inner = c
        )), 
    keySelector: (ti0) => ti0.Outer.Outer.CreationTime.Ticks)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Source: Microsoft.EntityFrameworkCore
Stack Trace: 
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.&lt;ExecuteAsync&gt;b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at ngTTM.TtmDataModel.QuestionBanksAppService.GetAll(GetAllQuestionBanksInput input) in C:\Users\Tim\Documents\__ngTTMv800\aspnet-core\src\ngTTM.Application\TtmDataModel\QuestionBanksAppService.cs:line 708

"CreationTime" field is displayed correctly for all other sort fields.

How can I sort on the "CreationTime" field?


2 Answer(s)
  • User Avatar
    0
    maliming created
    Support Team

    hi

    The Ticks method of datetime could not be translated. You can use Ticks when using automapper to map enitty to dto(CustomDtoMapper), befor that you should use CreationTime to order the entities.

    configuration.CreateMap<AuditLog, AuditLogListDto>()
    	.ForMember(d => d.ExecutionTimeTicks, s => s.MapFrom(x => x.ExecutionTime.Ticks));
    
     keySelector: (ti0) => ti0.Outer.Outer.CreationTime.Ticks)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
    
  • User Avatar
    0
    timmackey created

    Using CreationTime to order entities is the solution. Thank you.