C#无法为表"Rentals"中的标识列插入显式值.当IDENTITY_INSERT设置为OFF时 [英] C# Cannot insert explicit value for identity column in table "Rentals" when IDENTITY_INSERT is set to OFF

问题描述

我目前正在学习ASP.NET MVC，并且正在学习通过其ID获取对象并将其引用插入数据库.但是，我收到此错误

I am currently learning ASP.NET MVC and I'm learning to get an object via its ID and inserting its reference to the database. However, I am getting this error

当IDENTITY_INSERT设置为OFF时，无法为表出租"中的标识列插入显式值

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

我已经阅读了一遍，我注意到有人说要将身份插入设置为off，因为引用表的ID是自动递增的?但是我已经读过，不推荐使用此方法，因为它显然会锁定表或仅对单个用户有用?我可以知道执行此操作的正确方法是什么吗?

I have read around and I noticed that some people say to set identity insert to off because the ID of the referenced table is auto incremented? However I have read that this method is not recommended because it apparently locks off the table or is only useful for single users? May I know what is the correct way to do this?

这是我的控制器代码，正在尝试添加新的Rental

This is my controller code that is trying to add a new Rental

[HttpPost]
public IHttpActionResult CreateNewRental(RentalDTO RentalDTO)
{
    if (RentalDTO.MovieIds.Count == 0)
    {
        return BadRequest();
    }
    var customer = _context.Customers.SingleOrDefault(c => c.Id == RentalDTO.CustomerId);
    if (customer == null)
    {
        return BadRequest("Customer ID is not valid");
    }
    var movies = _context.Movies.Where(m => RentalDTO.MovieIds.Contains(m.Id)).ToList();
    if (movies.Count != RentalDTO.MovieIds.Count)
    {
        return BadRequest();
    }
    foreach (var movie in movies)
    {
        if (movie.NumberAvailable < 1)
        {
             return BadRequest();
        }
        movie.NumberAvailable--;
        var rental = new Rentals
        {
            Customer = customer,
            Movie = movie,
            DateRented = DateTime.Now,
        };
        _context.Rentals.Add(rental);
    }
    _context.SaveChanges();
    return Ok();
}

这是我的出租模式

public class Rentals
{
    public byte Id { get; set; }
    [Required]
    public Customers Customer { get; set; }
    [Required]
    public Movies Movie { get; set; }
    public DateTime DateRented { get; set; }
    public DateTime? DateReturned { get; set; }
}

这是我用来创建表格的迁移

This is the migration I used to create the table

CreateTable(
            "dbo.Rentals",
            c => new
                {
                    Id = c.Byte(nullable: false, identity: true),
                    DateRented = c.DateTime(nullable: false),
                    DateReturned = c.DateTime(),
                    Customer_Id = c.Int(nullable: false),
                    Movie_Id = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Customers", t => t.Customer_Id, cascadeDelete: true)
            .ForeignKey("dbo.Movies", t => t.Movie_Id, cascadeDelete: true)
            .Index(t => t.Customer_Id)
            .Index(t => t.Movie_Id);

更新:

我已经调试了代码，意识到当我添加新的Rental时，将ID设置为0.如何自动设置?

I have debugged my code and realized that when I added a new Rental, 0 was set as the ID. How do I set that automatically?

推荐答案

出租"表中的一列是身份列(猜测是ID列).通常，您不想显式设置标识列的值，因此您的SQL Server不允许不打开选项(IDENTITY_INSERT).

One of the columns in your Rentals table is an identity column (guessing it's the ID column). You generally don't want to explicitly set the value of an identity column, so your SQL server doesn't allow you without turning an option (IDENTITY_INSERT) on.

为避免此问题，无论您使用什么机制来保存更改，都需要知道在插入时无需指定标识列.相反，您可以指定其余的列，然后让数据库确定标识列的值.同样，在更新时，通常不会修改此列，因此您的更新不应尝试对其进行更改.

To avoid this problem, whatever mechanism you are using to save changes needs to know that when inserting, you don't specify the identity column. Instead, you specify the rest of the columns and let the database decide the value of the identity column. Similarly when updating, you won't generally be modifying this column, so your update shouldn't attempt to change it.

如果您提供有关要用于模型保存的框架的更多信息，我们也许可以提供更具体的答案.

If you provide more information about what framework you're using for your model saving, we may be able to provide more specific answers.

如果您实际上需要为Identity列指定特定值(例如，将数据从一个数据库复制到另一个数据库就是我亲自完成的一个示例)，则所有这些更改都会发生.在这种情况下，您可以使用语句 set IDENTITY_INSERT ON 允许插入，然后在完成后将其关闭.

This all changes if you actually have a need to specify a particular value for an identity column (such as copying data from one database to another is one example I've personally done this). In that case, you can use the statement set IDENTITY_INSERT ON to allow the insert, then turn it back off when you're done.

这篇关于C#无法为表"Rentals"中的标识列插入显式值.当IDENTITY_INSERT设置为OFF时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！