Linq2db batch delete not working for 2 tables

Question

I need to batch delete from a table filtering from another related table. there can be millions of rows so I use .Take() in a loop - so as not to fill transaction log. I'm getting SqlException: The column 'InvoiceId' was specified multiple times for 't1'.

var x = ctx.Invoices.Where(p => p.InvoiceDate <= new DateTime(2023, 9, 14)).SelectMany(p =>p.InvoiceDetails);
var y = x.ToLinqToDB().Take(100000);
y.Delete();

converts to

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 6.0.13 initialized 'Context' using provider 'Microsoft.EntityFrameworkCore.SqlServer:6.0.13' with options: SensitiveDataLoggingEnabled 
info: LinqToDB[0]
      --  SqlServer.2008
      DECLARE @take Int -- Int32
      SET     @take = 10
      DECLARE @InvoiceDate Date
      SET     @InvoiceDate = CAST('2023-09-14T00:00:00.0000000' AS DATETIME2)
     
      DELETE [c]
      FROM
        (
          SELECT TOP (@take)
            *
          FROM
            [Invoices] [p]
              INNER JOIN [InvoiceDetails] [c_1] ON [p].[InvoiceId] = [c_1].[InvoiceId]
          WHERE
            [p].[InvoiceDate] = @InvoiceDate
        ) [t1]
      
fail: LinqToDB[0]
      Failed executing command.
      Microsoft.Data.SqlClient.SqlException (0x80131904): The column 'InvoiceId' was specified multiple times for 't1'.

I have tried the many different ways of coding this, Query syntax, method syntax. If it is just one table .Delete() works fine. also Tried .ToLinq2DbTable() with the same error.

Linq2db version 6.7.1 EF Core version 6.0.13

Answer 1

Looks likee a bug. Try the following workaround. Since you are deleting InvoiceDetails, start from this entity:

var x = ctx.InvoiceDetails.Where(p => p.Invoice.InvoiceDate <= new DateTime(2023, 9, 14));
var y = x.ToLinqToDB().Take(100000);
y.Delete();