MySQL EFCore 9: LINQ Contains Array Query Fails to Translate to SQL

When using EF Core with MySQL, the following code

CSHARPCopied
var arr = new long[] { 5, 3, 2 }; 
var results = await _datacontext.ChatMessages
    .Where(x => arr.Contains(x.ChatMessageId))
    .ToListAsync(cancellationToken);

raises the error because the MySQL provider does not support translation of a primitive collection (e.g. long[]) in a Contains call. While SQL Server’s provider is able to handle this pattern by converting it to an SQL IN clause, the MySQL provider isn’t enabled for primitive collection translation by default.

There are a couple of workarounds:

1. Client Evaluation
   You can force the evaluation to occur on the client side (after retrieving the relevant data) by adding AsEnumerable() before the filtering:

   CSHARPCopied
   var results = await _datacontext.ChatMessages
       .AsEnumerable()
       .Where(x => arr.Contains(x.ChatMessageId))
       .ToListAsync(cancellationToken);

   Note: This approach pulls more data into memory and filters on the client, which may not be optimal for larger datasets.

2. Building the Query Differently
   Depending on your scenario, you might consider rewriting the query to dynamically build an SQL command (or use raw SQL via FromSqlInterpolated) that directly uses an IN clause. For example:

   CSHARPCopied
   var ids = string.Join(",", arr);
   var results = await _datacontext.ChatMessages
       .FromSqlInterpolated($"SELECT * FROM ChatMessages WHERE ChatMessageId IN ({ids})")
       .ToListAsync(cancellationToken);

   Caution: When using interpolated raw SQL, ensure you safeguard against SQL injection (here it's safe because you construct the string from a trusted array of longs).

3. Upgrade or Change Provider
   Check if there’s an updated version of the MySQL provider (e.g., Pomelo or Oracle’s MySQL provider) that supports primitive collections translation. Some providers may have added this functionality in later releases.

In summary, while the LINQ Contains syntax works with SQL Server, the MySQL provider requires either a different query pattern or shifting parts of the query evaluation to the client.