Output #nullable disable in reverse-engineered model

[HeinPauwelyn]

I'm using EF Core 3.1.9 and .NET Core 3.1. In my database I've some nvarchars that could be null. For example:

create table Person(
    Id int identity not null,
    Name nvarchar(max) not null,
    Phone nvarchar(max)
);

insert into Person (Name, Phone)
values ('Hein', null);

The I'll scaffold the C# code using database first. I've got this class:

public partial class Person 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Phone { get; set; }
}

and this code inside the DBContext

modelBuilder.Entity<Project>(entity =>
{
    entity.Property(e => e.Id).IsRequired();
    entity.Property(e => e.Name).IsRequired();
});

But when I want to select everything from the table I've this error:

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

at Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull()
at Microsoft.Data.SqlClient.SqlBuffer.get_String()
at Microsoft.Data.SqlClient.SqlDataReader.GetString(Int32 i)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.<MoveNextAsync>d__17.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__64`1.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToListAsync>d__64`1.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at XXX.PersonsRepo.<GetPersonssAsync>d__0.MoveNext() in C:\Projecten\XXX\XXX\XXX\Data\PersonsRepo.cs:line 22

Fix

I could solve this exception by using this code:

public partial class Person 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string? Phone { get; set; }
}

verbose output

PM> Scaffold-DbContext -Provider Microsoft.EntityFrameworkCore.SqlServer -Connection "<connection string>" -Force -verbose
Using project 'XXX.Logic'.
Using startup project 'XXX.Logic'.
Build started...
Build succeeded.
C:\Program Files\dotnet\dotnet.exe exec --depsfile C:\Projecten\XXX.Logic\bin\Debug\netcoreapp3.1\XXX.Logic.deps.json --additionalprobingpath C:\Users\XXX\.nuget\packages --additionalprobingpath "C:\Program Files\dotnet\sdk\NuGetFallbackFolder" --runtimeconfig C:\Projecten\XXX.Logic\bin\Debug\netcoreapp3.1\XXX.Logic.runtimeconfig.json C:\Users\XXX\.nuget\packages\microsoft.entityframeworkcore.tools\3.1.9\tools\netcoreapp2.0\any\ef.dll dbcontext scaffold "<connection string>" Microsoft.EntityFrameworkCore.SqlServer --json --force --verbose --no-color --prefix-output --assembly C:\Projecten\XXX.Logic\bin\Debug\netcoreapp3.1\XXX.Logic.dll --startup-assembly C:\Projecten\XXX.Logic\bin\Debug\netcoreapp3.1\XXX.Logic.dll --project-dir C:\Projecten\XXX.Logic\ --language C# --working-dir C:\Projecten\XXX\XXX --root-namespace XXX.Logic
Using assembly 'XXX.Logic'.
Using startup assembly 'XXX.Logic'.
Using application base 'C:\Projecten\XXX.Logic\bin\Debug\netcoreapp3.1'.
Using working directory 'C:\Projecten\XXX.Logic'.
Using root namespace 'XXX.Logic'.
Using project directory 'C:\Projecten\XXX.Logic\'.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Finding design-time services referenced by assembly 'XXX.Logic'.
No referenced design-time services were found.
Finding IDesignTimeServices implementations in assembly 'XXX.Logic'...
No design-time services were found.
Found default schema dbo.
Found type alias with name: sys.sysname which maps to underlying data type nvarchar(128).
Found table with name: dbo.Person.
Found column with table: dbo.Person, column name: Id, ordinal: 1, data type: sys.nvarchar, maximum length: 8, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: dbo.Person, column name: Name, ordinal: 2, data type: sys.nvarchar, maximum length: 60, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: dbo.Person, column name: FirstName, ordinal: 3, data type: sys.nvarchar, maximum length: 40, precision: 0, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: dbo.Person, column name: Phone, ordinal: 4, data type: sys.nvarchar, maximum length: 60, precision: 0, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: dbo.Person, column name: Mobile, ordinal: 5, data type: sys.nvarchar, maximum length: 60, precision: 0, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: dbo.Person, column name: Email, ordinal: 6, data type: sys.nvarchar, maximum length: 100, precision: 0, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: dbo.Person, column name: Title, ordinal: 7, data type: sys.nvarchar, maximum length: 200, precision: 0, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: dbo.Person, column name: Language, ordinal: 8, data type: sys.nvarchar, maximum length: 2, precision: 0, scale: 0, nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: dbo.Person, column name: ObsoleteDate, ordinal: 9, data type: sys.datetime2, maximum length: 8, precision: 27, scale: 7, nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: dbo.Person, column name: RequiredTimeRegistration, ordinal: 12, data type: sys.bit, maximum length: 1, precision: 1, scale: 0, nullable: True, identity: False, default value: (null), computed value: (null)

Provider and version information

EF Core version: 3.1.9
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10
IDE: Visual Studio 2019

[roji]

Duplicate of #15520

[roji]

Unfortunately EF Core's scaffolding isn't yet aware of nullable reference types (NRT) - #15520 tracks that. This is why your scaffolded model contains string Phone instead of string? Phone, even though the column is nullable in the database. However, at runtime, when EF builds the model, it does treat Phone as a non-nullable property, which is why it does not expect any nulls to come back from the database.

The workaround until #15520 is done is to manually annotate nullable reference properties by adding the question mark as you've done.

See this doc section on required/optional properties, and also this page for NRT in general.

[ajcvickers]

Note from triage: @roji to prepare a 3.1 patch to port #21190

[roji]

Submitted #23053 to patch 3.1.