1

I worked with EF Core and SQL Server using database first approach. It is Ok for me to use dotnet-ef CLI for reverse engineering both tables and views. It preserves nullability of select columns from table in view when reverse engineering.

I.E, I have the following SQL Server schema:

CREATE TABLE [dbo].[Terminal] (
    [Id] int NOT NULL IDENTITY,
    [Tid] nvarchar(max) NOT NULL,
    [ActivedAt] datetime2 NULL,
    CONSTRAINT [PK_Terminal] PRIMARY KEY ([Id])
);
CREATE OR ALTER VIEW [dbo].[TerminalView]
WITH SCHEMABINDING
AS
    SELECT [t].[Id],
        [t].[Tid],
        ISNULL(CAST(IIF([t].[ActivedAt] IS NULL, 0, 1) AS BIT), 0) AS [IsActive]
    FROM [dbo].[Terminal] AS [t];

When reverse engineering this schema, I get:


public partial class Terminal
{
    public int Id { get; set; }

    public string Tid { get; set; } = null!;

    public DateTime? ActivedAt { get; set; }
}

public partial class TerminalView
{
    public int Id { get; set; }

    public string Tid { get; set; } = null!;

    public bool IsActive { get; set; }
}

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace Npgsql.Scaffolding.Lab.Models;

public partial class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Terminal> Terminals { get; set; }

    public virtual DbSet<TerminalView> TerminalViews { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Terminal>(entity =>
        {
            entity.ToTable("Terminal");

            entity.Property(e => e.Id).HasColumnName("Id");
            entity.Property(e => e.ActivedAt).HasColumnName("ActivedAt");
            entity.Property(e => e.Tid).HasColumnName("Tid");
        });

        modelBuilder.Entity<TerminalView>(entity =>
        {
            entity
                .HasNoKey()
                .ToView("TerminalView");

            entity.Property(e => e.Id)
                .ValueGeneratedOnAdd()
                .HasColumnName("Id");
            entity.Property(e => e.IsActive).HasColumnName("IsActive");
            entity.Property(e => e.Tid).HasColumnName("Tid");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

When I migrated to PostgreSQL schema:

CREATE TABLE IF NOT EXISTS public.terminal
(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    tid text COLLATE pg_catalog."default" NOT NULL,
    actived_at timestamp with time zone,
    CONSTRAINT "PK_terminal" PRIMARY KEY (id)
)

CREATE OR REPLACE VIEW public.terminal_view
WITH (
    check_option=cascaded
) AS
 SELECT id,
    tid,
    actived_at IS NOT NULL AS is_active
   FROM terminal;

When I reverse engineering, TerminalView nullability is not persist. All properties now become nullable.

public partial class TerminalView
{
    public int? Id { get; set; }

    public string? Tid { get; set; }

    public bool? IsActive { get; set; }
}

Did anyone meet this kind of issue? Any solution?

0

1 Answer 1

0

The columns in PostgreSQL views are always displayed as nullable, no matter what. There is no way to change that (save to change PostgreSQL). A view is just a query with a name — it is the underlying table that enforces constraints.

I'd say that is a problem of your tool, which was probably written to exploit that special feature of Microsoft SQL Server.

Sign up to request clarification or add additional context in comments.

3 Comments

``` select * from information_schema.columns where table_name = 'terminal'; select * from information_schema.columns where table_name = 'terminal_view'; ``` When I run this script, I see that all column of terminal_view has is_nullable = YES. My tool reverse engineering nullability based on it. Can you check again on you system
Sorry, I meant to say "nullable", not "NOT NULL". But I see that you found the duplicate yourself. Essentially, there is nothing you can do about that.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.