How to implement Select For Update in EF Core

Question

As far as I've understood it, there is no option in EF (and EF Core) to explicitly lock resources which I'm querying, but I'll need this functionality quite often and don't really feel like falling back to writing select statements every time I'll need it.

Since I only need it for postgres and according to the spec FOR UPDATE is the last item in the query, the easiest I thought about implementing it was to get the select statement as described here: In Linq to Entities can you convert an IQueryable into a string of SQL? and append FOR UPDATE and directly execute it. However this will either give me a query with parameter placeholders or not a prepared query meaning that caching for execution plan won't really work on postgres, so in either way it's a no go.

Linq to SQL had the method DataContext.GetCommand but there doesn't seem to be anything equivalent in EF and specially EF Core. I also had a look at EntityFramework.Extended and their batch updates / deletes but since they have to transform the select statement into a different statement they need to deal with far more complexity than me and so I hope for a simpler solution.

Update:

In case it wasn't clear from the description, I want to create an extension method like this:

public static IList<T> ForUpdate (this IQueryable<T> me)
{
    // this line is obviously what is missing for me :)
    var theUnderlyingCommand = me.GetTheUnderlyingDbCommandOrSimilar();

    theUnderlyingCommand.Text += "FOR UPDATE";
    return me.ToList();
}

This way, other developers can use EF via Linq as with all other procedures and instead of running .ToList() they'd run .ForUpdate(). (For Update executes the query on purpose to make the implementation easier, and also because FOR UPDATE is the last option supported by postgres, afterwards there shouldn't be anything else anymore)

Answer 1

This work's for me using SQLServer (no tested async methods):

First, create a DbCommandInterceptor (I called HintInterceptor.cs)

using System;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Text.RegularExpressions;

public class HintInterceptor : DbCommandInterceptor
{
    private static readonly Regex _tableAliasRegex = new Regex(@"(?<tableAlias>FROM +(\[.*\]\.)?(\[.*\]) AS (\[.*\])(?! WITH \(*HINT*\)))", RegexOptions.Multiline | RegexOptions.IgnoreCase | RegexOptions.Compiled);

    [ThreadStatic]
    public static string HintValue;

    private static string Replace(string input)
    {
        if (!String.IsNullOrWhiteSpace(HintValue))
        {
            if (!_tableAliasRegex.IsMatch(input))
            {
                throw new InvalidProgramException("Não foi possível identificar uma tabela para ser marcada para atualização(forupdate)!", new Exception(input));
            }
            input = _tableAliasRegex.Replace(input, "${tableAlias} WITH (*HINT*)");
            input = input.Replace("*HINT*", HintValue);
        }
        HintValue = String.Empty;
        return input;
    }

    public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        command.CommandText = Replace(command.CommandText);
    }

    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        command.CommandText = Replace(command.CommandText);
    }
}

So into Web.config register the your interceptor class

<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
<interceptors> 
  <interceptor type="Full.Path.Of.Class.HintInterceptor, Dll.Name" />
</interceptors>
</entityFramework>

Now I create a static class called HintExtension

public static class HintExtension
{
    public static IQueryable<T> WithHint<T>(this IQueryable<T> set, string hint) where T : class
    {
        HintInterceptor.HintValue = hint;
        return set;
    }
    public static IQueryable<T> ForUpdate<T>(this IQueryable<T> set) where T : class
    {
        return set.WithHint("UPDLOCK");
    }
}

That's All, I can use inside a database transaction like:

using(var trans = context.Database.BeginTransaction())
{
        var query = context.mydbset.Where(a => a.name == "asd").ForUpdate();
        // not locked yet
        var mylist = query.ToList();
        // now are locked for update
        // update the props, call saveChanges() and finally call commit ( or rollback)
        trans.Commit();
        // now are unlocked
}

Sorry for my English, I hope my example will help.

Answer 2

According to this issue there is no easy way to implement locks hints and other database oriented calls in ef core

I implemented UPDLOCK with MsSQL and ef core in my project this way:

public static class DbContextExtensions
{
    public static string GetUpdLockSqlForEntity<T>(this DbContext dbContext, int entityPk, bool pkContainsTableName = true) where T : class
    {
        var mapping = dbContext.Model.FindEntityType(typeof(T)).Relational();
        var tableName = mapping.TableName;
        var entityPkString = entityPk.ToString();
        string idPrefix = pkContainsTableName ? tableName.Substring(0, tableName.Length - 1) : string.Empty;
        return $"Select 1 from {tableName} with (UPDLOCK) where {idPrefix}Id = {entityPkString}";
    }
}

We are using this method in database transaction as raw sql call(lock will be released after commit or rollback):

using (var dbTran = await DataContext.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted))
{
    try
    {
        await DataContext.Database.ExecuteSqlCommandAsync(DataContext.GetUpdLockSqlForEntity<Deposit>(entityId));
        dbTran.Commit();
    }
    catch (Exception e)
    {
        dbTran.Rollback();
        throw;
    }
}