SQL Server JSON列搜索及Entity Framework Core调用存储过程报错求助

错误原因 #

你调用FromSqlRaw时指定了映射到User实体，该实体中包含必填的Properties字段，但你编写的存储过程的SELECT查询列表中没有返回Properties列，EF Core在做结果映射时找不到对应字段，就抛出了这个异常。

解决方法 #

方案1：修改存储过程补全返回字段 #

如果需要沿用User实体接收查询结果，只需要在存储过程的SELECT列表中加上Properties字段即可：

alter procedure [dbo].[spSearchUsers]
@term nvarchar(max) AS BEGIN
select Id, 
    Properties, -- 新增返回Properties列
    JSON_VALUE(Properties, '$.FirstName') as FirstName,
    JSON_VALUE(Properties, '$.LastName') as LastName, 
    JSON_VALUE(Properties, '$.Email') as Email,
    JSON_VALUE(Properties, '$.Role') As [Role],
    JSON_VALUE(Properties, '$.ProgramInformation') as ProgramInformation,
    JSON_VALUE(Properties, '$.CertsAndCredentials') as CertsAndCredentials,
    JSON_VALUE(Properties, '$.Phone') as Phone,
    JSON_VALUE(Properties, '$.LogoUrl') as LogoUrl,
    JSON_VALUE(Properties, '$.ProfessionalPic') as ProfessionalPic,
    JSON_VALUE(Properties, '$.Biography') as Biography,
    CreatedAt, UpdatedAt
from Users
where -- 注意：原写法AND优先级高于OR，存在逻辑漏洞，建议加上括号调整优先级
    (CONCAT(JSON_VALUE(Properties, '$.FirstName'),' ',JSON_VALUE(Properties, '$.LastName')) like '%'+ @term +'%' 
    or JSON_VALUE(Properties, '$.Email') like '%'+ @term +'%')
    and JSON_VALUE(Properties, '$.IsActive') = 'true'
    and JSON_VALUE(Properties, '$.IsLockedOut') = 'false'
    and JSON_VALUE(Properties, '$.IsVerified') = 'true' 
END

修改后原来的控制器调用代码不需要改动即可正常运行。

方案2：自定义无键实体接收查询结果 #

因为存储过程返回的是解析后的JSON字段，和原User实体结构不匹配，更规范的做法是新建专门的接收类：

1. 定义查询结果类

// 字段和存储过程返回的列一一对应
public class UserSearchResult
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public int Role { get; set; }
    public string ProgramInformation { get; set; }
    public string CertsAndCredentials { get; set; }
    public string Phone { get; set; }
    public string LogoUrl { get; set; }
    public string ProfessionalPic { get; set; }
    public string Biography { get; set; }
    public DateTime? CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
}

2. 在DbContext中配置无键实体

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<UserSearchResult>().HasNoKey();
}

3. 调用存储过程时用该类型接收结果

var result = await _context.Set<UserSearchResult>()
    .FromSqlRaw("EXEC dbo.spSearchUsers {0}", term)
    .ToListAsync();

额外提示 #

原存储过程的WHERE条件没有加括号，由于SQL中AND优先级高于OR，会导致只要姓名匹配，不管账号是否激活、锁定、验证通过都会被返回，不符合你原本的过滤逻辑，建议按方案1中的写法调整括号优先级。

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