如何在 EF Core 中实现 Select For Update [英] How to implement Select For Update in EF Core

问题描述

据我所知，EF(和 EF Core)中没有选项可以显式锁定我正在查询的资源，但我会经常需要这个功能，并且不会真的觉得要掉下来回到每次需要时编写选择语句.

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.

因为我只需要 postgres 和 根据规范 FOR UPDATE 是查询中的最后一项，我想到的最简单的实现方法是获取如下所述的 select 语句:在Linq to Entities中，你可以将IQueryable转换为SQL字符串吗? 并附加 FOR UPDATE 并直接执行.然而，这要么给我一个带参数占位符的查询，要么不给我一个准备好的查询，这意味着执行计划的缓存在 postgres 上不会真正起作用，所以无论哪种方式都不行.

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 有方法 DataContext.GetCommand 但在 EF 和特别是 EF Core 中似乎没有任何等效的东西.我还查看了 EntityFramework.Extended 及其批量更新/删除，但由于他们必须将 select 语句转换为不同的语句，因此他们需要处理比我复杂得多的问题，因此我希望有一个更简单的解决方案.

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.

更新:

如果描述不清楚，我想创建一个这样的扩展方法:

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();
}

这样，其他开发人员可以像所有其他程序一样通过 Linq 使用 EF，而不是运行 .ToList()，而是运行 .ForUpdate().(For Update 故意执行查询以使实现更容易，也因为 FOR UPDATE 是 postgres 支持的最后一个选项，之后不应该再有其他任何东西了)

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)

推荐答案

这项工作适合我使用 SQLServer(没有经过测试的异步方法):

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

首先创建一个DbCommandInterceptor(我叫HintInterceptor.cs)

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);
    }
}

所以在 Web.config 中注册你的拦截器类

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>

现在我创建一个名为 HintExtension 的静态类

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.

这篇关于如何在 EF Core 中实现 Select For Update的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！