Lock database row on Read with Entity Framework Core

c# entity-framework-core multithreading

Question

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.

1
2
7/25/2016 5:47:10 AM

Accepted Answer

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.)

1
7/24/2016 10:03:01 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow