Delete is not batched when used with EntityFrameworkCore .Net8 and Postgres

[phebing]

Here is what to include in your request to make sure we implement a solution as quickly as possible.

1. Description

Delete does not use BatchSize or BatchDelayInterval when used with EntityFrameworkCore .Net8 and Postgres. Is that by design? Looking at /EntityFramework-Plus/src/shared/Z.EF.Plus.BatchDelete.Shared/BatchDelete.cs I can see that the CommandTextPostgreSQLTemplate does not contain a {Top} that would be replaced by BatchSize nor {Delay} replaced by BatchDelayInterval.

If that is by design, then please let me know and mark this in the documentation of the method and on your online help (https://entityframework-plus.net/ef-core-batch-delete)

2. Exception

-

3. Fiddle or Project

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Npgsql.EntityFrameworkCore.PostgreSQL;
using System.Collections.Generic;
using Microsoft.Data.SqlClient;
using Z.EntityFramework.Plus;
namespace efcoreplustest;

class Program
{
    public static void Main()
    {
        using (var context = new EntityContext())
        {
            context.Database.EnsureCreated();
        }

        GenerateData();
        Console.WriteLine("beforeDelete");
        // Delete: DELETE all rows from the database using a LINQ Query without loading entities in the context
        using (var context = new EntityContext())
        {
            // BatchSize
           // OrderBy(c => c.CustomerID) creates a limit, but int.max is used. See BatchDelete.cs 201 -> query = query.Take(int.MaxValue);
            context.Customers.Where(c => c.CustomerID < int.MaxValue).OrderBy(c => c.CustomerID).Delete(x =>
            {
                x.BatchDelayInterval = 2000;
                x.BatchSize = 1;
            });
        }
        Console.WriteLine("afterDelete");

        using (var context = new EntityContext())
        {
            context.Customers.ToList().ForEach(x => Console.WriteLine(x?.Name));
        }

    }

    public static void GenerateData()
    {

        using (var context = new EntityContext())
        {
            context.Customers.Add(new Customer() { Name = "Customer_A", IsActive = false });
            context.Customers.Add(new Customer() { Name = "Customer_B", IsActive = true });
            context.Customers.Add(new Customer() { Name = "Customer_C", IsActive = false });

            context.SaveChanges();
        }

        using (var context = new EntityContext())
        {
            context.Customers.ToList().ForEach(x => Console.WriteLine(x?.Name));

        }
    }

    public class EntityContext : DbContext
    {
        public EntityContext()
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql("Server=127.0.0.1;Port=5432;Database=testDB;User Id=test;Password=test;");

            optionsBuilder.EnableSensitiveDataLogging();
            optionsBuilder.EnableDetailedErrors();
            optionsBuilder.LogTo(Console.WriteLine);

            base.OnConfiguring(optionsBuilder);
        }

        public DbSet<Customer> Customers { get; set; }
    }

    public class Customer
    {
        public int CustomerID { get; set; }
        public string Name { get; set; } = "";
        public Boolean IsActive { get; set; }
    }
}

Running that create the following Query:

 LOG:  execute <unnamed>: 
     DELETE FROM "Customers" AS A
     USING ( SELECT c."CustomerID"
     FROM "Customers" AS c
     WHERE c."CustomerID" < 2147483647
     ORDER BY c."CustomerID"
     LIMIT $1 ) AS B WHERE A."CustomerID" = B."CustomerID"

DETAIL:  parameters: $1 = '2147483647'

4. Any further technical details

Further technical details

    <PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="8.0.7" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.4" />
    <PackageReference Include="Z.EntityFramework.Plus.EFCore" Version="8.103.0" />

[JonathanMagnan]

Hello @phebing ,

Thank you for reporting.

Indeed the current option BatchDelayInterval = 2000; and x.BatchSize = 1; are only supported for SQL Server

We will look into adding this option for PostgreSQL. I cannot promise anything yet but I should be able to give you an answer within 1 or 2 weeks about if we add the support to it or not.

Best Regards,

Jon

[JonathanMagnan]

Hello @phebing ,

The support for BatchDelayInterval and BatchSize has been finally added to PostgreSQL starting from version 8.103.2.0

If you have the chance to test it, could you let us know if everything is working as expected?

Best Regards,

Jon