英文:
EF Core - An error occurred while retrieving Mandate reports. System.InvalidOperationException: The LINQ expression 'DbSet<Mandate>()
问题
我有以下代码：
MandateApprovalStatus - 在数据库模型中：
public MandateApprovalStatus MandateApprovalStatus { get; set; }
和在枚举中：
public enum MandateApprovalStatus : byte
{
[Description("Pending Approval")]
PendingApproval = 0,
Declined = 1,
Approved = 2
}
存储库：
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);
// 应用搜索过滤
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()));
}
// 应用日期范围过滤
if (filter.StartDate.HasValue && filter.EndDate.HasValue)
{
query = query.Where(m => m.CreatedAt >= filter.StartDate.Value && m.CreatedAt <= filter.EndDate.Value);
}
// 应用排序
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
{
// 默认按CreatedAt降序排序
query = query.OrderByDescending(m => m.CreatedAt);
}
return query;
}
catch (Exception ex)
{
_logger.Error(ex, "检索代理时发生错误。");
// 根据您的需求处理或记录异常
throw new Exception("检索代理时发生错误。", ex);
}
}
然后我将其应用在服务中：
public async Task<Response<PageResult<IEnumerable<AdminMandateListDto>>>> GetAllMandateAsync(PagingFilter filter)
{
try
{
var query = _unitOfWork.AdminMandates.GetAllMandateAsync(filter);
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, // 更新此行
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 = "成功检索到所有代理"
};
}
catch (Exception ex)
{
_logger.Error(ex, "检索代理报告时发生错误。");
return new Response<PageResult<IEnumerable<AdminMandateListDto>>>()
{
StatusCode = (int)HttpStatusCode.InternalServerError,
Successful = false,
Message = "检索代理报告时发生错误。"
};
}
}
但是，我遇到了这个错误：
[ERR] 检索代理报告时发生错误。
System.InvalidOperationException: LINQ 表达式 'DbSet<Mandate>()
.Join(
inner: DbSet<Merchant>(),
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<BankBranch>(),
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))' 无法被转换。附加信息: 方法 'object.ToString' 的转换失败。如果此方法可以映射到您的自定义函数，请参阅 https://go.microsoft.com/fwlink/?linkid=2132413 以获取更多信息。要么以可翻译的形式重写查询，要么通过在 'AsEnumerable'、'AsAsyncEnumerable'、'ToList' 或 'ToListAsync' 中插入对 'AsEnumerable'、'AsAsyncEnumerable'、'ToList' 或 'ToListAsync' 的调用，明确切换到客户端评估。请参阅 https://go.microsoft
<details>
<summary>英文:</summary>
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<Mandate>()
.Join(
inner: DbSet<Merchant>(),
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<BankBranch>(),
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.<VisitMethodCall>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_0`1.<ExecuteAsync>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](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.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 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?
</details>
# 答案1
**得分**: 1
可能 EF Core 无法将 `m.MandateApprovalStatus.ToString()` 转换。
您可以通过条件运算符替换 `ToString`：
```cs
(m.MandateApprovalStatus == MandateApprovalStatus.PendingApproval ? "pending approval"
: m.MandateApprovalStatus == MandateApprovalStatus.Declined ? "declined"
: m.MandateApprovalStatus == MandateApprovalStatus.Approved ? "approved"
: "unknown"
).Contains(filter.SearchQuery.ToLower()))
英文:
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()))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论