如何在ApplicationDbContext中对逗号分隔角色串应用全局筛选？

问题背景 #

菜单实体MainMenu定义如下：

public class MainMenu
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string? Roles { get; set; } 
}

示例数据：

需求：

• 若Roles字段不为空，筛选出该字段包含的角色存在于当前用户角色列表myRoles（示例值：["Admin","Other"]）中的记录
• 若Roles为NULL，所有用户都能看到该菜单

此前尝试使用string.Split、未映射属性等方式均因EF Core无法翻译表达式报错，仅能匹配单个角色的筛选方式不符合需求。

可行解决方案 #

方案一：利用EF.Functions.Like实现精准模糊匹配 #

通过构造匹配格式，处理逗号分隔的角色字符串，同时覆盖Roles为NULL的场景：

public List<string> myRoles { get; set; }

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    
    builder.Entity<MainMenu>().HasQueryFilter(p => 
        // 处理Roles为NULL的情况
        p.Roles == null 
        || 
        // 遍历用户角色，匹配任意一个出现在Roles中的情况
        myRoles.Any(role => 
            (p.Roles == role) 
            || p.Roles.StartsWith($"{role},") 
            || p.Roles.EndsWith($",{role}") 
            || p.Roles.Contains($",{role},")
        )
    );
}

说明：

• 直接通过字符串首尾、包含的判断逻辑，避免LIKE可能带来的部分匹配问题（比如避免把AdminX识别为包含Admin的角色）
• EF Core可直接将该表达式翻译为SQL执行

方案二：映射数据库标量函数（以SQL Server为例） #

1. 在数据库中创建拆分字符串的标量函数：

CREATE FUNCTION dbo.SplitString(@input NVARCHAR(MAX), @separator NVARCHAR(10))
RETURNS @result TABLE (Value NVARCHAR(MAX))
AS
BEGIN
    IF @input IS NULL RETURN
    
    DECLARE @start INT = 1, @end INT
    WHILE CHARINDEX(@separator, @input, @start) > 0
    BEGIN
        SET @end = CHARINDEX(@separator, @input, @start)
        INSERT INTO @result VALUES(SUBSTRING(@input, @start, @end - @start))
        SET @start = @end + LEN(@separator)
    END
    INSERT INTO @result VALUES(SUBSTRING(@input, @start, LEN(@input) - @start + 1))
    RETURN
END

2. 在EF Core中映射该函数并配置筛选：

public class ApplicationDbContext : DbContext
{
    public List<string> myRoles { get; set; }

    [DbFunction("SplitString", Schema = "dbo")]
    public IQueryable<string> SplitString(string input, string separator)
    {
        throw new NotImplementedException("该函数仅用于EF Core查询翻译");
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        
        builder.Entity<MainMenu>().HasQueryFilter(p => 
            p.Roles == null 
            || 
            SplitString(p.Roles, ",").Any(role => myRoles.Contains(role))
        );
    }
}

说明：

• 该方式依赖数据库支持自定义函数，匹配逻辑更精准，适合需要严格拆分逗号分隔字符串的场景

方案三：调整实体关系设计（推荐长期方案） #

如果业务允许，将逗号分隔的Roles字段改为多对多关系，符合关系型数据库设计规范：

1. 添加角色关联实体：

public class Role
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<MainMenuRole> MainMenuRoles { get; set; }
}

public class MainMenuRole
{
    public int MainMenuId { get; set; }
    public MainMenu MainMenu { get; set; }
    public int RoleId { get; set; }
    public Role Role { get; set; }
}

2. 修改MainMenu实体：

public class MainMenu
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<MainMenuRole> MainMenuRoles { get; set; }
}

3. 配置全局筛选：

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    
    builder.Entity<MainMenuRole>()
        .HasKey(mr => new { mr.MainMenuId, mr.RoleId });
    
    builder.Entity<MainMenu>().HasQueryFilter(p => 
        // 无关联角色的菜单（对应原Roles为NULL的情况）
        !p.MainMenuRoles.Any() 
        || 
        p.MainMenuRoles.Any(mr => myRoles.Contains(mr.Role.Name))
    );
}

说明：

• 该方案彻底避免字符串拆分的弊端，查询性能、扩展性和维护性均更优

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