ASP.NET Core Web API - System.InvalidOperationException: The LINQ expression 'DbSet<Mandate&-CodePudding

In ASP.NET Core-6 Web API Project I have this code:

private async Task<int> GetTotalCustomers()
{
    var userName = _currentUserService.UserName;
    var merchantId = _dbContext.Merchants.Where(u => u.User.UserName == userName).Select(m => m.Id).FirstOrDefault();
    var customersList = await _dbContext.Mandates.Where(x => x.MerchantId == merchantId).GroupBy(x => x.DrAccountNumber).ToListAsync();
    return customersList.Count();
}

I I got this error:

System.InvalidOperationException: The LINQ expression 'DbSet() .Where(x => x.MerchantId == __merchantId_0) .GroupBy(x => x.DrAccountNumber)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

As stated in the error, already I have ToListAsync

How do I get this resolved?

Thanks

CodePudding user response：

Instead of executing two queries, everything can be written in one query, which will be more effective.

private Task<int> GetTotalCustomers()
{
    var userName = _currentUserService.UserName;

    var query =
        from m in _dbContext.Merchants.Where(m => m.User.UserName == userName)
        from mandate in _dbContext.Mandates.Where(mandate => mandate.MerchantId == m.Id)
        select x.DrAccountNumber;

    return query.Distinct().CountAsync();
}