EFCore Cannot insert the value NULL into column

Question

I am accepting 3 fields from a user. All 3 fields are mandatory to be entered. Look at the entity configuration below - all 3 fields in the EntityConfiguration has been marked as Required.

This works perfectly when I am Saving a new record to the database. However, now I require to Update an entry. In that case If I ONLY want to update the UserName, and Password - there's an exception thrown because Age can't be Null. How can I resolved this ?

InnerException = {"Cannot insert the value NULL into column 'Age', table 'DBFFFF.dbo.Users'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated."}

CODE

public class User
{
    public int Id{ get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }
    public string Age { get; set; }
}

public class UserEntityConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.ToTable("Users");

        builder.HasKey(c => c.Id);

        builder.Property(c => c.UserName)
                       .IsRequired(true);

        builder.Property(c => c.Password )
            .IsRequired(true);

        builder.Property(c => c.Age )
            .IsRequired(true); 
    }
}
    public async Task<string> UpdateUs(User u)
    {
        _dbCont.Update(u);
        await _dbCont.SaveChangesAsync();
        return "ok";
    }

Answer 1

Your problem is here:

    public async Task<string> UpdateUs(User u)
    {
        _dbCont.Update(u);
        await _dbCont.SaveChangesAsync();
        return "ok";
    }

I assume that your call-site for UpdateUs looks like this:

User u = new User()
{
    Id = 123,
    UserName = "foobar"
};

await UpdateUs(u);

• Note that the above code does not set any values for Age and Password

  • AND YOU MUST NOT STORE USERS' PASSWORDS AS PLAINTEXT!

• ...but then your code calls DbContext.Update...

  • ...which basically tells EF that "this object u now represents the current-state of the entity with Id = 123 including the fact that Age = null", which is not what you want.
    • I assume what you actually want is for EF to only consider the UserName property as updated and to ignore the other non-key properties (Password and Age) which have not yet been set.

• Also, I get the feeling that you're using your entity classes as mutable view-models (e.g. in ASP.NET or WPF). You should not do that because an entity object represents persisted business-data state, it does not represent in-memory mutable UI state.

  • Also, view-models need additional annotations and members that entity types don't have. For example, an "Edit User" view-model needs two password fields ("New password" and "Confirm new password"), this is why we have separate classes for view-models even when they have the same members as an entity class.

---

Anyway, the correct solution for this (assuming that you still want to use your class User as an internal DTO), is to use Attach and set IsModified on the properties you're updating.

Like so:

    public async Task< UpdateUserAsync(User u)
    {
        // Preconditions:
        if( u is null ) throw new ArgumentNullException(nameof(u));
        if( u.Id == default ) throw new ArgumentException( message: "User's Id is not set.", paramName: nameof(u) );

        // 

        this.dbContext.Users.Attach( u );

        var entry = this.dbContext.Entry( u );
        if( u.UserName != null ) entry.Property( x => x.UserName ).IsModified = true;
        if( u.Age      != null ) entry.Property( x => x.Age      ).IsModified = true;

        this.dbContext.Configuration.ValidateOnSaveEnabled = false; // <-- Only do this if necessary.

        Int32 rowCount = await this.dbContext.SaveChangesAsync();
        if( rowCount != 1 ) throw new InvalidOperationException( "No rows (or too many rows) were updated." );
    }

Answer 2

I think this will be helpful answer. You can ignore the specific field(s) when you do update

public async Task<string> UpdateUser(User u)
{
    _dbCont.Update(u);
    _dbCont.Entry(u).Property(x => x.Age).IsModified = false;
    await _dbCont.SaveChangesAsync();
    return "ok";
}

Answer 3

With the below configuration, you are making Age field not nullable.

builder.Property(c => c.Age ).IsRequired(true); 

Remove IsRequired(true); Configuration should be as below.

builder.Property(c => c.Age );