Entity Framework Core : filter by 3 fields

Question

I'm trying to query some subscriptions from the database using Entity Framework Core. A subscription is equal to another subscription if the userId, the NotificationType and the Channel match.

I'm attempting it like this:

// Create a list of anonymous objects that combine the three fields for comparison
var subscriptionKeys = subscriptions
    .Select(s => new { s.UserId, s.NotificationType, s.Channel })
    .ToList();
       
// Fetch all existing subscriptions where the combination of UserId,  NotificationType, and Channel match
var existingSubscriptions = await this.context.Subscriptions
    .Where(s => subscriptionKeys.Contains(new { s.UserId, s.NotificationType, s.Channel })).ToListAsync();

But I'm getting this exception:

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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Source=Microsoft.EntityFrameworkCore

StackTrace:
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)

How can I do this type of query? Btw the subscriptions enumerable and the DbSet Subscriptions are not the same type of entity. One is a message the other one the actual entity saved in the database.

Could I do something like mapping one to the other one and just use Contains?

Answer 1

As I tested, you are only left with FromSqlRaw:

var whereClause = string.Join(
    " OR ",
    subscriptions.Select(s => $"(UserId={s.UserId} AND NotificationType='{s.NotificationType}' AND Channel='{s.Channel}')"));

// Fetch all existing subscriptions where the combination of UserId, NotificationType, and Channel match
var existingSubscriptions = await this.context.Subscriptions
    .FromSqlRaw(" SELECT * FROM Subscriptions WHERE " + whereClause)
    .ToListAsync();

Of course, be mindful of SQL injection and sanitize your string appropriately.