DateTime column in EntityFramework code-first does not work as expected

Question

I have base model User and two additional models UserCreateModel and UserEditModel. UserCreateModel works just fine and user is created, but when I try to edit user it gives me error.

I don't understand why he acts up on datetime since it has valid DateTime property already in place in database. All I wanted to do is update Name property. What am I doing wrong here?

Error

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.

Controller

public HttpResponseMessage PutUser(int id, UserEditModel user)
{
    if (!ModelState.IsValid)
    {
        return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
    }

    if (id != user.Id)
    {
        return Request.CreateResponse(HttpStatusCode.BadRequest);
    }

    db.Entry(user).State = EntityState.Modified;

    try
    {
        db.SaveChanges(); // Exception here, all validation passed with success
    }
    catch (DbUpdateConcurrencyException ex)
    {
        return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
    }

    return Request.CreateResponse(HttpStatusCode.OK);
}

Model

public class User
{
    public int Id { get; set; }
    public virtual DateTime CreatedDateTime { get; set; }
    public virtual string Username { get; set; }
    public virtual string Password { get; set; }
    public virtual string PasswordSalt { get; set; }
    public virtual string AccessToken { get; set; }
    public virtual string Name { get; set; }
}

public class UserEditModel : User
{
    [Required]
    public override string Name { get; set; }
}

Answer 1

You need to tell Entity Framework to use the correct column type when you map your entities to the database.

If you are using the fluent API you can do it like this:

Property(p => p.CreatedDateTime).HasColumnType("datetime2");

or if you prefer using the column attribute directly on your POCO:

public class User
{   
    public int Id { get; set; }

    [Column(TypeName = "DateTime2")]
    public virtual DateTime CreatedDateTime { get; set; }

    public virtual string Username { get; set; }
    public virtual string Password { get; set; }
    public virtual string PasswordSalt { get; set; }
    public virtual string AccessToken { get; set; }
    public virtual string Name { get; set; }
}

Be careful; depending on how you have configured Entity Framework, your existing database may be deleted and recreated to account for the change.

See the following blog posts for further information:

• Configuring/Mapping Properties and Types with the Fluent API
• Configuring/Mapping Properties and Types with Data Annotations

Answer 2

By setting db.Entry(user).State = EntityState.Modified; you tell EF that you want to update all properties of user. And because your UserEditModel derives from the User entity EF will try to update all entity properties.

I don't know why you derive the Edit model from the entity but it causes problems in your scenario. I would prefer to just load the user from the Db, update the property you want and save it:

var dbUser = db.Users.Find(user.Id);
dbUser.Username = user.Name;
db.SaveChanges();

More generally you can update multiple properties like so:

var dbUser = db.Users.Find(user.Id);
db.Entry(dbUser).CurrentValues.SetValues(user);
db.SaveChanges();

But it requires that the properties in the edit model have the same name as the entity properties (and that the edit model is not derived from the entity).