如何在实体框架中获取 SQL Server 序列的下一个值? [英] How to get next value of SQL Server sequence in Entity Framework?

问题描述

我想使用 SQL Server sequence 对象 在实体框架中显示数字序列，然后将其保存到数据库中.

I want to make use SQL Server sequence objects in Entity Framework to show number sequence before save it into database.

在当前场景中，我正在通过在存储过程中递增 1(存储在一个表中的前一个值)并将该值传递给 C# 代码来做一些相关的事情.

In current scenario I'm doing something related by increment by one in stored procedure (previous value stored in one table) and passing that value to C# code.

为了实现这一点，我需要一张表，但现在我想将其转换为 sequence 对象(它会带来任何好处吗?).

To achieve this I needed one table but now I want to convert it to a sequence object (will it give any advantage ?).

我知道如何在 SQL Server 中创建序列并获取下一个值.

I know how to create sequence and get next value in SQL Server.

但我想知道如何在实体框架中获取 SQL Server 的 sequence 对象的下一个值?

But I want to know how to get next value of sequence object of SQL Server in Entity Framework?

我无法在相关问题中找到有用的答案 在 SO.

提前致谢.

推荐答案

您可以在 SQL Server 中创建一个简单的存储过程来选择下一个序列值，如下所示:

You can create a simple stored procedure in SQL Server that selects the next sequence value like this:

CREATE PROCEDURE dbo.GetNextSequenceValue 
AS 
BEGIN
    SELECT NEXT VALUE FOR dbo.TestSequence;
END

然后您可以将该存储过程导入到实体框架中的 EDMX 模型中，然后调用该存储过程并获取序列值，如下所示:

and then you can import that stored procedure into your EDMX model in Entity Framework, and call that stored procedure and fetch the sequence value like this:

// get your EF context
using (YourEfContext ctx = new YourEfContext())
{
    // call the stored procedure function import   
    var results = ctx.GetNextSequenceValue();

    // from the results, get the first/single value
    int? nextSequenceValue = results.Single();

    // display the value, or use it whichever way you need it
    Console.WriteLine("Next sequence value is: {0}", nextSequenceValue.Value);
}

更新:实际上，您可以跳过存储过程，直接从您的 EF 上下文运行这个原始 SQL 查询:

Update: actually, you can skip the stored procedure and just run this raw SQL query from your EF context:

public partial class YourEfContext : DbContext 
{
    .... (other EF stuff) ......

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

        return nextVal;
    }
}

这篇关于如何在实体框架中获取 SQL Server 序列的下一个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！