EF Core default value for not nullable column not set

[adsolutbe]

My models and DbContext are generated with the scaffolding tool.

When I want to save a new object, an exception occurs telling me that the column valuta_code does not allow null values. But a default value for that column has been set in SQL Server: ('EUR').

My model also looks ok:

entity.Property(e => e.ValutaCode)
.IsRequired()
.HasColumnName("valuta_code")
.HasColumnType("varchar(8)")
.HasDefaultValueSql("'EUR'");

The strange thing is, for other columns with the same properties, there is no exception and the default values are getting set.

The exception thrown:

{System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'valuta_code', table 'fakt.dbo.relaties'; column does not allow nulls. INSERT fails.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary2 parameterValues, Boolean closeConnection)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
ClientConnectionId:6e70a009-beda-43e2-8caa-d645e765caa5
Error Number:515,State:2,Class:16}

When I log the query that EF executes, I see that some columns do get a default value, but the column valuta_code does not:

INSERT INTO [relaties]
VALUES (...)
SELECT ... <valuta_code is not included in this list>
FROM [relaties]
WHERE @@rowcount = 1 ...

I'm using EF Core version 1.1.0.
In EF Core version 1.0.1 I did not had this problem.

Anyone else having the same issues?

Further technical details

EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
IDE: (e.g. Visual Studio 2015)

[ErikEJ]

Could you show the table definition (CREATE TABLE) ?

[adsolutbe]

/****** Object:  Table [dbo].[relaties]    Script Date: 22/02/2017 14:45:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[relaties](
	[code] [varchar](10) NOT NULL,
	[naam] [varchar](50) NOT NULL,
	[naam2] [varchar](50) NULL,
	[adres1] [varchar](67) NULL,
	[adres2] [varchar](67) NULL,
	[landen_code] [varchar](4) NULL CONSTRAINT [DF_relaties_landen_code]  DEFAULT ('BE'),
	[gemeente_id] [int] NULL,
	[telefoon] [varchar](25) NULL,
	[telefax] [varchar](25) NULL,
	[gsm] [varchar](25) NULL,
	[email] [varchar](60) NULL,
	[url] [varchar](80) NULL,
	[valuta_code] [varchar](8) NOT NULL CONSTRAINT [DF_relaties_valuta_code]  DEFAULT ('EUR'),
	[moddate] [datetime] NOT NULL CONSTRAINT [DF_relaties_moddate]  DEFAULT (getdate()),
	[createdate] [datetime] NOT NULL CONSTRAINT [DF_relaties_createdate]  DEFAULT (getdate()),
	[taalcodes_taalcode] [varchar](4) NOT NULL CONSTRAINT [DF_relaties_taalcodes_taalcode]  DEFAULT ('N'),
	[klapro_code] [varchar](2) NULL,
	[vertegenw_code] [varchar](10) NULL,
	[relatiegrp1_code] [varchar](10) NULL,
	[betvoorw_code] [varchar](12) NOT NULL CONSTRAINT [DF_relaties_betvoorw_code]  DEFAULT ('CO'),
	[btwregimes_btwregime] [varchar](2) NOT NULL CONSTRAINT [DF_relaties_btwregimes_btwregime]  DEFAULT ('H'),
	[btwnr] [varchar](30) NULL,
	[banknr] [varchar](30) NULL,
	[landen_code_0] [varchar](4) NULL CONSTRAINT [DF_relaties_landen_code_0]  DEFAULT ('BE'),
	[landen_code_1] [varchar](4) NULL CONSTRAINT [DF_relaties_landen_code_1]  DEFAULT ('BE'),
	[createuser] [varchar](8) NOT NULL CONSTRAINT [DF_relaties_createuser]  DEFAULT ('%user%'),
	[moduser] [varchar](8) NOT NULL CONSTRAINT [DF_relaties_moduser]  DEFAULT ('%user%'),
	[aansprek_code] [varchar](8) NULL,
	[prijscat_code] [varchar](10) NOT NULL CONSTRAINT [DF_relaties_prijscat_code]  DEFAULT ('A'),
	[relkortgrp_code] [varchar](10) NULL,
	[bic] [varchar](11) NULL,
	[relaties_code] [varchar](10) NULL,
	[intmemo] [varchar](max) NULL,
	[verzamelfct] [bit] NOT NULL CONSTRAINT [DF_relaties_verzamelfct]  DEFAULT ((1)),
	[kredlimiet] [decimal](12, 2) NULL,
	[melding] [varchar](max) NULL,
	[relatiebron_code] [varchar](10) NULL,
	[ctbtwverlaagd_code] [varchar](10) NOT NULL CONSTRAINT [DF_relaties_ctbtwverlaagd_code]  DEFAULT ('NVT'),
	[vlgdcontact] [datetime] NULL,
	[nacecode_nacecode] [varchar](10) NULL,
	[relatiebron2_id] [int] NULL,
	[relatiegrp2_id] [int] NULL,
	[relaties_code_0] [varchar](10) NULL,
	[redenopzeg_code] [varchar](10) NULL,
	[ctafhandeling_code] [varchar](4) NOT NULL CONSTRAINT [DF_relaties_ctafhandeling_code]  DEFAULT ('L'),
	[transp_code] [varchar](10) NULL,
	[aanmaning] [bit] NOT NULL CONSTRAINT [DF_relaties_aanmaning]  DEFAULT ((1)),
	[attestbtwverlaagd] [bit] NOT NULL CONSTRAINT [DF_relaties_attestbtwverlaagd]  DEFAULT ((0)),
	[klakaart] [bit] NOT NULL CONSTRAINT [DF_relaties_klakaart]  DEFAULT ((1)),
	[oudrelnr] [varchar](40) NULL,
	[verzend_id] [int] NULL,
	[geblokkeerd] [bit] NOT NULL CONSTRAINT [DF_relaties_geblokkeerd]  DEFAULT ((0)),
	[ctidentificatie_code] [varchar](1) NOT NULL CONSTRAINT [DF_relaties_ctidentificatie_code]  DEFAULT ('B'),
	[cttypeafdruk_code] [varchar](2) NOT NULL CONSTRAINT [DF_relaties_cttypeafdruk_code]  DEFAULT ('P'),
	[datkredcontr] [datetime] NULL,
	[rpr] [varchar](15) NULL,
	[isoverheid] [bit] NOT NULL CONSTRAINT [DF_relaties_isoverheid]  DEFAULT ((0)),
 CONSTRAINT [PK_relaties] PRIMARY KEY CLUSTERED 
(
	[code] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_aansprek] FOREIGN KEY([aansprek_code])
REFERENCES [dbo].[aansprek] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_aansprek]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_betvoorw] FOREIGN KEY([betvoorw_code])
REFERENCES [dbo].[betvoorw] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_betvoorw]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_btwregimes] FOREIGN KEY([btwregimes_btwregime])
REFERENCES [dbo].[btwregimes] ([btwregime])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_btwregimes]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_ctafhandeling] FOREIGN KEY([ctafhandeling_code])
REFERENCES [dbo].[ctafhandeling] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_ctafhandeling]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_ctbtwverlaagd] FOREIGN KEY([ctbtwverlaagd_code])
REFERENCES [dbo].[ctbtwverlaagd] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_ctbtwverlaagd]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_ctidentificatie] FOREIGN KEY([ctidentificatie_code])
REFERENCES [dbo].[ctidentificatie] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_ctidentificatie]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_cttypeafdruk] FOREIGN KEY([cttypeafdruk_code])
REFERENCES [dbo].[cttypeafdruk] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_cttypeafdruk]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_gemeente] FOREIGN KEY([gemeente_id])
REFERENCES [dbo].[gemeente] ([id])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_gemeente]
GO

ALTER TABLE [dbo].[relaties]  WITH NOCHECK ADD  CONSTRAINT [FK_relaties_klapro] FOREIGN KEY([klapro_code])
REFERENCES [dbo].[klapro] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_klapro]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_landen] FOREIGN KEY([landen_code])
REFERENCES [dbo].[landen] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_landen]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_landen_0] FOREIGN KEY([landen_code_0])
REFERENCES [dbo].[landen] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_landen_0]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_landen_1] FOREIGN KEY([landen_code_1])
REFERENCES [dbo].[landen] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_landen_1]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_nacecode] FOREIGN KEY([nacecode_nacecode])
REFERENCES [dbo].[nacecode] ([nacecode])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_nacecode]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_prijscat] FOREIGN KEY([prijscat_code])
REFERENCES [dbo].[prijscat] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_prijscat]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_redenopzeg] FOREIGN KEY([redenopzeg_code])
REFERENCES [dbo].[redenopzeg] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_redenopzeg]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_relatiebron] FOREIGN KEY([relatiebron_code])
REFERENCES [dbo].[relatiebron] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relatiebron]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_relatiebron2] FOREIGN KEY([relatiebron2_id])
REFERENCES [dbo].[relatiebron2] ([id])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relatiebron2]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_relatiegrp1] FOREIGN KEY([relatiegrp1_code])
REFERENCES [dbo].[relatiegrp1] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relatiegrp1]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_relatiegrp2] FOREIGN KEY([relatiegrp2_id])
REFERENCES [dbo].[relatiegrp2] ([id])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relatiegrp2]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_relaties] FOREIGN KEY([relaties_code])
REFERENCES [dbo].[relaties] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relaties]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_relaties_0] FOREIGN KEY([relaties_code_0])
REFERENCES [dbo].[relaties] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relaties_0]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_relkortgrp] FOREIGN KEY([relkortgrp_code])
REFERENCES [dbo].[relkortgrp] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_relkortgrp]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_taalcodes] FOREIGN KEY([taalcodes_taalcode])
REFERENCES [dbo].[taalcodes] ([taalcode])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_taalcodes]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_transp] FOREIGN KEY([transp_code])
REFERENCES [dbo].[transp] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_transp]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_valuta] FOREIGN KEY([valuta_code])
REFERENCES [dbo].[valuta] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_valuta]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_vertegenw] FOREIGN KEY([vertegenw_code])
REFERENCES [dbo].[vertegenw] ([code])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_vertegenw]
GO

ALTER TABLE [dbo].[relaties]  WITH CHECK ADD  CONSTRAINT [FK_relaties_verzend] FOREIGN KEY([verzend_id])
REFERENCES [dbo].[verzend] ([id])
GO

ALTER TABLE [dbo].[relaties] CHECK CONSTRAINT [FK_relaties_verzend]
GO

[divega]

In the create table statement you can see that the column valuta_code did get a default value:

[valuta_code] varchar NOT NULL CONSTRAINT [DF_relaties_valuta_code] DEFAULT ('EUR'),

The INSERT statement won't include the column unless a non-default value is set on the corresponding property. This should allow the row to be created with the default value specified on the table.

My only guess is that at the time you see the failure you may be executing the application against a database that is out of date and does not include the default constraint on the column.

[divega]

Can you share the full INSERT statement that fails?

[adsolutbe]

The table definition is the one I'm using now, so it can't be being out of date.

SET NOCOUNT ON;
INSERT INTO [relaties] ([code], [aanmaning], [aansprek_code], [adres1], [adres2], [banknr], [bic], [btwnr], [btwregimes_btwregime], [createdate], [createuser], [ctbtwverlaagd_code], [ctidentificatie_code], [datkredcontr], [email], [gemeente_id], [gsm], [intmemo], [klapro_code], [kredlimiet], [landen_code], [landen_code_0], [landen_code_1], [melding], [moddate], [moduser], [naam], [naam2], [nacecode_nacecode], [oudrelnr], [redenopzeg_code], [relatiebron2_id], [relatiebron_code], [relatiegrp1_code], [relatiegrp2_id], [relaties_code], [relaties_code_0], [relkortgrp_code], [rpr], [taalcodes_taalcode], [telefax], [telefoon], [transp_code], [url], [valuta_code], [vertegenw_code], [verzend_id], [vlgdcontact])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47);
SELECT [attestbtwverlaagd], [betvoorw_code], [ctafhandeling_code], [cttypeafdruk_code], [geblokkeerd], [isoverheid], [klakaart], [prijscat_code], [verzamelfct]
FROM [relaties]
WHERE @@ROWCOUNT = 1 AND [code] = @p0;

[adsolutbe]

The column betvoorw_code has more or less the same properties the column valuta_code has, but for this column the default value is being set

[divega]

I see vaulta_code is actually mentioned in the column list of the INSERT statement. So it is getting a value passed in a parameter.

@AndriySvyryd does this ring any bells?

[rowanmiller]

Can you share the application code that we can use to reproduce the issue.

[adsolutbe]

I attached some sample code that shows where the problem is located.

If I create a DbContext only for the 'relaties' table (MyDbContext2), the default value for the valuta_code field is inserted.
If I include the table Valuta in the model (MyDbContext), the default value is not set and an exception is thrown.

EFTest.zip

[ajcvickers]

Note for triage: the property that is not getting the value generated is an FK property. I don't know if we ever thought about this scenario, but I can't immediately think of any reason why we shouldn't enable it.

[ajcvickers]

@AndriySvyryd We could consider this for a patch if it is bad regression without a workaround, but as of right now we think it probably doesn't need to be in a patch.

[adsolutbe]

At the moment we're setting the default value in code, so we have a workaround.