0

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.

Improve this question
8
  • 1
    Either change UserPost Id column to an int: public int Id { get; set; } or set it as [DatabaseGenerated(DatabaseGeneratedOption.None)]. AFAIK you can't set a string/GUID column as autogenerated value column, it only applies to int, long or other numeric data types (see stackoverflow.com/questions/8855100/…). Commented Jul 6, 2017 at 7:49
  • @TetsuyaYamamoto So if I want to keep my ID as a string, how do I generate it when creating a new Post since it isn't auto-generated? Commented Jul 6, 2017 at 8:08
  • 1
    You can generate it manually from class constructor, like public UserPost() { Id = Guid.NewGuid().ToString(); }. Certainly you can set another value instead of a GUID there. Commented Jul 6, 2017 at 8:10
  • I tried changing the Id to an int, but I still get the same error. @TetsuyaYamamoto Commented Jul 6, 2017 at 8:42
  • This is Code First, right? If you're already creating a DB with data & still encountering same error, use EF Code First Migration to change identity column data type. As long as data type for Id in target table doesn't change, EF still treats that column as string instead of an int. Commented Jul 6, 2017 at 8:51

1 Answer 1

Reset to default
0

[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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.