There are many query patterns involving arrays parameters which we don't translate (e.g. see #30425).

Doing this would involve a bit of common infrastructure in EF (e.g. support for table valued functions which aren't mapped to entity types). Then, on SQL Server specifically, we could transform the array to a JSON string on the client, and use OPENJSON to expand it out to a rowset; at that point it functions like a normal table, and we should be able to compose LINQ operators on it as usual (similar to #13617 (comment)).

Note that for collections which are inline in the query (e.g. new[] { 1, 2, 3 }), we can translate to SQL VALUES.

PS Pay special attention to the ordering guarantees of the rowset coming out of unnest; we probably need to add an ORDER BY to preserve it.
PPS npgsql/efcore.pg#2677 tracks doing this on PostgreSQL via the unnest function (without needing a JSON string intermediary)