C# .NET 4.0 MVC Inserting a record with primary key

Question

We have a database with a tables called 'Sites' This table has the columns, SiteID, Name, Tags, Description, URI, with SiteID being a primary key (It is not set as an Identity because we want to add our own ID)

We have been using .NET 4.0 MVC with C# and have setup everything up in the code which we need. We can select things from the database and display them so we know that is working. But when we try to insert we get a Cannot insert the value NULL into column 'SiteID' error.

If I set the column as an Identity so that it auto generates, or if I take off the primary key then it is fine, but as I said it should be a primary key and we want to insert out own ID's.

My code is below (We get the error on SaveChanges() but have checked in the debugger and SiteID is definitely being assigned an int)

Sites

public class Sites
{
    [Key]
    public int SiteID { get; set; }
    public string Name { get; set; }
    public string Tags { get; set; }
    public string Description { get; set; }
    public string URI { get; set; }
}

CMSModels

public class CMSModels : DbContext
{
//public DbSet<ContentTypeModel> ContentType { get; set; }
    //public DbSet<LayoutModel> Layout { get; set; }
    //public DbSet<PageModel> Page { get; set; }
    //public DbSet<PageZoneModel> PageZone { get; set; }
    public DbSet<Sites> Site { get; set; }
    //public DbSet<ZoneContentModel> ZoneContent { get; set; }
    //public DbSet<ZoneTypeModel> ZoneType { get; set; }
}

HomeController:

private CMSModels models = new CMSModels();

public ActionResult Index()
{
    Sites site = new Sites { SiteID = 4, Name = "Name", Description = "Desc", Tags = "", URI = "" };

    models.Site.Add(site);
    models.SaveChanges();

    return View(models.Site.ToList());
}

I don't understand why I am getting this error, so any ideas would be appreciated.

If you need to see any other code please let me know.

Edit:

Just to extend this question a bit, it seems that this only happens on primary keys which are of the type int.

If we set the database column data type to nvarchar and the data types in the code to string it works fine.

Is this just a bug in Entity Framework 4, or are we just doing something wrong?

If we can't find a way to insert ints into a primary key column we may just have to use strings instead and validate them to make sure they are numeric.

Answer 1

If you want a NO IDENTITY primary key column, so you set the id manually, you have 2 possible solutions.

1. Decorate your id propertie with the following Attributes (DatabaseGenerated...).

    public class Sites
    {
        [Required, Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int SiteID { get; set; }
        public string Name { get; set; }
        public string Tags { get; set; }
        public string Description { get; set; }
        public string URI { get; set; }
    }

2. Do it using the ModelBuilder in your DbContext.

    public class CMSModels : DbContext
    {
      //public DbSet<ContentTypeModel> ContentType { get; set; }
      //public DbSet<LayoutModel> Layout { get; set; }
      //public DbSet<PageModel> Page { get; set; }
      //public DbSet<PageZoneModel> PageZone { get; set; }
      public DbSet<Sites> Site { get; set; }
      //public DbSet<ZoneContentModel> ZoneContent { get; set; }
      //public DbSet<ZoneTypeModel> ZoneType { get; set; }

      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          base.OnModelCreating(modelBuilder);
          modelBuilder.Entity<Sites>().Property(r => r.SiteID) 
                       .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
      }
    }