时间戳

首页 > 解决方案 > 当 IDENTITY_INSERT 设置为 OFF 时，无法为表中的标识列插入显式值。”

sql-server - 当 IDENTITY_INSERT 设置为 OFF 时，无法为表中的标识列插入显式值。”

问题描述

对于我的 API，我将 Entity Framework Core 与代码优先迁移一起使用。我创建了一些运行良好的关系。现在，我添加了另一个关系（一对多），突然间我被这个错误打了耳光：

Cannot insert explicit value for identity column in table 'Companies' when IDENTITY_INSERT is set to OFF."

Offcourse，我一定是做错了什么，但我就是不知道是什么。我遇到了更多这样的问题，答案是“将 IDENTITY_INSERT 设置为 ON”，但这对我不起作用，因为 EF 正在处理所有事情。

我的公司类可以属于一个组：

public class Company
{
  // Primary key
  public int Id { get; set; }

  // The optional Id of a Group
  public int? GroupID { get; set; }

...

}

和 Group 类：

public class Group
{
  // Primary key
  public int Id { get; set; }

  // Name of the Group
  public string Name { get; set; }

  // List of Companies in this group
  public IEnumerable<Company> Companies { get; set; }
}

用于处理 POST 的代码：

// POST api/groups
[HttpPost]
public async Task<IActionResult> Post([FromBody] Group group)
{
  try
  {
    if (ModelState.IsValid)
    {
      _context.Groups.Add(group);
      await _context.SaveChangesAsync();
      return CreatedAtRoute("GetGroup", new { id = group.Id }, group);
    }

    return BadRequest(ModelState);
  }
  catch (Exception e)
  {
    return BadRequest($"Unable to create: {e.Message}");
  }
}

在我的数据库中，所有列、索引和键都是按预期创建的，就像我在 API 中建立的所有其他一对多关系一样。但这个具体案例似乎最终以痛苦告终......

我试图添加到数据库的类：

标签： sql-serverasp.net-coreentity-framework-core

解决方案

Problem is that there's no hint for EF to know if Company (under Group relationship) is being explicitly inserted or if it is supposed to use the pre-existing one from database.

Since those instanced are disconnected from DbContext there is no indication whether they exist or not on the database by the time EF tries to generate its SQL command.

There is no easy way here, at least there was none by the time I've played with EF Core.

You should either:

  1. Change your code to only use the ID instead of the navigation property so you'll avoid this whenever possible, or;
  2. Change your code to fetch related data (eg: fetch Company and attach it to Group) before saving desired data (eg: before saving Group).

So, for instance:

var companyDB = await context.Companies.SingleOrDefaultAsync(c => c.Id == group.Company.Id);
group.Company = companyDB;
context.Groups.Add(group);
await context.SaveChangesAsync();

Yes, you're making two trips to database. That's why I'd suggest using the first approach, so you can avoid this fetch and just save Group entity directly into DB.

That does not, however, prohibits you from sending a navigation instace of Company to your view. Just create some entity-related classes which will correlate to your database so you can load/save data using this entity type, and create a Dto object which will be your endpoint input/output whenever needed.

Binding one into another can be done by using AutoMapper, manual linq or other approaches.

推荐阅读