Is there a best way to delete very data in database like 160k of data with Nhibernate

Question

I have like 120k of data in a table using mssql and i have to delete all to save again, but it´s turn very slow.

I´ve an Entity called InventarioEstoque and have two childrens InventariosEstoquesSaldos and InventariosEstoquesSaldosTerceiros, i tried to delete 100 in 100 and commit to database, but it´s take more than one minute for each 100 data, i was looking at the console, maybe it´s beacause are genering query N+1 for the childrens when look for cascade delete.

the last code i tried is this

var itensDelete = UnitOfWork.InventariosEstoques.All().Where(x => dates.Contains(x.Data.Date) && x.OrigemInventario == OrigemInventarioEstoque.FechamentoEstoque).Select(x => x.Id);

                    while (itensDelete.Any())
                    {
                        var idsDelete = itensDelete.Take(100).ToList();
                        UnitOfWork.InventariosEstoques.Delete(x => idsDelete.Contains(x.Id));
                        UnitOfWork.SaveChanges();
                    }

is there a way to delete this data with Nhibernate more fast?

Answer 1

Add a using NHibernate.Linq statement at the top and use DmlExtensionMethods.Delete extension method:

var itensDelete = UnitOfWork.InventariosEstoques.All()
    .Where(x => dates.Contains(x.Data.Date) && x.OrigemInventario == OrigemInventarioEstoque.FechamentoEstoque)
    .Delete();

UnitOfWork.SaveChanges();