MVC EF - System.Data.Entity.Infrastructure.DbUpdateException

Question

I have two models : UserProfile

public class UserProfile
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ApplicationUser ApplicationUser { get; set; }
    public virtual ICollection<UserPost> UserPost { get; set; }
}

UserPost:

public class UserPost
{  
   [Key]
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public string Id { get; set; }
   public string PostTitle { get; set; }
   public virtual UserProfile UserProfile { get; set; }
   public string Contents { get; set; }
}

UserProfile has a one-to-one relationship with AspNetUser.

UserProfile has a one-to-many relationship with UserPost.

When I'm adding UserPost to the database in the Create method in my controller I get a System.Data.Entity.Infrastructure.DbUpdateException

    [HttpPost]
    [ValidateAntiForgeryToken]
    [Authorize]
    public ActionResult Create([Bind(Include = "PostTitle, Content")] UserPost post)
    {
        if (ModelState.IsValid)
        {

            UserManager = new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(db));
            var user = UserManager.FindById(User.Identity.GetUserId());
            post.UserProfile = user.UserProfile;
            db.UserPosts.Add(post);
            db.SaveChanges();//error here
            return RedirectToAction("Index");
        }

        return View(post);
    }

System.Data.Entity.Infrastructure.DbUpdateException occurred HResult=0x80131501 Message=An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

Inner Exception 1: UpdateException: An error occurred while updating the entries. See the inner exception for details.

Inner Exception 2: SqlException: Cannot insert the value NULL into column 'Id', table 'aspnet-project12017.dbo.UserPosts'; column does not allow nulls. INSERT fails. The statement has been terminated.

Answer 1

[DatabaseGenerated(DatabaseGeneratedOption.Identity)] means that the column marked with such attribute expected having auto-generated value from DBMS, and as a general convention that EF treats string & uniqueidentifier (GUID) columns as non auto-incremented, hence those columns value must be assigned in another way (i.e. by constructor assignment).

Common approach to solve null problem for primary key auto-incremented identity column is simply set it to numeric data type (e.g. int):

public class UserPost
{  
   [Key]
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public int Id { get; set; } // change to numeric data type
   public string PostTitle { get; set; }
   public virtual UserProfile UserProfile { get; set; }
   public string Contents { get; set; }
}

Then, when performing migrations, do these steps:

1. Sometimes EF expects by default that you're trying to insert into identity column. If this happens, set StoreGeneratedPattern.None & DatabaseGenerationOption.None to UserPost.Id temporarily:

   protected void OnModelCreating(DbModelBuilder modelBuilder)
   {
       // other entities
   
       modelBuilder.Entity<UserPost>()
          .HasKey(p => p.Id)
          .Property(p => p.Id)
          .StoreGeneratedPattern = StoreGeneratedPattern.None;
   
      // other entities
   }
   

2. Ensure that the model has been modified to include actual Id property (try to delete UserPost table in DB when required).

3. Run migration through Package Manager Console as this:

   Add-Migration "Set Id as Identity Specification"
   

4. In a generated class from DbMigration you will see two methods: Up() and Down(). Modify Up() part to set int datatype there (and re-run migration process when required):

   public override void Up()
   {
       CreateTable(
             "dbo.UserPost",
             c => new
             {
                 Id = c.Int(nullable: false, identity: true),
                 PostTitle = c.String(),
                 Contents = c.String(),
             })
             .PrimaryKey(t => t.Id);                
   }
   

If you still want string Id column as primary key for UserPost, you need to set it as [DatabaseGenerated(DatabaseGeneratedOption.None)] and assign values manually from class constructor (note that generated values must be unique at all or EF will tell you "cannot insert duplicate key in object"):

public class UserPost
{
    public UserPost()
    {
        Id = [[autogenerated value here]];
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Id { get; set; }

    // other properties
}

Related issues:

MVC5 EF6 Seed Method: Cannot insert NULL into column 'Id' table AspNetUsers

Cannot insert the value NULL into column 'Id' (Database first)

Code first - Cannot insert the value NULL into column 'Id'

Entity Framework Cannot insert the value NULL into column Identity Specification set to No