RevEng: numeric fields no longer get their default value stored in DbContext

[fschlaef]

Prior to 2.1.0-preview2, (maybe even preview1 but I'm not sure) numeric fields got their default value stored in DbContext by Scaffold-DbContext like this :

entity.Property(e => e.Number)
     .HasColumnName("number")
     .HasDefaultValueSql("((0))");

With 2.1.0-preview2, the same field gets scaffolded like this

entity.Property(e => e.Number).HasColumnName("number");

I don't know if this intentional, I've seen discussion related to default values reverse engineering here : #9627 but it only focuses on nullable booleans.

Steps to reproduce

Create a database with a numeric field (int, double, etc) and a default value.
Scaffold-DbContext this database

Further technical details

EF Core version: 2.1.0-preview2-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 7 x64
IDE: Visual Studio 2017 15.6.6

[smitpatel]

We decided to do that for few more simple types. See #9627 (comment)

[ajcvickers]

@fschlaef The reason is that if the database default is the same as the CLR default for the property, then when the property has the CLR default value, the result of sending that to the database is the same as not including it and the database default being used instead. Also, a lot of times the database default isn't there because there is an intention for EF to use it, but just because the database has been setup with those defaults for other reasons--again see #9627 linked by Smit.

Is this causing functional issues in your application? If so, it would be great to get more details.

[fschlaef]

@ajcvickers This is indeed causing a functional issue for us.

What we do is get the database schema (first list the entities with context.Model.GetEntityTypes(), foreach with entity.GetProperties(), plus getting type with field.PropertyInfo.PropertyType) and select the following properties :

Type = propertyType.Name,
field.IsNullable,
MaxLength = field.GetMaxLength(),
DefaultValue = field.Relational().DefaultValueSql,

This gives an object looking like this (formatted as JSON) :

"User": {
    "Id": {
      "Type": "Int32",
      "IsNullable": false,
      "MaxLength": null,
      "DefaultValue": null
    },
    "Account": {
      "Type": "String",
      "IsNullable": false,
      "MaxLength": 255,
      "DefaultValue": null
    },
    "Active": {
      "Type": "Boolean",
      "IsNullable": false,
      "MaxLength": null,
      "DefaultValue": null
    },
    "ConnectionTime": {
      "Type": "Int32",
      "IsNullable": false,
      "MaxLength": null,
      "DefaultValue": null
    },
    "Information": {
      "Type": "String",
      "IsNullable": true,
      "MaxLength": null,
      "DefaultValue": "('')"
    }
}

We then do some conversion and feed this to our Angular app. This allows us to dynamically handle our HTML <input> tags, keeping their required, maxlength and type attributes in sync with the database, so when I go and change the database I don't have to edit my HTML code and check that I didn't forget anything.

Bottom line is : for nvarchar fields, I know that if the field is not nullable but has a default value, I don't have to put a required attribute on the HTML input. But I can't do this for int or double because I don't have their default values (I have a couple dozen non-nullable integer fields in my database with ((0)) as the default value, so I don't want to force an input client-side for those).

You may ask : why not set these fields are nullable ? This is a valid point, but it's a design choice, mostly to not get buried under null checks.

I hope this long post is clear 😄

[ajcvickers]

@fschlaef Does every column have a default? If so, then it would be safe to assume that if there isn't a default set in the EF model, then it's because the default value is the same as the CLR default.

More generally, using the model configuration generated for EF Core to drive other processes is something we consider very secondary to the use of the model configuration for the EF Core runtime. So we are likely to decide what to include in the metadata based on how it impacts the EF Core runtime. For example, setting DefaultValueSql will change the runtime behavior, and the goal of reverse engineering is to make the runtime behavior of EF Core optimal, not to retain all information that was in the database verbatim--this is also discussed in #9627.

EF Core also used a convention-based system, and we generally don't reverse engineer something that would be inferred by convention.

We will discuss this as a team, but it seems dangerous to use the lack of explicit metadata to drive decisions outside of the EF space.

[fschlaef]

@ajcvickers No, every column does not have a default, because we want to actually force a user input on some of them.

I understand EF Core team's view on this subject however, as it is true that what I am trying to do is kinda "highjacking" EF Core to do something else.

I'll try to find another way to accomplish this.

Edit : using good ol' GetSchema("Columns") I was able to find the information I wanted. However, it seems that EF Core is somehow able to retrieve default values with a user that doesn't have the db_ddladmin role in SQL Server, and I don't know how it's possible given that I had to grant the permission to be able to do the same thing.

[ajcvickers]

Closing this after discussion in triage since the metadata model is designed to support the EF Core runtime, and the meaning of "HasDefaultValueSql" is that there is a default constraint in the database which the EF runtime should make use of. In this case, the change to reverse engineering was explicit in that the EF runtime should not make use of the default constraint.

@fschlaef EF doesn't use "GetSchema", it rather just queries the database metadata tables directly. Maybe this requires different permissions?