HasDefaultValueSql is overriding my actual supplied value it seems

[gatecrasher63]

HasDefaultValueSql is doing something I can't explain

I have a simple class

    public partial class ApplicantAddress
    {
        public ApplicantAddress()
        {
            Applicant = new HashSet<Applicant>();
        }

        public int AddressId { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string AddressLine3 { get; set; }
        public string AddressTowncity { get; set; }
        public string AddressPostCode { get; set; }
        public string AddressCountry { get; set; }
        public string AddressProviceOrState { get; set; }
        public bool AddressTrusted { get; set; }
        public virtual ICollection<Applicant> Applicant { get; set; }
    }

The AddressTrusted has a SQL default of TRUE

                entity.Property(e => e.AddressTrusted)
                    .HasColumnName("address_trusted")
                    .HasDefaultValueSql("((1))");

It has a constraint on the database

ALTER TABLE [dbo].[applicant_address] ADD  CONSTRAINT [df_address_trusted]  DEFAULT ((1)) FOR [address_trusted]
GO

In my code I set the value

                        ApplicantAddress NewAddress = new ApplicantAddress
                        {
                            AddressLine1 = NewApplicant.Address.Line1?.Trim(),
                            AddressLine2 = NewApplicant.Address.Line2?.Trim(),
                            AddressLine3 = NewApplicant.Address.Line3?.Trim(),
                            AddressTowncity = NewApplicant.Address.TownCity?.Trim(),
                            AddressPostCode = NewApplicant.Address.PostalCode?.Trim(),
                            AddressCountry = NewApplicant.Address.Country?.Trim(),
                            AddressProviceOrState = NewApplicant.Address.ProvinceState?.Trim(),
                            AddressTrusted = false
                        };

But the value is ALWAYS being written as TRUE to the table

I put tracing on and I saw

      INSERT INTO [applicant_address] ([address_country], [address_line_1], [address_line_2], [address_line_3], [address_post_code], [address_provice_or_state], [address_towncity])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);
      SELECT [address_id], [address_trusted]
      FROM [applicant_address]
      WHERE @@ROWCOUNT = 1 AND [address_id] = scope_identity();

Why is it not being INSERTED? It selects the value back for some reason.

Rather than the default being used if I don't provide a value, it seems the default is being used to override my value!

Include provider and version information

EF Core version: 6.0.3 Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer) Target framework: 6.0.3 Operating system: Windows IDE: (e.g. Visual Studio 2022 17.1.5

[gatecrasher63]

If I remove the .HasDefaultValueSql line

                entity.Property(e => e.AddressTrusted)
                    .HasColumnName("address_trusted");
                    //.HasDefaultValueSql("((1))");

the trace shows

      INSERT INTO [applicant_address] ([address_country], [address_line_1], [address_line_2], [address_line_3], [address_post_code], [address_provice_or_state], [address_towncity], [address_trusted])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);
      SELECT [address_id]
      FROM [applicant_address]
      WHERE @@ROWCOUNT = 1 AND [address_id] = scope_identity();

[roji]

When a property contains the CLR default (0 for int, false for bool), EF Core treats it as "unset", and allows the database default to be generated; in your case, the database default is 1 (true).

You may want to make your property nullable (bool?); this would make null the "unset" value (which would allow the database-generated true to take effect), but still allow you to explicitly set the property to false.

Another option may be to simply set the property to true by default on the .NET side (e.g. in the constructor).

@ajcvickers should we add a doc note for bool+default value specifically?

[gatecrasher63]

Thanks for the reply, I had just come to the same conclusion myself. It needs a nullable type to work as expected

Instead however...

So I have removed the HasDefaultValueSQL line as I said above and assigned "true" to the field

public bool AddressTrusted { get; set; } = true;

in the class

That seems to be working. Is it the safe thing to do or should I leave that line in and make it nullable (it's a not null in the db)

[roji]

Putting this in 7.0 to consider documenting earlier, lots of people seem to be running into this (e.g. https://github.com/npgsql/efcore.pg/issues/2446).

[sccrgoalie1]

When a property contains the CLR default (0 for int, false for bool), EF Core treats it as "unset", and allows the database default to be generated; in your case, the database default is 1 (true).

You may want to make your property nullable (bool?); this would make null the "unset" value (which would allow the database-generated true to take effect), but still allow you to explicitly set the property to false.

Another option may be to simply set the property to true by default on the .NET side (e.g. in the constructor).

@ajcvickers should we add a doc note for bool+default value specifically?

@roji Is there a way to handle this for a nullable foreign key property with a default value?

public int? tblContactID { get; set; }
entity.Property(e => e.tblContactID).HasDefaultValueSql("((0))");

This is sent in as 0 when we set it to NULL. This is scaffolding from an existing database where I don't have control over the schema.

[gbirchmeier]

When a property contains the CLR default (0 for int, false for bool), EF Core treats it as "unset", and allows the database default to be generated; in your case, the database default is 1 (true).

That strikes me as a curious design decision. The difference between unset and CLR-default is and should be meaningful, so for EF to quietly unify that difference is pretty counter-intuitive.

If the C# var is non-nullable, then I don't get why EF is even doing an "unset" determination. It can't be unset! A zero should be a zero, false should be false. There is no ambiguity here.

I'm not surprised that "lots of people seem to be running into this", because I'm one of them.

[roji]

@gbirchmeier when you configure your column with a default database value, then it's critical that EF not send a value for that column when it isn't set; otherwise that value would always override whatever default is configured at the database, and the default would never take effect. That's why EF must decide whether a value is set or unset, even when it's non-nullable.

Note that in 8.0 we've made improvements to allow users to specify a different value to indicate "unset", so that it doesn't necessarily have to be the CLR default (e.g. 0).

[gbirchmeier]

it's critical that EF not send a value for that column when it isn't set; otherwise that value would always override whatever default is configured at the database, and the default would never take effect.

@roji Why is it critical? Why is it bad that my C# code could constantly clobber a DB column default with the model's (mandated to be non-null) value? Just because the DB has a column default 'xyz', doesn't mean my queries aren't allowed to explicitly set 'xyz'.

As I'm writing this comment, I think I might see what I'm missing: Is there some kind of partial-record-update situation that I'm not considering? Where the app code is updating a subset of columns that does not include this non-null-with-default column? I haven't had this kind of situation yet in my own projects, but now that I think about it, it's a scenario in which your position finally starts to make a little sense to me.

[brettzook]

Thanks @roji for the explanation.

Note that in 8.0 we've made improvements to allow users to specify a different value to indicate "unset", so that it doesn't necessarily have to be the CLR default (e.g. 0).

Can you point me to some documentation showing how this can be done? I'm not on 8.0 yet for my project but will be in the near future.

[ajcvickers]

@brettzook See https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#sentinel-values-and-database-defaults

[markookram]

@brettzook @roji I have a case with EF 8 that even having 0 as a value for PK (type long), its not been set as 'unsent' and in db im getting a row with Id = 0. Of course DB default value is not used. Do u maybe have some additional info? Sql server used is Microsoft SQL Server 2019 (RTM-GDR) (KB5029377) Im using SqlBulkCopy. Configuration:

builder.ToTable("TABLE_TEST", "dbo"); builder.HasKey(i => i.Id); builder.Property(i => i.Id) .HasColumnName("ID") .HasDefaultValueSql("NEXT VALUE FOR dbo.TestSeq");

It works using Context.Add()......SaveChangesAsync(); Is there anything that i can do to get the same results with SqlBulkCopy?

Tnx

[roji]

@markookram SqlBulkCopy is completely external to EF and isn't affected by EF configuration in any way. If your table was built by EF with the configuration above, then it should have a default value from TestSeq, at which point IIRC SqlBulkCopy should also use it, assuming you omit the Id in your import.

I'd advise looking at your actual table definition in the database (e.g. with SQL Server Management Studio) and thinking about this without taking EF into account.