1

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

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

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?

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();
}

This is my rentals model

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);

UPDATE:

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?

JianYA
  • 2,750
  • 8
  • 60
  • 136
  • 1
    What's the schema/model for your rentals table? – S.C. May 11 '18 at 15:18
  • 1
    Ive added it in the edit – JianYA May 11 '18 at 15:25
  • 1
    I think you should change your id to int and have this column set as identity true – Juan May 11 '18 at 15:28
  • 2
    "set identity insert to off because the ID of the referenced table is auto incremented" The purpose of turning identity insert to ON is to save an object where you explicitly set the ID. Instead, identity insert being OFF (as in your error messages) means you shouldn't be explicitly setting a RentalID when you create that object.... let the database handle that (and leave identity insert OFF). – C. Helling May 11 '18 at 15:30
  • 1
    Hi @C.Helling, sorry so does that mean I wont have to set it on ON? But right now my db isnt automatically handling it – JianYA May 11 '18 at 15:34
  • I'm also not creating an object. I get the object based on its ID and then insert the object to the db – JianYA May 11 '18 at 15:38
  • I believe you should leave `IDENTITY_INSERT` set to `OFF`, but I don't see where you're explicitly setting the `RentalID`. Explicitly setting the `RentalID` (as opposed to the `CustomerID` as a foreign key or whatever) is the source of your error. – C. Helling May 11 '18 at 15:40
  • I have attached the table creation migration in case it would help – JianYA May 11 '18 at 15:44
  • I would include the type of database you are using (such as SSMS) as a tag also – JosephDoggie May 11 '18 at 15:45
  • Are you aware that having your `Id` column as a `byte` will only allow for a max of 256 rows in the `Rentals` table, right? – Bradley Uffner May 11 '18 at 15:48
  • Yeah this was for a lesson. I will be changing it to an int later on. – JianYA May 11 '18 at 15:49

2 Answers2

5

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.

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.

Tyler Lee
  • 2,736
  • 13
  • 24
  • Hi, thanks for answering. I am using asp mvc 5. My method of saving is in the controller. I have attached my model as well. I'm unsure of what else I should provide. – JianYA May 11 '18 at 15:42
  • I have attached my table creation migration in case it would help – JianYA May 11 '18 at 15:44
  • @JianYA I searched around a bit more and found this question https://stackoverflow.com/questions/11300883/why-databasegenerateddatabasegeneratedoption-identity-doesnt-work-in-mvc-4 On his ID property, he has the attribute `[DatabaseGenerated(DatabaseGeneratedOption.Identity)]`. It looks like this might be the way to specify an Identity value in MVC. I am not 100% familiar however... – Tyler Lee May 11 '18 at 15:47
  • I tried adding this annotation above the customer and movie objects but it still doesnt work – JianYA May 11 '18 at 15:51
1

We ran into this issue today, also on a byte-type identity column. It didn't happen on the tables with an int-type identity column, but in the case of the byte column, what we assumed to be default behaviour was not being followed. Despite it being marked with HasKey under the entity configuration, our system was still attempting to insert a value into the identity column.

The solution that we found was the addition of a Property within the entity's configuration, marking it explicitly as having its value generated by the database: 

Property(c => c.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

(with using System.ComponentModel.DataAnnotations.Schema)

Once we set this property, the identity-insert bug was resolved.

Rob Wilkins
  • 1,650
  • 2
  • 16
  • 20