NullRef scaffolding Postgres DB

[mharward-gr]

When using EF Core scaffolding for an existing Postgres DB, I have started getting the following NullRef error. I have checked multiple versions of EF so I suspect a schema change - but I can't find any compelling changes that might cause it. Either way, shouldn't be getting a null ref.

The DB schema is provided by the open source application Waltz (https://github.com/finos/waltz).

I've reproduced this with a smaller subset of two tables in a couple of places in the schema (but definitely not affecting all tables with relationships).

Included is an example of just two tables that is throwing a null ref.

I've tried making public.person.employee_id not nullable, but that doesn't seem stop the error.

Would someone be able to take a look?

Command to scaffold

dotnet ef dbcontext scaffold "<connection-string>" Npgsql.EntityFrameworkCore.PostgreSQL -o DataModels -c WaltzDbCont
ext -p App1 -f --no-onconfiguring --no-build --table involvement --table person --verbose

Output

Using project '[Redacted]\App1\App1 .csproj'.
Using startup project '[Redacted]\App1\App1 .csproj'.
Writing '[Redacted]\App1\obj\App1.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\[Redacted]\AppData\Local\Temp\tmpFEB2.tmp /verbosity:quiet /nologo [Redacted]\App1\App1.csproj
Writing '[Redacted]\App1\obj\App1.csproj.EntityFrameworkCore.targets'...
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\[Redacted]\AppData\Local\Temp\tmp172.tmp /verbosity:quiet /nologo [Redacted]\App1\App1.csproj
dotnet exec --depsfile [Redacted]\App1\bin\Debug\net7.0\App1.deps.json --additionalprobingpath C:\Users\[Redacted]\.nuget\packages --additionalprobingpath "C:\Program Files (x86)\Microsoft Visual Studio\Shared\NuGetPackages" --additionalprobingpath "C:\Program Files\dotnet\sdk\N
uGetFallbackFolder" --runtimeconfig [Redacted]\App1\bin\Debug\net7.0\App1.runtimeconfig.json C:\Users\[Redacted]\.dotnet\tools\.store\dotnet-ef\7.0.4\dotnet-ef\7.0.4\tools\net6.0\any\tools\netcoreapp2.0\any\ef.dll dbcontext scaffold [... Redacted ...]
Using assembly 'App1'.
Using startup assembly 'App1'.
Using application base '[Redacted]\App1\bin\Debug\net7.0'.
Using working directory '[Redacted]\App1'.
Using root namespace 'App1'.
Using project directory '[Redacted]\App1\'.
Remaining arguments: .
Finding design-time services referenced by assembly 'App1'...
Finding design-time services referenced by assembly 'App1'...
No referenced design-time services were found.
Finding design-time services for provider 'Npgsql.EntityFrameworkCore.PostgreSQL'...
Using design-time services from provider 'Npgsql.EntityFrameworkCore.PostgreSQL'.
Finding IDesignTimeServices implementations in assembly 'App1'...
No design-time services were found.
Found column with table: public.person, column name: id, data type: bigint, nullable: False, identity: True, default value: (null), computed value: (null)
Found column with table: public.person, column name: employee_id, data type: character varying(128), nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: display_name, data type: character varying(255), nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: email, data type: character varying(255), nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: user_principal_name, data type: character varying(255), nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: department_name, data type: character varying(255), nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: kind, data type: character varying(255), nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: manager_employee_id, data type: character varying(128), nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: title, data type: character varying(255), nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: office_phone, data type: character varying(128), nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: mobile_phone, data type: character varying(128), nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: organisational_unit_id, data type: bigint, nullable: True, identity: False, default value: (null), computed value: (null)
Found column with table: public.person, column name: is_removed, data type: boolean, nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: public.involvement, column name: entity_kind, data type: character varying(128), nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: public.involvement, column name: entity_id, data type: bigint, nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: public.involvement, column name: employee_id, data type: character varying(128), nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: public.involvement, column name: provenance, data type: character varying(64), nullable: False, identity: False, default value: 'waltz'::character varying, computed value: (null)
Found column with table: public.involvement, column name: kind_id, data type: bigint, nullable: False, identity: False, default value: (null), computed value: (null)
Found column with table: public.involvement, column name: is_readonly, data type: boolean, nullable: False, identity: False, default value: true, computed value: (null)
Found unique constraint with name: unique_employee_id, table: public.person.
For foreign key inv_kind_to_inv_fk on table public.involvement, unable to model the end of the foreign key on principal table public.involvement_kind. This is usually because the principal table was not included in the selection set.
The column 'public.involvement.is_readonly' would normally be mapped to a non-nullable bool property, but it has a default constraint. Such a column is mapped to a nullable bool property to allow a difference between setting the property to false and invoking the default constraint. See https://go.microsoft.com
/fwlink/?linkid=851278 for details.
The principal end of the foreign key 'public.involvement(employee_id)' is supported by the unique index 'unique_employee_id' and contains the following nullable columns 'public.person.employee_id'. Entity Framework requires the properties representing those columns to be non-nullable.
System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.CSharpDbContextGenerator.TransformText()
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.CSharpModelGenerator.ProcessTemplate(ITextTransformation transformation)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.CSharpModelGenerator.GenerateModel(IModel model, ModelCodeGenerationOptions 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 useDataAnnot
ations, 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 useD
ataAnnotations, 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)
Object reference not set to an instance of an object.

This example is between person and involvement, have found the same between involvement and involvement_kind, but they are the only two examples I have been able to find from a fairly large schema - so might be something directly related to the involvement table - but processing this alone does not generate the error.

Provider and version information

EF Core version: 7.0.4 (have also tried 6.0.8, 6.0.15, 7.0,2 and this doesn't seem to make a difference)
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 7.0
Operating system: Windows 10
IDE: Terminal

dotnet ef --version
Entity Framework Core .NET Command-line Tools
7.0.4

[ErikEJ]

@mharward-gr Have you updated the t4 templates, some bugs in them were fixed in 7.0.3

[mharward-gr]

@ErikEJ thank you for the quick response - I don't have any t4 templates set up for this project. Is that something I need to add?

[ajcvickers]

@mharward-gr Can you post the schema? Or point to where I can get it from in the linked project?

[mharward-gr]

@ajcvickers Here are the create scripts for the two specific tables above:

-- Table: public.involvement

-- DROP TABLE IF EXISTS public.involvement;

CREATE TABLE IF NOT EXISTS public.involvement
(
    entity_kind character varying(128) COLLATE pg_catalog."default" NOT NULL,
    entity_id bigint NOT NULL,
    employee_id character varying(128) COLLATE pg_catalog."default" NOT NULL,
    provenance character varying(64) COLLATE pg_catalog."default" NOT NULL DEFAULT 'waltz'::character varying,
    kind_id bigint NOT NULL,
    is_readonly boolean NOT NULL DEFAULT true,
    CONSTRAINT inv_kind_to_inv_fk FOREIGN KEY (kind_id)
        REFERENCES public.involvement_kind (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT inv_to_person_fk FOREIGN KEY (employee_id)
        REFERENCES public.person (employee_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.involvement
    OWNER to "waltzsvc-owner";

COMMENT ON TABLE public.involvement
    IS 'defines a specific instance of an involvement kind between a person and an entity (e.g. ''User X'' is ''IT Owner'' for ''APPLICATION/32'')';

COMMENT ON COLUMN public.involvement.entity_kind
    IS 'the kind of entity the person is involved with';

COMMENT ON COLUMN public.involvement.entity_id
    IS 'the identifier of the entity the person is involved with';

COMMENT ON COLUMN public.involvement.employee_id
    IS 'reference to the person involved with the entity';

COMMENT ON COLUMN public.involvement.provenance
    IS 'where did this involvement record originate, will be ''waltz'' if provided via the UI';

COMMENT ON COLUMN public.involvement.kind_id
    IS 'the type of involvement between the person and the entity (e.g. ''IT Owner'')';

COMMENT ON COLUMN public.involvement.is_readonly
    IS 'can this involvement be edited/removed by users (e.g. set to true if externally mastered)';
-- Index: idx_involvement_entity_emp

-- DROP INDEX IF EXISTS public.idx_involvement_entity_emp;

CREATE INDEX IF NOT EXISTS idx_involvement_entity_emp
    ON public.involvement USING btree
    (entity_kind COLLATE pg_catalog."default" ASC NULLS LAST, entity_id ASC NULLS LAST, employee_id COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

-- Table: public.person

-- DROP TABLE IF EXISTS public.person;

CREATE TABLE IF NOT EXISTS public.person
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    employee_id character varying(128) COLLATE pg_catalog."default",
    display_name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    email character varying(255) COLLATE pg_catalog."default" NOT NULL,
    user_principal_name character varying(255) COLLATE pg_catalog."default",
    department_name character varying(255) COLLATE pg_catalog."default",
    kind character varying(255) COLLATE pg_catalog."default" NOT NULL,
    manager_employee_id character varying(128) COLLATE pg_catalog."default",
    title character varying(255) COLLATE pg_catalog."default",
    office_phone character varying(128) COLLATE pg_catalog."default",
    mobile_phone character varying(128) COLLATE pg_catalog."default",
    organisational_unit_id bigint,
    is_removed boolean NOT NULL DEFAULT false,
    CONSTRAINT person_pkey PRIMARY KEY (id),
    CONSTRAINT unique_employee_id UNIQUE (employee_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.person
    OWNER to "waltzsvc-owner";
-- Index: idx_person_email

-- DROP INDEX IF EXISTS public.idx_person_email;

CREATE INDEX IF NOT EXISTS idx_person_email
    ON public.person USING btree
    (email COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

The liquidbase schema can be found here for the latest waltz release if that is more useful: https://github.com/finos/waltz/releases/download/1.48/liquibase-scripts.zip

[roji]

@mharward-gr your script above references other missing tables (e.g. involvement_kind). Can you please submit a self-contained script that can be executed on an empty database and which reproduces the exception?

[mharward-gr]

Ah, sorry @roji, here is the create script for involvement_kind. I believe that the 3 tables (with involvement executed last), should be self contained. Hopefully that works.

-- Table: public.involvement_kind

-- DROP TABLE IF EXISTS public.involvement_kind;

CREATE TABLE IF NOT EXISTS public.involvement_kind
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    description character varying(4000) COLLATE pg_catalog."default" NOT NULL,
    last_updated_at timestamp without time zone DEFAULT now(),
    last_updated_by character varying(255) COLLATE pg_catalog."default" NOT NULL,
    external_id character varying(200) COLLATE pg_catalog."default",
    user_selectable boolean NOT NULL DEFAULT true,
    CONSTRAINT involvement_kind_pkey PRIMARY KEY (id),
    CONSTRAINT involvement_kind_name_key1 UNIQUE (name)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.involvement_kind
    OWNER to "waltzsvc-owner";

COMMENT ON TABLE public.involvement_kind
    IS 'defines a particular type of involvement a person may have in relation to an entity (e.g. IT Owner for an APPLICATION)';

COMMENT ON COLUMN public.involvement_kind.name
    IS 'display name for the involvement kind';

COMMENT ON COLUMN public.involvement_kind.description
    IS 'longer textual description of the involvement';

COMMENT ON COLUMN public.involvement_kind.external_id
    IS 'external identifier, typically used when external jobs are updating the associated involvements';

COMMENT ON COLUMN public.involvement_kind.user_selectable
    IS 'flag to allow users to add people to an entity with this involvement (set to false to restrict usage, i.e. if involvement is mastered in another system)';
-- Index: idx_inv_kind_ext_id_uniq

-- DROP INDEX IF EXISTS public.idx_inv_kind_ext_id_uniq;

CREATE UNIQUE INDEX IF NOT EXISTS idx_inv_kind_ext_id_uniq
    ON public.involvement_kind USING btree
    (external_id COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

[roji]

I could create the three tables, but scaffolding worked fine without any error... Can you please take another look?

[mharward-gr]

Hi @roji, thank you very much for looking at this.

This definitely seems to be a problem at my end - not with latest version of EF. With a clean project and clean DB, I have confirmed this happens for 7.0.2 and earlier, but does not happen with 7.0.4.
I think I got stuck because I didn't realise that on upgrading EF and before running scaffolding, I needed to have a successfully built version of the project.

[roji]

Duplicate of #29516