Entity framework generates values for NOT NULL columns which has default defined in db

Question

Hi I have a table Customer. One of the columns in table is DateCreated. This column is NOT NULL but default values is defined for this column in db.

When I add new Customer using EF4 from my code.

var customer = new Customer();
customer.CustomerName = "Hello";                
customer.Email = "[email protected]";
// Watch out commented out.
//customer.DateCreated = DateTime.Now;
context.AddToCustomers(customer);
context.SaveChanges();

Above code generates following query.

exec sp_executesql N'insert [dbo].[Customers]([CustomerName], 
[Email], [Phone], [DateCreated], [DateUpdated])
values (@0, @1, null, @2, null)
select [CustomerId]
from [dbo].[Customers]
where @@ROWCOUNT > 0 and [CustomerId] = scope_identity()
',N'@0 varchar(100),@1 varchar(100),@2 datetime2(7)
',@0='Hello',@1='[email protected]',@2='0001-01-01 00:00:00'

And throws following 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.

Can you please tell me how NOT NULL columns which has default values at db level should not have values generated by EF?

DB:

DateCreated DATETIME NOT NULL

DateCreated Properties in EF:

• Nullable: False
• Getter/Setter: public
• Type: DateTime
• DefaultValue: None

Thanks.

Answer 1

From my knowledge of EF (which is minimal), it does not grab the default value from the schema. The fact that you are inserting the row and the column is marked as NOT NULL, means EF thinks it should be inserting a value for that column which happens to have the value of DateTime.MinValue.

You may need to force your own values in the entities constructor or create some factory methods.

Is there anything on the property pages of the table designer in EF that lets you specify a default value?

Answer 2

You have to said to the ORM that the property is generated by the database. For the example in the Customer Class in the previous example, the following code should work.

public class Customer{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]//The database generates a value when a row is inserted. 
    public int CustomerId { get; set; }

    public string CustomerName { get; set; }

    public string Email { get; set; }

    public string Phone { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)] //The database generates a value when a row is inserted or updated.
    public DateTime DateCreated { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.IComputed)]//The database generates a value when a row is inserted or updated.
    public DateTime DateUpdated { get; set; }

}

Don't forget to add the

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] 

to the primary key, for a reason that i not know, when you use the annotation of DatabaseGeneratedAttribute at least once in the Model Class, you have to specify the the generated option method. When i use them, i have an error because the ORM was trying to insert the key in the query statement instead of let the database to generate them.

There are three Database Generated Options

• Computed : The database generates a value when a row is inserted or updated.
• Identity : The database generates a value when a row is inserted.
• None : The database does not generate values.

You can find the documentation here Database Generated Options

Answer 3

NOT NULL column means that property is not nullable and it must have a value in your entity. If you do not specify value in value type like DateTime the default value will be persisted. Default value for DateTime is DateTime.MinValue.

As I know EF doesn't support scenario where you can use NOT NULL column and not send any value from your application. The only exception is when you set StoreGeneratedPattern for the property to Identity or Computed but in such case you will not be able to set the value in the application.

So you can set value in application or in database. Not in both.