CODEWITHSUNDEEP
c#linqlinq-to-sql
flying227
flying227

Reputation: 1321

Using Linq, I need to delete all of the rows from a table and then enter about a thousand new rows. How can I make this whole thing a transaction?

I need to empty a table and then enter around 1000 rows into it. I need for the whole thing to be a transaction, however, so I'm not stuck with an empty (or partially empty) table if any of the inserts fail.

So I experimented with the code below, where the insert (.Add) will intentionally fail. When running it, however, the call to the delete stored procedure (prDeleteFromUserTable) does not roll back with the transaction. I'm left with an empty table and no inserts.

using (var context = new Entities(_strConnection))
{
    using (var transaction = new TransactionScope())
    {
        //delete all rows in the table
        context.prDeleteFromUserTable();

        //add a row, which I made intentionally make fail to test the transaction
        context.UserTable.Add(row);
        context.SaveChanges();

        //end the transaction
        transaction.Complete();
    }
}

How would I accomplish this using Linq-to-SQL?

Upvotes: 0

Views: 632

Answers (1)

Richard Schneider
Richard Schneider

Reputation: 35477

LINQ is for queries (Language Integrated Query) and is not designed for BULK deletion and insertion. A good solution would be to use SQL to delete all rows DELETE FROM myTable and the SqlBulkCopy for the 1000 inserts.

Upvotes: 1

Related Questions