EF returns "Cannot Insert NULL" ... But column defines a default value

Question

I am Using EF 6, Database First (edmx), I have defined a table with a NON NULLABLE field which has a default value defined. When I use EF to insert a new record I am getting the error ...

"Cannot insert the value NULL into column attr_is_required ... "

Here's the table definition ...

CREATE TABLE [Auth].[AppAttributes]
(
    [app_attr_id]  BIGINT NOT NULL PRIMARY KEY IDENTITY, 
    [app_id]       BIGINT NOT NULL, 
    [attr_name]    NVARCHAR(50) NOT NULL, 
    [attr_description] NVARCHAR(MAX) NULL, 
    [attr_type]    NVARCHAR(50) NOT NULL,   
    [attr_is_required] BIT        DEFAULT (0) NOT NULL, 
    [created_date] DATETIME       DEFAULT (getdate()) NOT NULL,
    [created_by]   NVARCHAR (50)  DEFAULT ('db') NOT NULL,
    [updated_date] DATETIME       DEFAULT (getdate()) NOT NULL,
    [updated_by]   NVARCHAR (50)  DEFAULT ('db') NOT NULL, 
)

Here is the C# used to add the record. You will notice that I do not specify the attr_is_required at all.

uow.DbContext.AppAttributes.Add( new AppAttribute
{
    app_id           = adminApp.app_id,
    attr_description = "User's Date of Birth",
    attr_name        = "DateOfBirth",
    attr_type        = AppAttributeTypes.DATE.ToString(),
    created_by       = UnitTestSvc.CREATED_BY,
    created_date     = DateTime.Now,
    updated_by       = UnitTestSvc.UPDATED_BY,
    updated_date     = DateTime.Now
});

I have managed to log the SQL generated by EF.

INSERT [Auth].[AppAttributes]([app_id], [attr_name], [attr_description], [attr_type], [attr_is_required], [created_date], [created_by], [updated_date], [updated_by])
VALUES (@0, @1, @2, @3, NULL, @4, @5, @6, @7)

What I notice is that the NULL value is explicitly being assigned to the attr_is_required column. I read somewhere that removing this field from the INSERT statement would allow the default to be used instead. The problem is that the SQL generation is abstracted away by EF. I am not sure what, if anything there is I can do about this. I suppose I CAN explicitly provide the value if I have to, but it doesn't seem like I should be required to provide it.

Does anyone know a way to deal with this?

DUPLICATE?

The referrred to article is dealing with code-first approach where I am using database-first and the visual designer. That being said the article did inspire me to look at something which led me to an solution.

SOLUTION

In the visual designer (EDMX file) I selected the attr_is_required property on the AppAttribute object. In the properties pane I noticed the setting for "Nullable" Setting this to FALSE appears to have resolved my issue.

Your analysis is correct. To get the Default value to be used you have to remove the field from the actual raw sql. I am interested to learn what the best way to do this with EF6 is. — Sql Surfer, Apr 07 '18 at 22:11
While I disagree with this being tagged as a duplicate, since the referred to post is dealing with code-first, and I am using database first approach with the visual designer (edmx) in VS, the post DID inspire me to look at the individual field Properties of the field in the EDMX file. I selected the **attr_is_required** field in the designer and in the properties pane I set the **Nullable** property to FALSE. This seems to have resolved the issue. — Gary O. Stenstrom, Apr 07 '18 at 22:30
That seems like an appropriate error. Your table definition says the column can not be NULL so I would not expect you to be able to give it a NULL. Remove the column from the insert statement to have the default value applied. — thomas, Apr 08 '18 at 16:36
Does Not Seem Like a Duplicate. The so-called duplicate has a solution that uses the compiled code to define the default value. The solution desired in this question is to respect the current default of the DB at runtime without requiring a hardcoded default in the compiled code. — Sql Surfer, Apr 09 '18 at 02:22
@thomas The problem is that EF is auto-generating the INSERT statement and determining which columns to include. I would think that by not specifying ANY value for that field at all, it would not include it, but it does and provides it with a NULL value, thereby breaking the request. I am using the database-first approach and visual designer to avoid as much interaction with the EF details as possible. If I have to engage in custom coding in order to omit fields from the auto-generated SQL then I begin to lose much of the supposed value of this approach. — Gary O. Stenstrom, Apr 09 '18 at 20:52

EF returns "Cannot Insert NULL" ... But column defines a default value

0 Answers0