CODEWITHSUNDEEP
c#multithreadingentity-framework-core
dnanon
dnanon

Reputation: 550

Lock database row on Read with Entity Framework Core

How is it possible to lock a row on read with EF Core? I found an easy solution using TransactionScope's but they don't seem to work (class not found)

The problem is when deleting an item, which I do in a thread, and press the delete button twice, the item isn't there the second time, so I get an exception.

My first solution was to check if the item is still there, but as method is called as task, the check is performed by both tasks before either of them deletes it. So I would need to lock this row to block the second task from producing an error.

A quick-n-dirty solution would of course be try with empty catch, but I would prefer to handle this in a clean way.

Upvotes: 3

Views: 6309

Answers (3)

Tom
Tom

Reputation: 366

I feel that you should always shield your ef queries in try/catch whenever your are causing INSERT, UPDATE or DELETE, since you can trigger exceptions in various routine conditions like foreign key violations, record not found, etc. Such an exception should be regarded as an error, but not fatal, so should be handled. Then your question is answered not by how to avoid an exception but making sure it doesn't surprise you.

Upvotes: 2

Jonathan Allen
Jonathan Allen

Reputation: 70337

Another option is to simply not use EF for your delete operations.

If you execute the raw SQL DELETE TableName WHERE PrimaryKey = @Key and the record was already deleted then you won't get an error.

Dapper

    using (var sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
    {
        sqlConnection.Open();
        string sqlQuery = "DELETE FROM [dbo].[Customer] WHERE CustomerId=@CustomerId";
        sqlConnection.Execute(sqlQuery, new {customerId});
        sqlConnection.Close();

    }

Tortuga Chain

dataSource.DeleteByKey ( "Customer", CustomerId).Execute();

EF

using (var context = new [...]) {

    context.Database.ExecuteSqlCommand("DELETE FROM [dbo].[Customer] WHERE CustomerId=@CustomerId", new SqlParameter("@CustomerId", CustomerId));

}

(Yea, using EF to execute a non-EF query is wasteful, but it is an option.)

Upvotes: 2

objectNotFound
objectNotFound

Reputation: 1783

Write the Delete statement inside a stored procedure (with necessary parameters that will be used in the where clause for the delete stmt ) and call that stored procedure in EF. The DB will take care of the concurrency. If you need to know if the stored procedure delete any records it can return the rows affected as a output parameter that you can handle in C#.

This is the traditional and IMHO the best way to do it.

Upvotes: 1

Related Questions