Scaffolding fails for some SQLite generated columns

jzebedee commented 1 year ago

In .NET 8 RC2, dotnet ef dbcontext scaffold fails on SQLite databases that contain generated columns.

Repro environment

EF Core version: 8.0.0-rc.2.23480.1
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: net8.0

Operating system: Windows 11 Version 22H2


PS C:\temp> [System.Environment]::OSVersion.Version

Major Minor Build Revision

10 0 22621 0

* IDE: `n/a`

## DB Schema

```sql
CREATE TABLE IF NOT EXISTS nodes (
    body TEXT,
    id   TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL UNIQUE
);

Repro project

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.0-rc.2.23480.1">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="8.0.0-rc.2.23480.1" />
  </ItemGroup>

</Project>

CLI output

PS C:\temp> dotnet ef --version
Entity Framework Core .NET Command-line Tools
8.0.0-rc.2.23480.1
PS C:\temp> sqlite3 repro.db "CREATE TABLE IF NOT EXISTS nodes (body TEXT, id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL UNIQUE);"
PS C:\temp> dotnet ef dbcontext scaffold "Data Source=repro.db" Microsoft.EntityFrameworkCore.Sqlite --verbose
Using project 'C:\temp\temp.csproj'.
Using startup project 'C:\temp\temp.csproj'.
Writing 'C:\temp\obj\temp.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\Zebedee\AppData\Local\Temp\tmpubpisq.tmp /verbosity:quiet /nologo C:\temp\temp.csproj
Writing 'C:\temp\obj\temp.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\Zebedee\AppData\Local\Temp\tmpmn0jlc.tmp /verbosity:quiet /nologo C:\temp\temp.csproj
Build started...
dotnet build C:\temp\temp.csproj /verbosity:quiet /nologo /p:PublishAot=false

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:01.23
Build succeeded.
dotnet exec --depsfile C:\temp\bin\Debug\net8.0\temp.deps.json --additionalprobingpath C:\Users\Zebedee\.nuget\packages --additionalprobingpath "C:\Program Files (x86)\Microsoft Visual Studio\Shared\NuGetPackages" --additionalprobingpath "C:\Program Files\dotnet\sdk\NuGetFallbackFolder" --runtimeconfig C:\temp\bin\Debug\net8.0\temp.runtimeconfig.json C:\Users\Zebedee\.dotnet\tools\.store\dotnet-ef\8.0.0-rc.2.23480.1\dotnet-ef\8.0.0-rc.2.23480.1\tools\net8.0\any\tools\netcoreapp2.0\any\ef.dll dbcontext scaffold "Data Source=repro.db" Microsoft.EntityFrameworkCore.Sqlite --assembly C:\temp\bin\Debug\net8.0\temp.dll --project C:\temp\temp.csproj --startup-assembly C:\temp\bin\Debug\net8.0\temp.dll --startup-project C:\temp\temp.csproj --project-dir C:\temp\ --root-namespace temp --language C# --framework net8.0 --nullable --working-dir C:\temp --verbose
Using assembly 'temp'.
Using startup assembly 'temp'.
Using application base 'C:\temp\bin\Debug\net8.0'.
Using working directory 'C:\temp'.
Using root namespace 'temp'.
Using project directory 'C:\temp\'.
Remaining arguments: .
Finding design-time services referenced by assembly 'temp'...
Finding design-time services referenced by assembly 'temp'...
No referenced design-time services were found.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.Sqlite'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.Sqlite'.
Finding IDesignTimeServices implementations in assembly 'temp'...
No design-time services were found.
To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see https://go.microsoft.com/fwlink/?LinkId=723263.
Found table with name: 'nodes'.
Found column on table 'nodes' with name: 'body', data type: TEXT, not nullable: False, default value: (null).
Found column on table 'nodes' with name: 'id', data type: TEXT, not nullable: True, default value: (null).
Querying table 'nodes' to determine an appropriate CLR type for each column.
Found unique constraint on table 'sqlite_autoindex_nodes_1' with name: nodes.
System.ArgumentException: The string argument 'sql' cannot be empty.
   at Microsoft.EntityFrameworkCore.Utilities.Check.NullButNotEmpty(String value, String parameterName)
   at Microsoft.EntityFrameworkCore.RelationalPropertyBuilderExtensions.HasComputedColumnSql(PropertyBuilder propertyBuilder, String sql, Nullable`1 stored)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitColumn(EntityTypeBuilder builder, DatabaseColumn column)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitColumns(EntityTypeBuilder builder, ICollection`1 columns)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTables(ModelBuilder modelBuilder, ICollection`1 tables)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseModel)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(DatabaseModel databaseModel, ModelReverseEngineerOptions options)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The string argument 'sql' cannot be empty.

jzebedee commented 1 year ago

It looks like #21557 was supposed to get generated column scaffolding working and #21588 is the blame source for the empty ComputedColumnSql.

ajcvickers commented 1 year ago

/cc @bricelam

bricelam commented 1 year ago

Hmm, looks like this was never actually fixed. Pandemic brain, I guess...

bricelam commented 1 year ago

We need to update RelationalScaffoldingModelFactory to call the parameterless overload of HasComputedColumnSql when it's empty on the db model.

dotnet / efcore