MySql EFCore 9 Contains in array issue

Question

I am trying to do a contains list linq sql query to a MySql database and the query cannot be translated to SQL. This same query works when using the SQLServer.

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

where ChatMessage is (relevant properties)

public class ChatMessage 
{
    public long ChatMessageId { get;set; }
}

Error returned:

System.InvalidOperationException: 'The LINQ expression '@__arr_0' could not be translated. Additional information: Primitive collections support has not been enabled. 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'.

Shouldnt the above work, or is there a way to get an 'IN' SQL generated in Linq other than 'contains' for MySql?

Answer 1

The comment from Mark G solved the issue. Original Link Thanks. Just pasting in here in case the link ever disappears.

Add the following to your dbcontext configuration

sqloptions.EnablePrimitiveCollectionsSupport(true);
sqloptions.TranslateParameterizedCollectionsToConstants();

e.g.

 services.AddDbContextPool<DatabaseUnitOfWork>(options => options
 .UseMySql(connectionstring, new MySqlServerVersion(new Version(8, 0, 21)),
 sqloptions =>
 {
     sqloptions.EnablePrimitiveCollectionsSupport(true);
     sqloptions.TranslateParameterizedCollectionsToConstants();
 });