How to implement Select For Update in EF Core

Question

How to implement Select For Update in EF Core

entity-framework entity-framework-core npgsql postgresql

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)

peter

View more on Stack Overflow

Gustavo Rossi Muller · Accepted Answer · 8/19/2019 3:18:50 PM

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.

How to implement Select For Update in EF Core

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related