EF Core中使用Like筛选带千分位的Decimal类型数据问题

数据库中有带精度的Decimal类型列，需要使用Entity Framework Core的Contains或Like函数执行包含千分位的筛选查询。在SQL Server中常用的实现语法如下：

SELECT * FROM Table WHERE CONVERT(VARCHAR(32), CONVERT(MONEY, Rate), 3) LIKE '%12,%'

尝试将数值类型转换为字符串时出现错误，代码如下：

.Where(x => x.Rate.ToString("N2").Contains($"{searchValue}"))

更新：错误信息 #

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
System.ArgumentNullException: Value cannot be null. (Parameter 'source')

at System.Linq.ThrowHelper.ThrowArgumentNullException(ExceptionArgument argument)
at System.Linq.Enumerable.Skip[TSource](IEnumerable`1 source, Int32 count)
at Game.BackEnd.Controllers.AdaDeyController.GetData(DataTableRequestModel post) in C:\MyGame\Game.BackEnd\Controllers\AdaDeyController.cs:line 261
at lambda_method216(Closure, Object)
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

更新：完整LINQ代码 #

var query = _context.AdaDey
    .Include(x => x.SourceSatu)
    .Include(y => y.TargetSatu)
    .Select(s => new AdaDey()
    {
        Id = s.Id,
        Date = s.StartDate,
        Rate = s.Rate,
        SourceSatu = new SourceSatu()
        {
            Id = s.Id,
            Code = s.SourceSatu!.Code,
            Name = s.SourceSatu.Name
        },
        TargetSatu = new TargetSatu()
        {
            Id = s.Id,
            Code = s.TargetSatu!.Code,
            Name = s.TargetSatu.Name
        }
    })
    .Where(x => (x.Rate).ToString("N2").Contains($"{searchValue}"));

更新：报错代码行（AdaDeyControllers.cs第261行） #

return Json(new
                {
                    draw = post.draw,
                    recordsFiltered = dataResponse?.DataResponse?.Count ?? 0,
                    recordsTotal = totalRecords,
                    data = dataResponse?.DataResponse.Skip(skip).Take(post.length)
                }, opt);

解决方案 #

1. 解决千分位筛选问题 #

EF Core无法将ToString("N2")直接转换为SQL语句，导致查询无法在服务器端执行，甚至引发异常。要实现和SQL中CONVERT(VARCHAR(32), CONVERT(MONEY, Rate), 3)等价的逻辑，有两种可行方式：

方式一：使用参数化原生SQL查询

直接复用你熟悉的SQL语法，通过EF Core的FromSqlInterpolated实现参数化查询，避免SQL注入风险：

var query = _context.AdaDey
    .FromSqlInterpolated($"SELECT * FROM AdaDey WHERE CONVERT(VARCHAR(32), CONVERT(MONEY, Rate), 3) LIKE '%{searchValue}%'")
    .Include(x => x.SourceSatu)
    .Include(y => y.TargetSatu)
    .Select(s => new AdaDey()
    {
        Id = s.Id,
        Date = s.StartDate,
        Rate = s.Rate,
        SourceSatu = new SourceSatu()
        {
            Id = s.SourceSatu.Id,
            Code = s.SourceSatu.Code,
            Name = s.SourceSatu.Name
        },
        TargetSatu = new TargetSatu()
        {
            Id = s.TargetSatu.Id,
            Code = s.TargetSatu.Code,
            Name = s.TargetSatu.Name
        }
    });

方式二：映射SQL Server的FORMAT函数

通过自定义EF Core函数映射SQL Server的FORMAT函数，实现千分位格式化后筛选：
首先在DbContext中定义静态函数：

[DbFunction("FORMAT", "")]
public static string FormatDecimal(decimal value, string format)
{
    throw new NotImplementedException("此函数仅用于EF Core SQL转换，不会在客户端执行");
}

然后在查询中使用：

.Where(x => EF.Functions.Like(YourDbContext.FormatDecimal(x.Rate, "N2"), $"%{searchValue}%"))

2. 解决ArgumentNullException报错 #

报错是因为dataResponse?.DataResponse为null时调用Skip方法导致的，需要在调用前做空值判断，返回空集合替代：

return Json(new
{
    draw = post.draw,
    recordsFiltered = dataResponse?.DataResponse?.Count ?? 0,
    recordsTotal = totalRecords,
    data = dataResponse?.DataResponse?.Skip(skip).Take(post.length) ?? Enumerable.Empty<AdaDey>()
}, opt);

内容的提问来源于stack exchange，提问作者Amigo