asp.net - 不断收到 SqlException：当 IDENTITY_INSERT 设置为 OFF 时，无法在表“电影”中插入标识列的显式值

问题描述

我正在开发一个程序，我将一个 excel 文件放到 SQL 数据库中。我正在使用 EPPlus -版本 4.5.2.1。导入 excel 文件 Movie 时，我不断收到错误消息。SqlException：当 IDENTITY_INSERT 设置为 OFF 时，无法在表“电影”中插入标识列的显式值。DbUpdateException：更新条目时发生错误。有关详细信息，请参阅内部异常。 HomeController.cs 中的 WebApplication14.Controllers.HomeController.Import(IFormFile 文件) + await _dbContext.SaveChangesAsync();

家庭控制器代码：

namespace WebApplication14.Controllers
{
    public class HomeController : Controller
    {
        private readonly ApplicationDbContext _dbContext;
        public HomeController(ApplicationDbContext dbContext)
        {
            _dbContext = dbContext;
        }
        public async Task<List<Movie>> Import(IFormFile file)
        {
            var list = new List<Movie>();
            using (var stream = new MemoryStream())
            {
                await file.CopyToAsync(stream);
                using (var package = new ExcelPackage(stream))
                {

                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                    var rowcount = worksheet.Dimension.Rows;
                    var colcount = worksheet.Dimension.Columns;

                    for (int row = 2; row < rowcount; row++)
                    {
                        list.Add(new Movie
                        {
                            Id = int.Parse(worksheet.Cells[row, 1].Value.ToString().Trim()),
                            Title = worksheet.Cells[row, 2].Value.ToString().Trim(),
                            Genre = worksheet.Cells[row, 3].Value.ToString().Trim()
                        });
                    }
                }
            }
            //SaveDataToDb(list);
            _dbContext.Movie.AddRange(list);
            await _dbContext.Database.ExecuteSqlCommandAsync(@"SET IDENTITY_INSERT [MovieList-1].[dbo].[Movie] ON");
            await _dbContext.SaveChangesAsync();
            await _dbContext.Database.ExecuteSqlCommandAsync(@"SET IDENTITY_INSERT [MovieList-1].[dbo].[Movie] OFF");

            return list;


        }

        public IActionResult Index()
        {
            return View();
        }

        public IActionResult Privacy()
        {
            return View();
        }

        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}

模特电影

namespace WebApplication14.Models
{
    public class Movie
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Genre { get; set; }
    }
}

DbContext 代码

namespace WebApplication14.Models
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {

        }

        public DbSet<Movie> Movie { get; set; }
    }
}

我的迁移代码

public partial class MoviesToDb : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Movie",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Title = table.Column<string>(nullable: true),
                    Genre = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Movie", x => x.Id);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Movie");
        }
    }
}

启动代码

    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllersWithViews();
            services.AddDbContext<ApplicationDbContext>(options =>
               options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });

        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }
            app.UseHttpsRedirection();
            app.UseStaticFiles();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllerRoute(
                    name: "default",
                    pattern: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

我的 AppSettings.Json 连接字符串

 "ConnectionStrings": {
    "DefaultConnection": "Server=localhost\\SQLEXPRESS;Database=MovieList-1;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

查看/首页/索引

@{
    ViewData["Title"] = "Home Page";
}

<div class="text-center">
    <h1 class="display-4">Welcome</h1>
    <p>Learn about <a href="https://docs.microsoft.com/aspnet/core">building Web apps with ASP.NET Core</a>.</p>
    <div class="container">
        <form method="post" asp-controller="Home" asp-action="Import" enctype="multipart/form-data">
            <input type="file" name="file" />
            <button type="submit">Import From Excel</button>
        </form>
    </div>
</div>

标签： asp.netsql-serverasp.net-mvcvisual-studioasp.net-core

asp.net - 不断收到 SqlException：当 IDENTITY_INSERT 设置为 OFF 时，无法在表“电影”中插入标识列的显式值

问题描述

解决方案

推荐阅读