获得“下一个价值"对于使用 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;
相关文章