"Array contains" translates to "where id in (..., ...)" instead of "id = any(array)"

[sep2]

@roji Please see repro here: https://github.com/sep2/PgArrayTest

Related issue: #1794 (comment)

[roji]

Can you please try taking ther query out of the function tryGet? That may have an effect on it.

Also, in general a C# would be more helpful, I'm not an F# expert, and it's important to isolate whether there's an F#-specific issue here or not.

[sep2]

Updated F# version to do the query in main, the result is same. Also Added a C# version, which has the correct behavior. So this seems like a F# specific issue?

Edit: I also tested int[] instead of Guid[], same result.

[roji]

OK, I'll try to investigate this; it's possible F# causes a different expression tree structure to get emitted here, which isn't recognized and properly translated by Npgsql.

[roji]

@sep2 this is a general issue with how F# generates its expression trees - I've opened dotnet/fsharp#12841 to discuss this. It's definitely not specific to "array contains", but to any sort of parameter embedded in an EF Core query (or any other LINQ provider).

Am going to close this here, and let's see how the discussion goes on the F# side.

[sep2]

@roji appreciate your time for investigating this. While this is a F# issue, I would like to know is there any way to work around this
for the time being?

[roji]

Not that I'm aware of... F# simply doesn't emit the "closure" expression tree shape that EF Core detects in order to do parameterization. A workaround may come out of the F# issue though.

[sep2]

Finally found a workaround, just leave it here for others:

// wrap anything in a Some(...)
let ints = [| 1; 2 |] |> Some

// this will generate the correct behavior
db.Events.Where(fun x -> ints.Value.Contains(x.Id)).ToList()

info: 3/15/2022 18:32:55.869 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand DbCommand (16ms) [Parameters=[@__Value_0={ '1', '2' } (DbType = Object)], CommandType='Text', CommandTimeout='30']
      SELECT e."Id"
      FROM "Events" AS e
      WHERE e."Id" = ANY (@__Value_0)

[roji]

@sep2 can you please post that workaround in dotnet/fsharp#12841?

[jack775544]

In case anyone comes across this from the C# side of things I was running into something similar but in C# instead and was able to restructure my code a small amount to get it to work. I thought I would document my notes here in case someone else comes across this issue like I did.

I had something like this

IEnumerable<Guid> keys;

// Some code ...

await _dbContext.MyTable.Where(x => keys.Contains(x.Id)).ToListAsync()

This was causing the SQL query to embed individual ids into the query. Changing it slightly to this made it parametrize correctly instead.

IEnumerable<Guid> keys;

// Some code ...

var keyList = keys.ToList();
await _dbContext.MyTable.Where(x => keyList.Contains(x.Id)).ToListAsync()

So providing a list type instead of an IEnumerable type for a contains query is one way to get it to work correctly.