Efcore: EF Core 1.0 not including columns with default values in insert into query

[monicadelprado]

Steps to reproduce

I'm using EF Core 1.0 and I have a model where I design some properties with default values similar to
modelBuilder.Entity<Listings>(entity => { entity.Property(e => e.IsActive).HasDefaultValueSql("1");}
The table in the database is created correctly defining the columns with the corresponding default value
[IsActive] bit NOT NULL DEFAULT 1

The issue

When I add a new entity and save changes in my context, the insert into statement generated does not contain the properties that have default values even though I assign specific values for those properties. The result in the database is a record where the columns with default values doesn't contain the values I passed in my model.

More info on this post
http://stackoverflow.com/questions/40619319/entity-framework-not-including-columns-with-default-value-in-insert-into-query/

Further technical details

EF Core version: 1.0
Visual Studio version: VS 2015

[rowanmiller]

I think we made some bug fixes around this in 1.1. Could you upgrade to 1.1 and see if the issue is fixed? https://blogs.msdn.microsoft.com/dotnet/2016/11/16/announcing-entity-framework-core-1-1/

[monicadelprado]

I have upgraded to EF Core 1.1.0 and still have the issue

[ajcvickers]

@mdelprado-emphasys This looks like a dupe of #6054. If you need a bool with a store-generated default, then use a nullable bool. This gives EF a way to distinguish between not set (null) set to false and set to true.

[divega]

@ajcvickers do you think there is an opportunity here for a model validation error or warning?

[ajcvickers]

@divega Yes, I think that would be a good idea.

[gdoron]

@divega what kind of error or warning were you talking about?
And what about DateTime InsertDate with default value of GetUtcDate()? I really don't want to make InsertDate nullable.

BTW, I'm not sure what exactly did people expect to happen with boolean with default value of true... Did they expect EF to change the C# / CLR spec?

[divega]

@gdoron we need to do more analysis but I think the warning would only apply to Boolean properties.

When you specify that a property has a default value in the database, EF Core will use the default value of the CLR type as an indication that it should let the database set the default value, hence it won't include the column in the INSERT operation.

But Boolean properties only have two states, so it is unlikely that you would intend any of these two values to mean "leave the column alone":

• If you configure the default to be "1" it becomes impossible to insert a "0"
• Configuring the default to be "0" is pointless

[gdoron]

@divega

But Boolean properties only have two states ...

This is why I don't get what did they expect to happen, and they may expect a similar thing with numeric properties too, _I assigned my property 0 but it's being ignored._
(I'm assuming default(DateTime) wouldn't bother them as its value isn't _considered_ to be "valid")

[monicadelprado]

@gdoron , @divega It makes sense, now that you mention, that if I don't have a nullable type, the EF just adds the default value, but I was actually expecting the same behavior than when I do an INSERT INTO directly in the database. The INSERT INTO just adds the record with the values I send in the query and ignores the default values.

[gdoron]

@mdelprado-emphasys That's because SQL doesn't have the limitation there actually is a property that being sent.

[GertArnold]

IMO, when a property is not nullable, the HasDefaultValue(Sql) instruction should be ignored when it comes to setting property values, not for migrations. This gives users the opportunity to create the database schema as they like (i.e. with defaults), but they won't be surprised by this rather unexpected behavior. It's easier to understand that a non-nullable property always has a value (so setting a default by EF isn't necessary) than that a value is overwritten when it happens to have been set to the CLR default.

[gdoron]

@GertArnold It would be very annoying having DateTime? InsertDate all over the place just because people make mistakes.
But regardless, I believe it's too late for changing as it's a huge breaking change.
Maybe for EF Core Core. 😸

[GertArnold]

@gdoron I see your point. But people _are_ going to tack HasDefaultValue to required properties other than InsertDate and such. That's common practice, for example to be able to add required columns to existing tables. For any required property of which the type's default value makes sense (i.e. not DateTime, usually) this causes the insane behavior that an entity with (CLR) default values can't be stored correctly if the defined default differs from the CLR default. Unless ValueGeneratedNever is applied. Do we expect people to understand (or even know) all that? For one, it's not in in the docs on HasDefaultValue.

[divega]

There seems to be more useful information in this issue now than in the original #6054, so closing that one as a duplicate of this one.

[divega]

@GertArnold that is an interesting insight. I do agree that currently you need to understand a lot before you can make some of these scenarios work correctly.

However as @AndriySvyryd pointed out in a recent conversation, ValueGeneratedNever is not even the right solution in this case: if you were to set that, the value you configured in HasDefaultValue() or HasDefaultValueSql() would never be used.

Instead what we are looking at is to introduce a warning (on second thought, besides bool it should also probably apply to properties of enum types) which suggests making the property of a nullable type. If the property is nullable then the default value of the CLR type will be null, which is hopefully outside of the domain of values you intend to store in the database and hence a better choice of a "sentinel value" to indicate that the value will be generated in the store.

In fact in most cases if you have ValueGeneratedOnAdd and a nullable type it would probably make sense to make the property IsRequired(true) at the same time. We could even consider introducing this as a convention or otherwise include it in the warning mentioned above.

We have considered other options, e.g.:

• Having a way to configure a different "sentinel" value from the default value for the CLR type to decide whether the value should be sent to the database on INSERTs (I remember vaguely but I think we either had this and we removed it at some point or we were very close to implementing it and then we decided not to do so).
• Having a way to configure that the default CLR type should not be used as a sentinel, and that the decision to send the value to the database should be made merely on whether the property value is marked as temporary.

But these options seem to lead to a more complicated experience or mental model and not necessarily help as much as making the property of a nullable type.

cc @ajcvickers

[rowanmiller]

@ajcvickers to split this out into some separate actionable items

[ajcvickers]

Created #7163, #7165, and #7199.

[Allann]

@ajcvickers I understand your comment in #7347 but this is not a valid long term solution. Let me explain the scenario (nothing to do with migrations).

I have an entity, it has a required boolean IsActive property in the domain object that is used by the DbContext. However, in many cases this field is not included on dynamic objects that represent a subset of the domain (e.g. id, code, name) so when a save is performed (on a new dynamic object) I expect the IsActive to be set to true since it is not a nullable, but not a field in the object passed to the DbConext.

Another similar scenario would be a CreateDate audit field that can't be nullable but should be set by the database when inserted, or a LastChanged audit field when the record is created/updated, again can't be nullable.

In the case of migrations for existing tables. If I add a migration to add a new required column called bool IsActive, I instantly fall back into the same problem above. So I don't feel that this issue is closed, but instead redefines the meaning of Default values.

It is quite feasible to have a required field with a default, and supply a value on insert. If a value is supplied it should always be used, and only when a value is not supplied, should the default be used. This is what Default Values used to mean.

[divega]

@Allann I am not sure the scenario you are describing is what I have in mind, but consider that you can at the same time:

• make the property of type bool?
• configure the property as IsRequired()

That way the property on the entity can be null (meaning no value was supplied and the default specified in the model should be used on new inserted rows) but the column created will not be nullable.

[Allann]

@divega You are completely correct in that that will "work-around" the issue, however, I don't want the domain object to rely on a particular database mapping to work. I NEED the field to be not nullable as it is not allowed to be null. What you are suggesting is the field is allowed to be null at some stage, as long as it's set before being sent to the database. My viewmodel field is nullable, but that is a particular user/UX scenario and doesn't normally require these fields. But the dto and domain classes both have this set as bool not nullable.

Note: I use code first via a domain class and a separate mapping class. I would need to set the domain object field to nullable, and remember to set the mapping for that field to required. This would have been implied if the field remained non-nullable.

As an aside this is new in EF core as behaves differently in EF6. This means that many will have to change their code base when moving to EF core.

Much of our legacy code relies on primitive types with database defaults, having to change all these to nullable will be a huge task especially since this is a hidden side-effect and not expected behavior. If I set a value on a field, I expect that value to be inserted into the database and not have it overridden by a default constraint when inserted into the database.

If this is to be the "rule", then I highly recommend putting an exception in the database generation logic that states that ALL fields with defaults NEED to map to nullable properties. Either that, or ignore default values when set against non-nullable properties.

[Allann]

Isn't the answer to this issue simply:
_EF Core uses the default value if the column is not included in the insert statement or is null on a nullable column._

[ajcvickers]

@Allann With that rule, how does EF determine whether to include the value in the insert statement? Currently, EF includes the value if it set to something other than the CLR default for the property type.

[Allann]

@ajcvickers Sorry, I thought the rule was fairly clear. Let me try and rework that:

EF should only use the default if it is different to the CLR default like you say BUT ONLY when the property is not included in the insert statement OR is null on a nullable property.

[ajcvickers]

@Allann EF has to determine somehow which properties are included in the insert statement. How are you proposing that EF determine which properties to include in the insert statement?

[Allann]

@ajcvickers Correct me if I'm wrong but does EF not already determine which properties to include when it converts the objects into the SQL statement? In that process does the SQL generator not determine what the property type and values are?
I have no idea about how that process works, hence these questions, otherwise I'd attempt to make the appropriate rule changes and send a push request. I'm assuming it would be different for each provider type as SQL Server is not the same as SQLite, etc. If I knew where this code was I can have a look to try and understand your question better.
There is a rule somewhere now that inserts the default constraint value if one exists into the sql statement ignoring the actual object value passed, so to my reckoning, that part needs to change to include a couple of conditional statements comparing the actual value of the object and the default value and/or whether it's nullable. Does that make sense or is there something I'm just not understanding.

[ajcvickers]

@Allann Yes, EF does do this already. It looks at the value of each property. If that value is different from the CLR default, then the value is included in the insert statement. If the value is the CLR default, then it is not included in the insert statement. (This is only for properties configured with store default values and is simplified a bit to keep it clear.)

Examples:

• string property, value "foo" -> include in insert
• string property, value null -> do not include in insert
• int property, value 1 -> include in insert
• int property, value 0 -> do not include in insert
• int? property, value 0 -> include in insert
• int? property, value null -> do not include in insert
• bool property, value false -> do not include in insert
• bool property, value true -> include in insert
• bool? property, value false -> include in insert
• bool? property, value null -> do not include in insert

[Allann]

As I see it most of those examples are correct except:

• int property, value 0 -> do not include in insert
• bool property, value false -> do not include in insert

How do you ever insert the CLR default value into the database if these columns has a default value then? Should they be inserted and then updated with the CLR default?

Whenever the value is passed on a value type, regardless of whether a default constraint exist or not, it MUST be used. Only when the value is NULL should the default constraint value be used. Note that a partial object may be passed (dynamic) in which case the default constraint should also be used if it exists.

The issue has always been against value types with a value set, even if the value was set to the CLR Default.

An example:
public class TaxPayable
{
string Name = "ajvickers";
decimal Amount = 0M;
}

database table has a default constraint which is $10.00. When I pass the TaxPayable instance to the database saying you don't need to pay tax, the current logic will insert 10. VERY bad for you as you now owe the tax department $10 due to the default constraint. And worse still, the business logic that sent the request would be non the wiser.

[ajcvickers]

@Allann What you said is a pretty good summary of the discussion in this thread. The other side of the argument is that the current behavior works very well for many scenarios--consider store generated keys with nothing special done in the entity type. Issue #7199 was created to discuss adding the ability to always push values even if they are the CLR default. As of now we discussed and do not plan to add this feature, but we are always ready re-assess based on feedback.

[Allann]

Does store generated keys = identity column? As I understand keys, these are a very different attribute/scenario, not a default constraint. I have a lot of these in the project, but business rules/logic would normally insert these, not a database default constraint/function.

My only concern is that this redefines the legacy meaning of default constraints. And in the example above, I would have to know implicitly to perform an additional update to set the Amount value to zero after the insert.
This is my biggest concern and until this is rectified I can't in good conscience recommend that our project teams upgrade to EF core as they would need to do a LOT of testing on all aspects of the code base where default constraints exist in the database. This would be over and above the normal upgrade testing when taking new major version updates.
I have worked around the issue in my project and the team is aware of the current logic so can code around it accordingly.

[ajcvickers]

@Allann That's fair enough. However, I'm not sure what you are "upgrading" from. EF Core has always had this behavior. EF6 and previous versions of non-core EF are a different product with different behaviors and there should be no expectation of compatibility between EF Core and EF6. A lot of things will work the same; a lot of things will work differently. Moving from EF6 to EF Core should be considered a porting effort in the same vein as moving from EF6 to any other different data access technology.

[Allann]

Thanks, that is becoming more and more evident. Whilst the majority would be EF6 some other ADO from older applications would also be on the cards. However, regardless of the legacy technology, and they all do things differently, there are many common rules adhered to, one is what a default constraint means. Regardless of whether I'm using nHibernate, EF6, ServiceStack's ORM or ADO, they all treat a default CLR value on value types as a real value on insert.

[popcatalin81]

@ajcvickers @rowanmiller This has bitten me hard today !!!

After a long debugging session I've found to my (and the whole team's) surprise that for some reason we cannot insert new rows with the column Enabled=false in DB, The reason being the default (bit) value was 1 (True) in Db, and while we were inserting rows with Enabled = False they were with Enabled = True in DB.
Trust me this can drive a developer crazy!!! ( This is a maximum astonishment feature! )

I highly recommend you reconsider this behavior (of ignoring default CLR values for insert) and make it optional (a flag on context) and disabled by default.

[gdoron]

@popcatalin81 it's optional, and it's not just optional, you need to opt-in for this behaviour!
You specifically asked this behaviour from EF by using the DefaultValue feature.

Just remove your opt-ining code, and you'll get what you want.

[popcatalin81]

@gdoron it is "obscurely" optional by not including HasDefaultValueSql(), which is different from purely optional, totally unrelated and not intuitive. Also scaffolding generates by default this code: "entity.Property(e => e.Enabled).HasDefaultValueSql("1");" which makes this feature Opt-Out, not Opt-In!

[popcatalin81]

@gdoron Or if you're suggesing I've opted-in to not being able to insert rows with Enabled=false in DB. I never did that, was not warned about, and it was never clear EF will do that! This is contrary to what a regular developer using a fairly straightforward API would expect. This is as unintuitive as it gets, if you don't actually know about nitty grittly internal details of the API.

[Allann]

@gdoron with that logic, how do you set the column value to false? When you add a migration to add a new boolean column in an existing table, you must use default values, so that any existing rows are updated during the migration.

[gdoron]

@popcatalin81 and @Allann I hear what you say, but I yet heard a better way.
EF unlike T/SQL is limited by the CLR rules and if you have a not nullable bool, EF has no way to know if it's false because you didn't set it (and then your "default value"should be used instead) or because you explicit set it to false.

That is because EF core (currently) doesn't use proxies and I'm not sure if it is on the roadmap or even wanted to use proxies which has other and much bigger implications.

[popcatalin81]

@gdoron maybe this should not be based on actual value for all properties (with some exceptions like the Primary Key properties) but use an explicit API, something like ctx.ChangeTracker.Entry(entity).Property("Enabled").UseDefaultValueOnInsert() ... or similar.

[gdoron]

Now that's intuitive and going to be fun to use...! 😄
Sometimes you need to chose the "lesser evil", yes it's bad, but better than the alternative.

[popcatalin81]

So there's no problem if you do something like this:

ctx.Tutorials.Add(new Tutorial{Uri ="...", Name ="...", Enabled = true);
ctx.Tutorials.Add(new Tutorial{Uri ="...", Name ="...", Enabled = false);
ctx.Tutorials.Add(new Tutorial{Uri ="...", Name ="...", Enabled = false);
ctx.Tutorials.Add(new Tutorial{Uri ="...", Name ="...", Enabled = false);

ctx.SaveChanges();

Somewhere else:

// Only display enabled tutrials (should be only 1 right, I've just inserted them)
ctx.Tutorials.Where(t => t.Enabled);

Surpriseeee they are all enabled, but you look at the code, you inspect the ChangeTracker before save, you search for bugs in your code ... and finally when you inspect the generated SQL, you think it's a bug, then you look it up on google and then you find out it's not a bug it's a feature. A surprising feature in this particular context (because in others you expect it, IE: Identity column).

[ajcvickers]

@popcatalin81 Two things:

• #7163 adds a warning for this case.
• Once #7914 gets implemented, the default behavior for computed columns will be to always use the store-generated value if one is configured, rather than only using it if some non-CLR default value is set in the entity. Of course, this won't help you in this case because what you are really asking for is to not use the store-generated default ever. I.e. If the value is false, then insert false. If the value is true, then insert true. So really you need to tell us in the model builder than even though the column has a default value in the database, in reality you never want EF to allow it to be used.

[ajcvickers]

Correction: Actually #7914 doesn't apply in this case because this is a ValueGenerated.OnAdd (i.e. at insert time) column, and not a computed column.

[popcatalin81]

@ajcvickers in this case I didn't do anything, I've just generated an model based on an existing DB and proceeded to insert some data. The HasDefaultValueSql("1") was not added by me but by Scaffold-DbContext cmdlet, based on existing column default.

I understand what I need to do, but it's still incovenient to clean up after Scaffold-DbContext on dozens of locations after each time the model is generated.

[iJungleboy]

I just ran into this issue as well and it cost me a few hours of debugging. actually I'm still not sure if I can fix it - but scaffolding generated the HasDefaultValueSql("1") and this caused my draft-items to always be published :(

Edit: Removing that fixed my issue now. Very unintuitive and will cost a lot of frustration :(

[ewahner]

I guess I don't understand why this isn't treated as just a check constraint. In pure SQL if I have a table
create table Foo ( Id integer primary key (1, 1), IsActive bit not null default(1), name nvarchar(50))
And I do an insert insert into Foo ( name ) values ( 'Bar')
The result will be
Id = 1
IsActive = 1
name = 'Bar'
if I do an insert insert into Foo (name, isActive) values ('Fooey', 0)
The result will be
Id = 2
IsActive = 0
name = 'Fooey'

We need the ability to add a new field with a default value, so that when the new field is added it updates all the rows to match the default value and then we need the ability to override that default when we see fit. If the value is not present, meaning I created my Foo object but didn't set the IsActive property then I would expect the database constraint's default of 1 to take effect. Am I missing something here or did we just redefine what a sql default is?

[ajcvickers]

@ewahner The problem is in this statement, " I created my Foo object but didn't set the IsActive property." EF has to have some way of knowing whether or not the IsActive property is set. It makes the non-perfect but pragmatic choice that if the value is the CLR default, then it has not been set. For bool, this means that a value of false means not-set. But this means that false cannot be inserted. So for bool properties, either don't configure it as a store default in the model so that EF will insert false or true, or change the CLR type so that there is a specific sentinel value for not set--i.e. null.

[Allann]

i still don't understand why this is such a complex issue. If the client code provides a value, use it, like all databases have always done. if it isn't provided or is null, use the default configured for the column. When adding a new column to an existing table, it must have a default value so that the upgrade script knows what to do with existing records.
Why does EF 7 have to work differently to all other ORMs? and existing database behaviour?

[ajcvickers]

@Allann Could you post some code showing how this works with some other O/RM? (Not the internals of the O/RM, but just what code you would write on the client side to make it work.)

[Allann]

Simple OData using a partial insert

var product = await client
    .For<Products>()
    .Set(new { ProductName = "Test1", UnitPrice = 18m })
    .InsertEntryAsync());

https://github.com/object/Simple.OData.Client/wiki/Adding-entries

ef6 using anon type
LoginId - int, identity
UserId - int
LoginDateTime- Allow nulls false,default value getdate()

db.LoginRecords.Add(new LoginRecord() { UserId = UserId }); 
db.SaveChanges();

http://stackoverflow.com/a/22436753

nHibernate via class where mapping property definitions have been created

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
    session.Save(new Blog
    {
        CreatedAt = DateTime.Now,
        Title = "hello",
        Subtitle = "world",
    });
    tx.Commit();
}

http://stackoverflow.com/questions/1126610/how-can-nhibernate-use-the-database-default-value-when-saving-a-new-entity
https://ayende.com/blog/3936/nhibernate-mapping-property

This should do for now, I'm sure you can find hundreds of other examples using your favorite search engine. The point is, by populating an anon type with only the new/changed data, or using nullable properties (that remain null), the database takes over and supplies the default values. If the user wanted to add their own value, they just include that property value too. The engine should then use that value over the db default (always). only when the field is not supplied (or remains null) should the database default be used.

[popcatalin81]

The thing is, the default value mechanism for databases is a feature helping backward compatibility, it's not a feature to be used for regular crud operations.

The database does NOT use sentinel values to mean insert Default value here. Insert null means you want to insert null in the DB world, etc.

With this mechanism EF is effectively locking out the user of inserting some values IE: null, or false or 0 etc. That does not make sense.

The only ways to use insert a DB default should be either by:

• not mapping a column (IE: like a previous version of the app)
• with explicit API call, something like "IgnoreDuringInsert(x => x.Col)".

[Allann]

@popcatalin81 so what you are saying is that you shouldn't use default values to add Create/Modified dates when a record is added/changed by the database? They shouldn't be set by the client because the server time may be different. and what about a userid taken from the authenticated user? There are many cases when the database should be responsible for inserting default values. Another typical instance is creating a new record where a client only has access to partial insert functionality due to the bounded context, where the remaining data is inserted by default values for fields that cannot be null.
Why are we trying to redefine the long standing definition of what a database default value is?

SQL DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for a column.
The default value will be added to all new records IF no other value is specified.
https://www.w3schools.com/sql/sql_default.asp

Default values, in the context of databases, are preset values defined for a column type. Default values are used when many records hold similar data.
https://www.techopedia.com/definition/24362/default-values-database

You can specify a default value that will be entered in the column in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. If you do not assign a default value and the user leaves the column blank, then:
If you set the option to allow null values, NULL will be inserted into the column.
If you do not set the option to allow null values, the column will remain blank, but the user will not be able to save the row until they supply a value for the column.
https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-default-values-for-columns

[gdoron]

You are using .NET ORM and as such there are CLR limitations like this one.
EF has no way of knowing if Foo equals false because you set it to false or because you left it with the CLR default value.

I honestly not sure what's so complicated about this concept.

[iJungleboy]

@gdoron the concept isn't complicated - it just causes very unexpected behavior which hurts programmers. Of course if people would read all the documentation and already know about this, it's logical why it happens, but that still doesn't remove the problem.

Fact is, that people will not plan for this or assume it will happen, until in hits them unexpectedly. And that's bad. Basically part of the problem is simply that the insert wants to optimize for default values, resulting in an insert which is inconsistent with what the developer coded. That's very bad, and EF shouldn't do that.

[gdoron]

Suggest alternative.

[iJungleboy]

Here's a trivial alternative:
Don't try to optimize the insert-statement - I'm pretty sure we're not saving _any_ performance by optimizing it at all - the work to evaluate if it's the default, + the work the SQL must do to retrieve the default, will probably never be smaller than just sending the value along.

[gdoron]

I don't think you understood what I wrote about the CLR.
It has NOTHING to do with optimization ​but to (as mentioned by me and others many times already) limitations of the CLR.

This is becoming a lengthy and repetitive thread.

[iJungleboy]

@gdoron I understand, and I must admit I haven't read every single post here - because it is too long. Sorry about that. I often think git is missing a summary-feature at the beginning of the thread, where the admin could summarize the situation and everything.

[popcatalin81]

@Allann I'm saying you shouldn't use sentinel values to insert database defaults. I'm saying if you want (for legit reasons) to insert database defaults then either you do it by not mapping the columns in the model or with an explicit API.

[ajcvickers]

@Allann Your statement at the end, especially this part "using nullable properties (that remain null), the database takes over and supplies the default values" describes the behavior in EF Core.

The EF6 code doesn't really work like this. EF6 either always uses the default constraint (with linitations) or never uses the default constraint. There is no way to have some inserts for that property use it, while others do not. (Note that EF Core can be configured to do the same thing using StoreGeneratedAlways.)

From what I can tell, the nHibernate code works the same was as EF6--that is, it will always use the database constraint. If it doesn't, then it looks like it would work the same way as EF Core since there is no other information provided that could make it work otherwise.

@popcatalin81 I don't think that is an unreasonable interpretation, and there is a way to set this up with EF Core using StoreGeneratedAlways. However, we have had a lot of feedback over the years indicating that many developers want the ability to insert only when an explicit value has not been set.

[gdoron]

@ajcvickers since NHibernate uses proxies it actually can know if Foo equals false because it was never set or because it was set to false.
I have no idea if it does that or not (I haven't used it for more than 5 years now).

Maybe you can also define a new EF Boolean type (with implicit casting to Boolean), though I don't think it would be any easier and user friendly than what we have now.

[ajcvickers]

@gdoron I don't think the nHibernate code is using a proxy, or, if it is, it is created later and so wouldn't help here, because the code explicitly says new Blog....

I agree that the new Boolean type wouldn't be better than using a nullable bool.

[popcatalin81]

@ajcvickers

I think there are two different problems and mindsets at conflict here.
Example 1:

new Product
{
           Name = "Petunias",
           Price = 2.5,
           LastEditDate = null //Default getdate()
}

This is typical case where you'd want to use the database generate value.

Example 2:

new Agreement
{
           Type = "Care rental",
           ExpirationDate = null // Default getdate() + 30
}

You'd really like to be able to insert null here as it is significant for the logic.

Example 3:

new Message
{
           Content = "Hi there",
           IsVisible = "false" // default true
} 

You'd really want to be able to insert false here even if for some other reasons you had to choose the default to be true.

What I'm trying to say, database defaults diffent from NULL and the need to insert NULL are common but EF core makes this scenario (and others) somewhat difficult.

[ajcvickers]

@popcatalin81 Agreed that being able to sometimes insert null for a given property, but sometimes have the value be store-generated for the same property is something EF Core can't do. We explicitly scoped that out some time ago. This is the first time I have heard someone ask for it. 😸

The other cases are all doable in various ways--using nullable properties or StoreGeneratedAlways.

[popcatalin81]

This is the first time I have heard someone ask for it.

I always was an early adopter, and used newest EF versions even in brown fields (dating back to EF 1 Beta 1 days ....) Just wait for it ... 😄

[John0King]

I totally don't get why this could be an issue. let .HasDefaultValue() only work for migration, and let the real model default value (eg. CLR default) use Property initializer 。
Database's defalut value is about add a default value when u missing the column . and when u specific that column ,then you can event add null to it.
with ORM this can happen too, when you add a column to the database first , and forget update the model .
Why EF Core want to make a column we already know(in our model) treat as a missing column ?

[hisuwh]

Just want to weigh in as this issue has just stung us. Unsure why it has been closed.
We have the same scenario - added a default value of true to a boolean property so that when the migration was applied existing records would get the value of true.

Completely unexpected behaviour that this would make it impossible to set false when inserting a record.