获得“下一个价值"对于使用 EF Core 3.1 的 SQL Server 序列 - 不可能?

Getting quot;NEXT VALUE FORquot; for a SQL Server sequence using EF Core 3.1 - impossible?(获得“下一个价值对于使用 EF Core 3.1 的 SQL Server 序列 - 不可能?)

问题描述

我正在编写一个新的 ASP.NET Core Web API，我的要求之一是能够利用 EF Core 3.1 来获取在我的 SQL Server 中定义的序列的下一个值作为我需要存储的记录的 ID.

I'm writing a new ASP.NET Core Web API, and one of my requirements is to be able to leverage EF Core 3.1 to grab the next value of a sequence defined in my SQL Server as the ID for a record I need to store.

我正在努力寻找一种方法来做到这一点 - 在 EF 6.x 中，我直接在 DbContext 后代上使用了一种方法，如下所示:

I'm struggling to find a way to do this - in EF 6.x, I used a method directly on the DbContext descendant like this:

public int GetNextSequenceValue()
{
    var rawQuery = Database.SqlQuery<int>("SELECT NEXT VALUE FOR dbo.TestSequence;");
    var task = rawQuery.SingleAsync();
    int nextVal = task.Result;

    return nextVal;
}

对于高达 2.1 的 EF Core，我本来可以使用 Database.ExecuteSqlCommand() 来运行 SQL 代码段并返回结果.但似乎在 EF Core 3.x 中，我不走运....

and for EF Core up to 2.1, I would have been able to use Database.ExecuteSqlCommand() to run a SQL snippet and get back results. But it seems, in EF Core 3.x, I'm out of luck....

我知道 .FromSqlRaw() 和 .FromSqlInterpolated 方法在 DbSet 上 - 但因为我只需要返回下一个序列的值(INT)，它不会飞.而且我也知道这些方法也存在于 context.Database 级别，看起来它与我在 EF 6.x 中的非常接近 - 但在这里，这些方法将只 返回受影响的行数 - 我还没有找到从 SEQUENCE 发回新值的方法.

I know there are the .FromSqlRaw() and .FromSqlInterpolated methods on the DbSet - but since I only need to return the next value of a sequence (an INT), that's not going to fly. And I also know these methods also exist on the context.Database level which looks like it would be really close to what I had in EF 6.x - but here, those methods will only return the number of rows affected - I haven't found a way to send back the new value from the SEQUENCE.

在 EF Core 3.x 中真的可以这样，我实际上必须求助于古老的 ADO.NET 代码来获取该值吗??是否有真的无法执行任意 SQL 代码段并从上下文中获取一些结果??

Can it really be that in EF Core 3.x, I have to actually resort back to way-old ADO.NET code to fetch that value?? Is there REALLY no way to execute an arbitrary SQL snippet and get back some results from the context??

推荐答案

如果你想运行一个任意的 TSQL 批处理并返回一个标量值，你可以这样做:

If you want to run an arbitrary TSQL batch and return a scalar value, you can do it like this:

var p = new SqlParameter("@result", System.Data.SqlDbType.Int);
p.Direction = System.Data.ParameterDirection.Output;
context.Database.ExecuteSqlRaw("set @result = next value for some_seq", p);
var nextVal = (int)p.Value;

这篇关于获得“下一个价值"对于使用 EF Core 3.1 的 SQL Server 序列 - 不可能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！