dotnet
HasDefaultValueSql is overriding my actual supplied value it seems
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
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();
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?
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)
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).
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.
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.
@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).
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.
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.
@brettzook See https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#sentinel-values-and-database-defaults
