CODEWITHSUNDEEP
entity-framework-core
Kirsten
Kirsten

Reputation: 18066

Default value not respected with Entity Framework Core Insert

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?

Upvotes: 1

Views: 2725

Answers (1)

user9105725
user9105725

Reputation:

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.

Upvotes: 2

Related Questions