EF Core 6基于客户端角色列表的服务器端查询改造求助

在ASP.NET Core Web应用中，客户端侧存在一个Roles对象列表，每个对象包含三个字符串属性（Property1、Property2、Property3）以及一个存储角色名的字符串列表（Roles）。需要使用EF Core从SQL Server数据库查询Notification实体，并按以下规则过滤：

• 列表中存在任一Roles对象，其三个属性与数据库实体的对应列值匹配
• 该Roles对象的字符串列表包含与数据库中SendToRole列值匹配的字符串

尝试的实现代码如下：

public async Task<List<Notification>> GetNotificationsForUser(List<Roles> roles)
{
    return await Context.Notifications
        .Where(x => roles.Any(r =>
            r.Property1 == x.Property1 &&
            r.Property2 == x.Property2 &&
            r.Property3 == x.Property3 &&
            r.Roles.Any(r => r == x.SendToRole)))
        .ToListAsync();
}

但该LINQ表达式无法转换为SQL，抛出异常：

System.InvalidOperationException: 无法转换LINQ表达式'r => r.Property1 == EntityShaperExpression:
.Notification
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False
.Property1 && r.Property2 == EntityShaperExpression:
.Notification
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False
.Property2 && r.Property3 == EntityShaperExpression:
.Notification
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False
.Property3 && r.Roles
.Any(r => r == EntityShaperExpression:
.Notification
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False
.SendToRole)'。请将查询改写为可转换的形式，或通过调用'AsEnumerable'、'AsAsyncEnumerable'、'ToList'或'ToListAsync'显式切换到客户端评估。

希望修改查询以支持服务器端评估，避免客户端过滤带来的性能瓶颈。

问题根源在于EF Core无法将客户端集合中嵌套的列表（r.Roles）转换为SQL。要实现服务器端过滤，需先将客户端的Roles列表扁平化为简单值类型的组合集合，再基于这个扁平集合构建查询。

步骤1：扁平化客户端角色数据 #

将每个Roles对象拆分为多条记录，每条记录包含三个属性和一个角色名：

var flattenedRoles = roles.SelectMany(r => 
    r.Roles.Select(role => new 
    {
        r.Property1,
        r.Property2,
        r.Property3,
        Role = role
    })
).ToList();

步骤2：基于扁平集合构建查询 #

使用扁平化后的集合与数据库的Notification实体做匹配，EF Core可以将这种简单值类型的匹配转换为SQL的IN子句或JOIN逻辑：

public async Task<List<Notification>> GetNotificationsForUser(List<Roles> roles)
{
    // 扁平化客户端角色数据
    var flattenedRoles = roles.SelectMany(r => 
        r.Roles.Select(role => new 
        {
            r.Property1,
            r.Property2,
            r.Property3,
            Role = role
        })
    ).ToList();

    // 构建服务器端可转换的查询
    return await Context.Notifications
        .Where(x => flattenedRoles.Any(fr => 
            fr.Property1 == x.Property1 &&
            fr.Property2 == x.Property2 &&
            fr.Property3 == x.Property3 &&
            fr.Role == x.SendToRole
        ))
        .ToListAsync();
}

原理说明 #

扁平化后的集合是由普通值类型（字符串）组成的匿名对象列表，EF Core能够识别这种结构，并将其转换为SQL中的WHERE EXISTS或IN条件，从而在数据库端完成过滤，避免将所有Notification数据加载到客户端再过滤的性能问题。

如果需要进一步优化，可以考虑为Notification的Property1、Property2、Property3、SendToRole列创建联合索引，提升查询效率。

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