CODEWITHSUNDEEP
c#sql-serverentity-framework
Jim
Jim

Reputation: 2828

Entity Framework delete from three tables in one query

I am trying to delete multiple records from three db tables (one parent and two child's) using EF6 in a for-each loop in a SQL Server database. While I was able to do it via EF default db.SaveChanges() method the whole process is unacceptably slow. Therefore I am leaning towards executing ExecuteSqlCommand on three tables in single transaction using the bellow method:

 foreach (var sm in MyCollection)
 {
     var query = "DELETE t1, t2, t3 FROM @table1 as t1 " +
                 "JOIN @table2 as t2 ON t2.ID = t1.ID " +
                 "JOIN @table3 as t3 ON t3.ID = t1.ID";


    List<SqlParameter> parameterList = new List<SqlParameter>();
    parameterList.Add(new SqlParameter("@table1", table1));
    parameterList.Add(new SqlParameter("@table2", table2));
    parameterList.Add(new SqlParameter("@table3", table3));
    SqlParameter[] parameters = parameterList.ToArray();

    // Execute the query against the context. Note that this bypasses SaveChanges() and executes immediately.
    int result = db.Database.ExecuteSqlCommand(query, parameters);  
 }

Current approach throwing error near ','.

My question is : is this correct way of doing this transaction, any other suggestions on how to speedup the whole process?

Note: Cascade delete is not enabled on this db and I don't have access of adding it.

Upvotes: 1

Views: 285

Answers (1)

M.Ali
M.Ali

Reputation: 69554

You can issue a Delete statement against only ONE Table at a time.

But to keep this multiple Deletes operation atomic you can wrap three delete statements in one transaction.

Also you cannot pass a table name as you are trying to it would treat that variable as a literal string but not as an object name.

I would do this operation as follow:

First create the procedure to handle delete operation

Procedure Definition

CREATE PROCEDURE usp_Delete_Records
 @Table     SYSNAME,
 @ID        INT
 AS
 BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(MAX);   

   SET @Sql = N' DELETE FROM ' + QUOTENAME(@Table) 
            + N' WHERE ID = @ID'

    Exec sp_executesql @Sql 
                      ,N'@ID INT'
                      ,@ID
 END

Handling Multiple Deletes

BEGIN TRANSACTION;

  EXEC usp_Delete_Records @Table1 , @ID 
  EXEC usp_Delete_Records @Table2 , @ID 
  EXEC usp_Delete_Records @Table3 , @ID 

COMMIT TRANSACTION;

Upvotes: 2

Related Questions