Delete data from multiple tables at a time

Question

Hi,

I want to insert, update or delete data from multiple tables in a single db call in entity framework

ex:

delete from emp where deptid=1

delete from dept where deptid=1

i want to do this transactions in a single call and also 

i want to rollback if the second transaction fails means if both queries executed successfully then only commit otherwise it have to roll back first table deleted data also

thanks in advance

Answer 1

Simply

            using (var db = new YourDbContext())
            {
                var sql = @"
                delete from emp where deptid=1;
                delete from dept where deptid=1;
                ";

                db.Database.ExecuteSqlCommand(TransactionalBehavior.EnsureTransaction,sql);
            }

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

Using (db conext)_

{

   Using (scope = new system.transaction)

   {

        delete

        delete

        scope.complete()

     }

{

https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx?f=255&MSPPError=-2147217396&cs-save-lang=1&cs-lang=csharp#code-snippet-2

If code doesn't reach scope.complete() and it blewup, then the Using statement for the Transaction.Scope is going to rollback all transactions within rhe scope.

Answer 3

Hi David, 

thank you for replying. 

i dont want to use sql queries in code. 

Answer 4

>i dont want to use sql queries in code. 

Then just use SaveChanges(), it will introduce a transaction to ensure that all the pending changes are either completed successfully or rolled back.  It will result in multiple SQL queries, though.

David

---

David http://blogs.msdn.com/b/dbrowne/