Default value not respected with Entity Framework Core Insert

Question

My SQL Server 2017 table is created in Transact-SQL as follows:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[JobTag]
(
    [JobId] [int] NOT NULL,
    [Name] [nvarchar](64) NOT NULL,
    [Created] [datetime] NOT NULL,
    [Value] [nvarchar](max) NULL,
    [Comment] [nvarchar](2048) NULL,

    CONSTRAINT [PK_JobTag_1] 
       PRIMARY KEY CLUSTERED ([JobId] ASC, [Name] ASC)
                   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                         IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobTag] 
    ADD CONSTRAINT [DF_JobTag_Created] DEFAULT (GETDATE()) FOR [Created]
GO

ALTER TABLE [dbo].[JobTag]  WITH CHECK 
    ADD CONSTRAINT [FK_JobTag_job] 
        FOREIGN KEY([JobId]) REFERENCES [dbo].[job] ([JobID])
GO

ALTER TABLE [dbo].[JobTag] CHECK CONSTRAINT [FK_JobTag_job]
GO

For the model I include

 public System.DateTime Created { get; set; }

In .Net Core, when I try to add a record using

   var newTag = new JobTag { JobId = job.JobID, Name = "myName", Value = myValueString};
   connect.JobTags.Add(newTag)
   connect.SaveChanges()

I get an error

Microsoft.EntityFrameworkCore.DbUpdateException:
An error occurred while updating the entries. See the inner exception for details.

System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

I do not get an error if I initialise the Created property with DateTime.Now.

If I remove the property from the model, then the SQL Server DateTime is used correctly.

Why do I need to initialize the property when it has a default value?

Use Fluent API configuration and it will generate the appropriate default constrain for the auto generate date-time — TanvirArjel
– TanvirArjel, Commented Mar 21, 2019 at 2:11
One simple solution is use datetime2 instead of datetime for Created column — TanvirArjel
– TanvirArjel, Commented Mar 21, 2019 at 2:22
@TanvirArjel will that make use of the datetime from the database? The c# code is running on a different pc. — Kirsten
– Kirsten, Commented Mar 21, 2019 at 2:40
It will generate appropriate datetime for Created column during insert in database. You don't need to assign it manually in c# code. — TanvirArjel
– TanvirArjel, Commented Mar 21, 2019 at 2:42
Yes it is: modelBuilder.Entity<Blog>().Property(b => b.Created).HasDefaultValueSql("getdate()"); — TanvirArjel
– TanvirArjel, Commented Mar 21, 2019 at 2:50

score 2 · Accepted Answer · 2019-03-21 02:57:29Z

2

Null values can be stopped, or allowed, in either place, but the database has the final say.

In your model, it should be DateTime.Now for the default date.

If you did want to allow nulls

Putting DateTime? for the column type in model may fix the issue. This will allow the value to be null from the application perspective, which probably occurs before the record is created in the database.

public DateTime? Created { get;set;}

If you do not want to allow nulls and want the flexibility of not identifying a value when creating a new record from the application

set it to a default value in your model:

public DateTime Created { get;set;}=DateTime.Now

The problem is

In your database, you say it is required:

[Created] [datetime] NOT NULL,

But, in app, you don’t set it.

var newTag = new JobTag { JobId = job.JobID,     Name = "myName", Value = myValueString};  
connect.JobTags.Add(newTag)
connect.SaveChanges()

The problem is two fold, as by setting:

public DateTime Created { get;set;}

You are also saying it is required from the application perspective, as it does not include the ?, meaning no nulls are allowed, but it has no default value.

The requirement needs to match in both places.

edited Mar 21, 2019 at 2:57

answered Mar 21, 2019 at 1:38

user9105725

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

2 Comments

Kirsten Over a year ago

Surely public DateTime Created { get;set;}=DateTime.Now in the model does not have the same effect as using getdate() in the fluent api ?

Kirsten Over a year ago

In summary I need to either declare the fluent api properties or not have the datetime in the model.

Collectives™ on Stack Overflow

Default value not respected with Entity Framework Core Insert

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related