array_remove is not translated in ExecuteUpdate #3410
Description
array_remove was added in #3328 as a translation for arrayOrList.Where(i => i != value). This works fine in a normal select statement / projection and is properly translated to array_remove. Unfortunately it does not work with ExecuteUpdate where it throws the typical "could not be translated" error. I would expect that bulk update should be able to recognize this pattern as well.
Please see the following reproduction:
public sealed class Thing
{
public int Id { get; set; }
public int[] AsArray { get; set; } = [];
public List<int> AsList { get; set; } = [];
}
public sealed class TestContext : DbContext
{
public DbSet<Thing> Things => Set<Thing>();
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseNpgsql("Host=localhost;Username=postgres;Password=postgres;Database=stringtest");
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Thing>(e =>
{
e.ToTable("things");
e.Property(p => p.AsArray).IsRequired().HasDefaultValueSql("ARRAY[]::int[]");
e.Property(p => p.AsList).IsRequired().HasDefaultValueSql("ARRAY[]::int[]");
});
}
}
await using (var ctx = new TestContext())
{
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();
ctx.Things.Add(new Thing { Id = 1, AsList = [ 1, 2, 3 ], AsArray = [ 1, 2, 3 ] });
await ctx.SaveChangesAsync();
}
// querying works fine
await using (var ctx = new TestContext())
{
var a = await ctx.Things.Select(e => new { e.Id, arr = e.AsArray.Where(i => i != 2) }).FirstAsync();
var b = await ctx.Things.Select(e => new { e.Id, arr = e.AsList.Where(i => i != 2) }).FirstAsync();
Console.WriteLine($"Id: {a.Id}, Vals: {string.Join('-', a.arr)}");
Console.WriteLine($"Id: {b.Id}, Vals: {string.Join('-', b.arr)}");
}
// both of these throw
await using (var ctx = new TestContext())
{
await ctx.Things.ExecuteUpdateAsync(e => e.SetProperty(p => p.AsArray, p => p.AsArray.Where(i => i != 2)));
await ctx.Things.ExecuteUpdateAsync(e => e.SetProperty(p => p.AsList, p => p.AsList.Where(i => i != 2)));
}It does not matter if you configure the properties with Property or PrimitiveCollection; neither List<T> nor T[] work.
We are currently using a custom IMethodCallTranslator/IMethodCallTranslatorPlugin and a set of ArrayRemove extension methods on DbFunctions. It would be nice to be able to drop this in favor of built-in support.
Versions used
- Npgsql.EntityFrameworkCore.PostgreSQL - 9.0.2
- Npgsql - 9.0.2
- Microsoft.EntityFrameworkCore - 9.0.0
- .NET 9