ASP.NET插入Oracle数据时遇ORA-02287序列错误求助

我在Oracle数据库中有对应Box模型的表，尝试通过ASP.NET应用插入新数据，ID由名为box_seq的序列生成。插入时ID无法写入，返回ORA-02287: sequence number not allowed here错误，尝试多种手动查询序列的写法均未解决。

现有代码 #

上下文类 #

using ApiProject.Models;
using Microsoft.EntityFrameworkCore;

namespace ApiProject.Data
{
    public class GeneralContext: DbContext
    {
        public GeneralContext(DbContextOptions<GeneralContext> options): base(options) { }
        public DbSet<Box> BoxDatabase { get; set; } = default!;
        public long SetNewId()
        {
            var query = Database.SqlQueryRaw<long>("SELECT box_seq.nextval FROM dual");
            long id = query.Single();
            return id;
        }
    }
}

控制器代码 #

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using ApiProject.Data;
using ApiProject.Models;
using ApiProject.Models.Requests;

namespace ApiProject.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class BoxesController: ControllerBase
    {
        …
        [HttpPost]
        public async Task<ActionResult<Box>> create(BoxRequest request)
        {
            if (_context.BoxDatabase == null)
            {
                return Problem("Entity set 'GeneralContext.BoxDatabase' is null.");
            }
            var result = new Box
            {
                Id = _context.SetNewId(),
                XLength = request.XLength,
                YLength = request.YLength,
                ZLength = request.ZLength,
            };
            _context.BoxDatabase.Add(result);
            await _context.SaveChangesAsync();

            return CreatedAtAction("Get", new { id = result.Id }, result);
        }
        …
    }
}

尝试过的其他写法及问题 #

• 写法1：返回相同ORA-02287错误

public long SetNewId()
{
    var query = Database.SqlQuery<long>($"SELECT box_seq.nextval FROM dual;");
    long id = query.Single();
    return id;
}

• 写法2：返回missing right parenthesis错误（SQL语法错误）

public long SetNewId()
{
    var query = Database.SqlQuery<long>($"SELECT NEXT VALUE FROM box_seq;");
    long task = query.SingleAsync();
    long id = task.Result;
    return id;
}

• 写法3：返回cannot convert from 'string' to 'System.FormattableString'警告（方法参数类型不匹配）

public long SetNewId()
{
    var query = Database.SqlQuery<long>("SELECT box_seq.nextval FROM dual;");
    long id = query.Single();
    return id;
}

方案1：通过EF Core Fluent API自动绑定序列（推荐） #

这是最符合EF Core设计的方式，无需手动查询序列，让框架自动处理ID生成：

1. 修改上下文类，配置序列绑定 #

using ApiProject.Models;
using Microsoft.EntityFrameworkCore;

namespace ApiProject.Data
{
    public class GeneralContext: DbContext
    {
        public GeneralContext(DbContextOptions<GeneralContext> options): base(options) { }
        public DbSet<Box> BoxDatabase { get; set; } = default!;

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // 配置Box实体的ID使用box_seq序列生成
            modelBuilder.Entity<Box>()
                .Property(b => b.Id)
                .HasColumnName("ID") // 确保与数据库列名一致
                .UseOracleSequenceHiLo("box_seq");
        }
    }
}

2. 修改控制器创建方法，移除手动ID赋值 #

[HttpPost]
public async Task<ActionResult<Box>> create(BoxRequest request)
{
    if (_context.BoxDatabase == null)
    {
        return Problem("Entity set 'GeneralContext.BoxDatabase' is null.");
    }
    var result = new Box
    {
        XLength = request.XLength,
        YLength = request.YLength,
        ZLength = request.ZLength,
        // 不再手动设置Id，EF会自动生成
    };
    _context.BoxDatabase.Add(result);
    await _context.SaveChangesAsync();

    return CreatedAtAction("Get", new { id = result.Id }, result);
}

方案2：修复手动查询序列的方法（若必须手动生成） #

如果业务需要手动控制ID生成，需使用EF Core正确的异步查询方法，并避免同步阻塞：

1. 修改SetNewId为异步方法 #

public async Task<long> SetNewIdAsync()
{
    // 使用FromSqlRaw执行原生Oracle查询，避免类型匹配问题
    var id = await BoxDatabase.FromSqlRaw("SELECT box_seq.nextval FROM dual")
                              .Select(b => b.Id)
                              .FirstOrDefaultAsync();
    return id;
}

2. 控制器中异步调用 #

var result = new Box
{
    Id = await _context.SetNewIdAsync(),
    XLength = request.XLength,
    YLength = request.YLength,
    ZLength = request.ZLength,
};

错误原因分析 #

1. ORA-02287：手动设置ID后，EF Core可能仍尝试自动生成ID，导致序列使用冲突；或原生查询的上下文状态与后续插入操作冲突。
2. missing right parenthesis：SELECT NEXT VALUE FROM box_seq是SQL Server语法，Oracle获取序列值的正确语法为SELECT box_seq.nextval FROM dual。
3. 类型转换警告：SqlQuery<T>要求传入可格式化字符串，普通字符串需使用SqlQueryRaw<T>方法。

内容的提问来源于stack exchange，提问作者Prido1024