EF Core - An error occurred while retrieving Mandate reports. System.InvalidOperationException: The LINQ expression 'DbSet<Mandate>()

Question

I am performing query and search filter in ASP.NET Core using Entity Framework.

I have this code:

MandateApprovalStatus - in the db model:

public MandateApprovalStatus MandateApprovalStatus { get; set; }

and in the enum:

 public enum MandateApprovalStatus : byte
        {
            [Description("Pending Approval")]
            PendingApproval = 0,
            Declined = 1,
            Approved = 2
        }

Repository:

    public IQueryable<Mandate> GetAllMandateAsync(PagingFilter filter)
    {
        try
        {
            IQueryable<Mandate> query = _dbContext.Mandates
                        .Include(x => x.MandateDetails)
                        .Include(x => x.MandateApproval)
                        .Include(x => x.Merchant)
                        .Include(x => x.BankBranch);



            // Apply search filtering
            if (!string.IsNullOrEmpty(filter.SearchQuery))
            {
                query = query
                    .Where(m =>
                    m.Merchant.MerchantName.ToLower().Contains(filter.SearchQuery.ToLower()) ||
                    m.ReferenceNumber.ToLower().Contains(filter.SearchQuery.ToLower()) ||
                    m.DrAccountNumber.Contains(filter.SearchQuery) ||
                    m.BankBranch.BranchName.ToLower().Contains(filter.SearchQuery.ToLower()) ||
                    m.Amount.ToString().Contains(filter.SearchQuery) ||
                    m.MandateApprovalStatus.ToString().Contains(filter.SearchQuery.ToLower()));
            }



            // Apply date range filtering
            if (filter.StartDate.HasValue && filter.EndDate.HasValue)
            {
                query = query.Where(m => m.CreatedAt >= filter.StartDate.Value && m.CreatedAt <= filter.EndDate.Value);
            }



            // Apply sorting
            if (!string.IsNullOrEmpty(filter.SortBy))
            {
                switch (filter.SortBy)
                {
                    case "MerchantName":
                        query = filter.IsSortAscending ? query.OrderBy(m => m.Merchant.MerchantName) : query.OrderByDescending(m => m.Merchant.MerchantName);
                        break;
                    case "ReferenceNumber":
                        query = filter.IsSortAscending ? query.OrderBy(m => m.ReferenceNumber) : query.OrderByDescending(m => m.ReferenceNumber);
                        break;
                    case "DrAccountNumber":
                        query = filter.IsSortAscending ? query.OrderBy(m => m.DrAccountNumber) : query.OrderByDescending(m => m.DrAccountNumber);
                        break;
                    case "BranchName":
                        query = filter.IsSortAscending ? query.OrderBy(m => m.BankBranch.BranchName) : query.OrderByDescending(m => m.BankBranch.BranchName);
                        break;
                    case "Amount":
                        query = filter.IsSortAscending ? query.OrderBy(m => m.Amount) : query.OrderByDescending(m => m.Amount);
                        break;
                    case "MandateApprovalStatus":
                        query = filter.IsSortAscending ? query.OrderBy(m => m.MandateApprovalStatus) : query.OrderByDescending(m => m.MandateApprovalStatus);
                        break;
                    case "CreatedAt":
                        query = filter.IsSortAscending ? query.OrderBy(m => m.CreatedAt) : query.OrderByDescending(m => m.CreatedAt);
                        break;
                }
            }
            else
            {
                // Default sorting by CreatedAt in descending order
                query = query.OrderByDescending(m => m.CreatedAt);
            }

            return query;
        }
        catch (Exception ex)
        {
            _logger.Error(ex, "An error occurred while retrieving Mandates.");
            // Handle or log the exception as per your requirement
            throw new Exception("An error occurred while retrieving Mandates.", ex);
        }
    }

Then I applied this in the service:

   public async Task<Response<PageResult<IEnumerable<AdminMandateListDto>>>> GetAllMandateAsync(PagingFilter filter)
    {
        try
        {
            var query = _unitOfWork.AdminMandates.GetAllMandateAsync(filter);
           // var query = _unitOfWork.AdminMandates.GetAllMandateAsync(filter).ToList();



            var totalRecords = await query.CountAsync();
            var item = await query.PaginationAsync<Mandate, AdminMandateListDto>(filter.PageNumber, filter.PageSize, _mapper);



            var pageResult = new PageResult<IEnumerable<AdminMandateListDto>>
            {
                PageItems = item.PageItems,  // Update this line
                CurrentPage = filter.PageNumber,
                PageSize = filter.PageSize,
                NumberOfPages = (int)Math.Ceiling((double)totalRecords / filter.PageSize),
                TotalRecord = totalRecords
            };



            return new Response<PageResult<IEnumerable<AdminMandateListDto>>>()
            {
                StatusCode = (int)HttpStatusCode.OK,
                Successful = true,
                Data = pageResult,
                Message = "All Mandates Retrieved Successfully"
            };
        }
        catch (Exception ex)
        {
            _logger.Error(ex, "An error occurred while retrieving Mandate reports.");
            return new Response<PageResult<IEnumerable<AdminMandateListDto>>>()
            {
                StatusCode = (int)HttpStatusCode.InternalServerError,
                Successful = false,
                Message = "An error occurred while retrieving Mandate reports."
            };
        }
    }

However, I got this error:

[ERR] An error occurred while retrieving Mandate reports. System.InvalidOperationException: The LINQ expression 'DbSet() .Join( inner: DbSet(), outerKeySelector: m => EF.Property<Guid?>(m, "MerchantId"), innerKeySelector: m0 => EF.Property<Guid?>(m0, "Id"), resultSelector: (o, i) => new TransparentIdentifier<Mandate, Merchant>( Outer = o, Inner = i )) .LeftJoin( inner: DbSet(), outerKeySelector: m => EF.Property<Guid?>(m.Outer, "BankBranchId"), innerKeySelector: b => EF.Property<Guid?>(b, "Id"), resultSelector: (o, i) => new TransparentIdentifier<TransparentIdentifier<Mandate, Merchant>, BankBranch>( Outer = o, Inner = i )) .Where(m => m.Outer.Inner.MerchantName.ToLower().Contains(__filter_SearchQuery_0) || m.Outer.Outer.ReferenceNumber.ToLower().Contains(__filter_SearchQuery_0) || m.Outer.Outer.DrAccountNumber.Contains(__filter_SearchQuery_0) || m.Inner.BranchName.ToLower().Contains(__filter_SearchQuery_0) || m.Outer.Outer.Amount.ToString().Contains(__filter_SearchQuery_0) || m.Outer.Outer.MandateApprovalStatus.ToString().Contains(__filter_SearchQuery_0))' could not be translated. Additional information: Translation of method 'object.ToString' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& ) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 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.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken) at AdminMandatesService.GetAllMandateAsync(PagingFilter filter) in:line 83

and this is line 83:

var totalRecords = await query.CountAsync();

How do I resolve this?

What is MandateApprovalStatus? Entity Framework is complaining that you are calling ToString() on this property, and it doesn't know how to translate that to SQL — Andrew Williamson
– Andrew Williamson, Commented Jul 7, 2023 at 0:27
@AndrewWilliamson - in tthe model: public MandateApprovalStatus MandateApprovalStatus { get; set; } and in the enum: public enum MandateApprovalStatus : byte { [Description("Pending Approval")] PendingApproval = 0, Declined = 1, Approved = 2 } — Ayobamilaye
– Ayobamilaye, Commented Jul 7, 2023 at 4:33
@AndrewWilliamson - See the updated code for MandateApprovalStatus — Ayobamilaye
– Ayobamilaye, Commented Jul 7, 2023 at 4:36
Did you read the error? The error complains about ToString() which simply makes no sense in SQL. LINQ queries don't run in the database, they get translated to SQL. The query has a lot of serious problems too, like all those calls to ToLower(). These are not necessary if the database uses case-insensitive collation. They seriously harm performance too, because they prevent the server using any indexes constructed using the stored values. All those ToLower() calls will force the database to scan the entire table for matches — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 7, 2023 at 8:12
If you want to search for everything everywhere, consider using your database's full text search functionality to perform a Google-like search. Don't expect to just search numbers as if they were text though. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 7, 2023 at 8:16

Svyatoslav Danyliv · Accepted Answer · 2023-07-07 08:05:57Z

1

Probably EF Core cannot convert m.MandateApprovalStatus.ToString()

You can replace ToString by conditional operator:

(m.MandateApprovalStatus == MandateApprovalStatus.PendingApproval ? "pending approval"
  : m.MandateApprovalStatus == MandateApprovalStatus.Declined ? "declined"
  : m.MandateApprovalStatus == MandateApprovalStatus.Approved ? "approved"
  : "unknown"
  ).Contains(filter.SearchQuery.ToLower()))

answered Jul 7, 2023 at 8:05

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EF Core - An error occurred while retrieving Mandate reports. System.InvalidOperationException: The LINQ expression 'DbSet<Mandate>()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related