[ASP.NET Core][Entity Framework Core][Npgsql] Try code first & DB first

Intro

This time I will try Entity Framework Core.

I want to create Database tables from code("Code first"). And to add Entity Framework Core controls into a project what has already had Database, I wanna to know how to generate codes from Database("DB first").

Environments

• .NET 5
• Microsoft.EntityFrameworkCore: ver.5.0.0-preview.6.20312.4
• Microsoft.EntityFrameworkCore.Design: ver.5.0.0-preview.6.20312.4
• Npgsql.EntityFrameworkCore.PostgreSQL: ver.5.0.0-preview6

Code first

Create project

I creat a empty ASP.NET Core project.

dotnet new empty -n CodeFiirstSample

And I add NuGet packages.

• Microsoft.EntityFrameworkCore
• Npgsql.EntityFrameworkCore.PostgreSQL

Samples

After that, I add some model classes and connection strings.

appsettings.Development.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": "Host=localhost;Port=5432;Database=WorkflowSample;Username=postgres;Password=XXX"
}

Models/Workflow.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Models
{
    [Table("Workflow")]
    public class Workflow
    {
        // Primary Key
        [Key]
        // Auto increament
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set;}
        // not null
        [Required]
        public int ProductId { get; set; }
        [Required]
        [Column("CreateUserMail", TypeName="text")]
        public string CreateUserMail { get; set;}
        [Column(TypeName="timestamp with time zone")]
        public DateTime? CirculationLimit { get; set; }
        [Column(TypeName="timestamp with time zone")]
        // Set current time 
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime LastUpdateDate { get; set; }

        public List<WorkflowReader> Readers {get; set; }
    }
}

Models/WorkflowReader.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Models
{
    public class WorkflowReader
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        [ForeignKey("Workflow")]
        [Required]
        public int WorkflowId { get; set; }
        [Required]
        public string Name{ get; set; }

        public Workflow Workflow {get; set; }
    }
}

Models/CodeFirstSampleContext.cs

using Microsoft.EntityFrameworkCore;

namespace Models
{
    public class CodeFirstSampleContext: DbContext
    {
        public CodeFirstSampleContext(DbContextOptions<CodeFirstSampleContext> options)
            : base(options)
        {

        }
        public DbSet<Workflow> Workflows { get; set; }
        public DbSet<WorkflowReader> WorkflowReaders { get; set; }
    }
}

Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Models;

namespace CodeFirstSample
{
    public class Startup
    {
        private readonly IConfiguration configuration;
        public Startup(IHostEnvironment env)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json", false, true)
                .AddJsonFile($"appsettings.{env.EnvironmentName}.json", false, true)
                .AddEnvironmentVariables();
            configuration = builder.Build();
        }
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ProofreadingWorkflowContext>(options =>
                options.UseNpgsql(configuration["ConnectionStrings"]));
        }

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapGet("/", async context =>
                {
                    await context.Response.WriteAsync("Hello World!");
                });
            });
        }
    }
}

Add the first migration file

Now I start trying create Database by code.

• Getting Started - EF Core | Microsoft Docs

In "Code first" way, I create tables by the first migration.

To add migration file, I install a dotnet tool.

dotnet tool update --global dotnet-ef --version 5.0.0-preview.6.20312.4

• NuGet Gallery | dotnet-ef 3.1.6

According to the document, I should install "Microsoft.EntityFrameworkCore.Design" by "dotnet add package Microsoft.EntityFrameworkCore.Design".

But because I use .NET5 version, I was occurred an error.

"dotnet add" installs stable version, so it installs version 3.1.6.

• dotnet add package command - .NET Core CLI | Microsoft Docs

But generating migration files needs ver.5.

So I must install "Microsoft.EntityFrameworkCore.Design" by NuGet package manager.

Generated migration file

Now I can generate migration files.

dotnet ef migrations add InitialCreate

The command add three files in "Migrations" directory.

• 20200719144103_InitialCreate.cs
• 20200719144103_InitialCreate.Designer.cs
• WorkflowSampleContextModelSnapshot.cs

And I can update Database by update command.

dotnet ef database update

When the datatable has already existed

If the database has already had the datatable, I get error when I execute update command.

So in "Code first" way, I should drop all tables or use "DB first".

DB first

I also can generate codes from existed Database.

Create a project

I create a empty ASP.NET Core project again.

dotnet new empty -n DbFiirstSample

And I add NuGet packages.

• Microsoft.EntityFrameworkCore
• Npgsql.EntityFrameworkCore.PostgreSQL

Generate

Because I have already installed "dotnet-ef".

So I just execute command.

dotnet ef dbcontext scaffold "Host=localhost;Port=5432;Database=WorkflowSample;Username=postgres;Password=XXX" Npgsql.EntityFrameworkCore.PostgreSQL -d -o Models -n Models

• Getting Started | Npgsql Documentation
• EF Core tools reference (.NET CLI) - EF Core | Microsoft Docs

About options

I think I should add at least these three options.

• -d(--data-annotations): To add data annotations.
• -o(--output-dir): To decide output files directory. Default directory is root of the project.
• -n(--namespace): To decide namespace of classes. Default namespace is "DbFiirstSample.Models"

Generated codes

Models/Workflow.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

namespace Models
{
    public partial class Workflow
    {
        public Workflow()
        {
            WorkflowReaders = new HashSet<WorkflowReaders>();
        }
        [Key]
        public int Id { get; set; }
        public int ProductId { get; set; }
        [Required]
        public string CreateUserMail { get; set; }
        [Column(TypeName = "timestamp with time zone")]
        public DateTime? CirculationLimit { get; set; }
        [Column(TypeName = "timestamp with time zone")]
        public DateTime LastUpdateDate { get; set; }
        [InverseProperty("Workflow")]
        public virtual ICollection<WorkflowReader> WorkflowReaders { get; set; }
    }
}

Models/WorkflowReaders.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

namespace Models
{
    [Index(nameof(WorkflowId))]
    public partial class WorkflowReaders
    {
        [Key]
        public int Id { get; set; }
        public int WorkflowId { get; set; }
        [Required]
        public string Name{ get; set; }
        [ForeignKey(nameof(WorkflowId))]
        [InverseProperty("WorkflowReaders")]
        public virtual Workflow Workflow { get; set; }
    }
}

Models/WorkflowSampleContext.cs

using Microsoft.EntityFrameworkCore;

namespace Models
{
    public partial class WorkflowSampleContext : DbContext
    {
        public WorkflowSampleContext()
        {
        }

        public WorkflowSampleContext(DbContextOptions<WorkflowSampleContext> options)
            : base(options)
        {
        }

        public virtual DbSet<WorkflowReaders> WorkflowReaders { get; set; }
        public virtual DbSet<Workflow> Workflow { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning 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 http://go.microsoft.com/fwlink/?LinkId=723263.
                optionsBuilder.UseNpgsql("Host=localhost;Port=5432;Database=WorkflowSample;Username=postgres;Password=XXX");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

They aren't an exact match for "code first".

Especially, they don't have "DatabaseGenerated" annotations.

So maybe I should add some annotations manually after generating codes.