Entity Framework - default values doesn't set in sql server table

Question

SQL server 2005 database table has a column 'createdon' for which default value set to getdate(). I am trying to add a record using entity framework. 'createdon' column is not getting updated.

Did I miss any property in Entity framework, please suggest.

Answer 1

This is one of the few issues that are problematic with Entity Framework. Say you have a class that looks like this:

public class MyEntity
{
    // Id is a PK on the table with Auto-Increment
    public int Id { get; set; }

    // CreatedOn is a datetime, with a default value
    public DateTime CreatedOn { get; set; }
}

Now, you want to insert a new element:

using(var context = new YourContext()) 
{
    context.MyEntities.Add(new MyEntity())
}

Entity Framework knows how to handle an auto-increment primary key because of the definition in the EDMX. It will not try to insert a value for the Id property. However, as far as Entity Framework is concerned, CreatedOn has a value: the default DateTime. Because Entity Framework cannot say "well, it has a value but I should ignore it", it will actively insert the record with the CreatedOn property value, bypassing the default value on your column definition on your table.

There is no easy way to do this. You can either actively set the CreatedOn property to DateTime.Now when you insert that item. Or you can create an interface and an extension method pair:

public interface ICreatedOn
{
    public DateTime CreatedOn { get; set; }
}

public partial class MyEntity : ICreatedOn
{

}

public static TEntity AsNew<TEntity>(this TEntity entity) where TEntity : ICreatedOn
{
    if(entity != null)
        entity.CreatedOn = DateTime.Now;

    return entity;
}

using(var context = new YourContext()) 
{
    context.MyEntities.Add(new MyEntity().AsNew())
}   

Edit: To expand on this point, the reason why this is an unresolvable issue is because of the meaning behind an autoincrement field and a field with a default value constraint. An auto-increment field should, by definition, always be handle by the server, using a seed and all that jazz. You cannot specify a value for an auto-increment field on an insert unless you have used SET IDENTITY INSERT ON. A default value, however, is just a hint that say "if I don't specify any value, use this". Because value types in .NET cannot be null, there will always be a value and Entity Framework cannot infer that the default value for that field, at that time, means that you want it to be defaulted on the SQL server.

Answer 2

Next to using the designer and some more nifty stuff shown already, you can also mark the columns as being calculated by simply setting the DatabaseGenerated attribute on the field:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime CreatedOn { get; set; }

Answer 3

You can set StoreGeneratedPattern to Computed (as Malcolm suggested) in the GUI of the entity data model as well.

1. Open your .edmx file in Visual Studio
2. Open the properties of the field (click on the field -> hit F4 or right click->properties)
3. Set StoreGeneratedPattern to Computed in the properties window as shown below:

Answer 4

I've got around this issue by telling EF that the column is 'computed', and should therefore be left alone for inserts.

If you look in the configuration for the generated entity

       namespace Data.Context
        {
            // Table
            internal partial class MyTableConfiguration : EntityTypeConfiguration<MyTable>
            {
                public MyTableConfiguration(string schema = "dbo")
                {    
                    ToTable(schema + ".MyTable");
                    HasKey(x => x.Id);

                    Property(x => x.ColumnName).HasColumnName("ColumnName").IsOptional().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
                    ....