Entity Framework Core三字段匹配查询报错，如何实现该过滤逻辑？

我尝试用Entity Framework Core从数据库查询订阅数据，当两条订阅的UserId、NotificationType和Channel字段完全匹配时，视为相同订阅。我创建了包含这三个字段的匿名对象列表，用Contains方法查询，代码如下：

// 创建用于比较的匿名对象列表，包含三个字段
var subscriptionKeys = subscriptions
    .Select(s => new { s.UserId, s.NotificationType, s.Channel })
    .ToList();
       
// 查询数据库中UserId、NotificationType和Channel组合匹配的订阅
var existingSubscriptions = await this.context.Subscriptions
    .Where(s => subscriptionKeys.Contains(new { s.UserId, s.NotificationType, s.Channel })).ToListAsync();

但运行时抛出异常：

System.InvalidOperationException HResult=0x80131509
Message=The LINQ expression '__subscriptionKeys_0.Contains(new { UserId = StructuralTypeShaperExpression: Micro.NotificationService.Models.Subscription ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False .UserId, NotificationType = StructuralTypeShaperExpression: Micro.NotificationService.Models.Subscription ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False .NotificationType, Channel = StructuralTypeShaperExpression: Micro.NotificationService.Models.Subscription ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False .Channel })' could not be translated. 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'.
Source=Microsoft.EntityFrameworkCore
StackTrace:
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)

补充说明：输入的subscriptions枚举和DbSet<Subscriptions>不是同一实体类型，一个是消息实体，另一个是数据库存储的实体。请问该如何实现这类查询？能否通过映射实体后使用Contains方法？

EF Core无法将匿名对象的Contains操作翻译为SQL，可通过以下几种方式解决：

方法1：使用元组替代匿名对象 #

EF Core支持值元组的Contains翻译，直接替换匿名对象即可：

// 将消息实体映射为元组列表
var subscriptionTuples = subscriptions
    .Select(s => (s.UserId, s.NotificationType, s.Channel))
    .ToList();

// 查询匹配的数据库订阅
var existingSubscriptions = await context.Subscriptions
    .Where(s => subscriptionTuples.Contains((s.UserId, s.NotificationType, s.Channel)))
    .ToListAsync();

方法2：拼接OR条件（适合小数据量） #

如果subscriptions数量不多，可逐个拼接匹配条件：

var query = context.Subscriptions.AsQueryable();

foreach (var sub in subscriptions)
{
    var userId = sub.UserId;
    var type = sub.NotificationType;
    var channel = sub.Channel;
    query = query.Where(s => s.UserId == userId && s.NotificationType == type && s.Channel == channel);
}

var existingSubscriptions = await query.ToListAsync();

注意：数据量大时会生成冗长SQL，可能影响性能。

方法3：映射为统一键类型再查询 #

先定义一个包含三个匹配字段的类（属性类型、名称需与数据库实体一致）：

public class SubscriptionKey
{
    public Guid UserId { get; set; }
    public NotificationType NotificationType { get; set; }
    public ChannelType Channel { get; set; }
}

再转换消息实体并查询：

var subscriptionKeys = subscriptions
    .Select(s => new SubscriptionKey 
    { 
        UserId = s.UserId, 
        NotificationType = s.NotificationType, 
        Channel = s.Channel 
    })
    .ToList();

var existingSubscriptions = await context.Subscriptions
    .Where(s => subscriptionKeys.Any(k => 
        k.UserId == s.UserId && 
        k.NotificationType == s.NotificationType && 
        k.Channel == s.Channel))
    .ToListAsync();

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