Use ExecuteUpdateAsync to remove array item

[s-horbach]

Hi all 👋

9.0 Release introduced one more great feature of translating array.Where(i => i != x) to array_remove(array, x) and i tried to use that, but unfortunately without any success.

So i'd really appreciate any help 🙏

My case:

Table schema:

UserId: uuid	Items: text[]
ce8b8d26-1575-4f32-8226-e71f75bbc1f0	{Item1,Item2}

c#:

await dbContext
    .UserItems
    .Where(x => x.UserId == userId) // filter by UserId
    .Where(x => x.Items.Where(item => item == itemToDelete).Any()) // make sure it has an item we'd like to delete
    .ExecuteUpdateAsync(setter => setter.SetProperty(
        prop => prop.Items,
        value => value.Items.Where(item => item != itemToDelete)));

Actual result: The LINQ expression 'item => item != __itemToDelete_1' could not be translated.

Expected result: i was expecting EF to generate something like this:

UPDATE "userItems" AS u
SET "items" = array_remove(u."items", @__item_1)
WHERE u."userId" = @__userId_0 AND EXISTS (
  SELECT 1
  FROM unnest(u."items") AS i(value)
  WHERE i.value = @__item_1)

PS as a fallback i can always use ExecuteSqlAsync, but i just wanted to have it beautiful, via LINQ 🤩

PSPS replacing value.Items.Where(item => item != itemToDelete... with Append( - works flawlessly and generates SQL with array_append function

Thanks in advance for any help or hint.

[roji]

There are unfortunately some EF-side limitations on using collections inside ExecuteUpdate - you'll have to make do with raw SQL for now.