[ASP.NET Core][Entity Framework Core][Npgsql] Try code first & DB first
Tags: aspnetcore, entityframeworkcore
Published: 2020-07-19 17:25:04

## 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
```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
```csharp
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
```csharp
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
```csharp
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
```csharp
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](https://docs.microsoft.com/en-us/ef/core/get-started/?tabs=netcore-cli)

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](https://www.nuget.org/packages/dotnet-ef/)

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](https://docs.microsoft.com/en-us/dotnet/core/tools/dotnet-add-package)

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](https://www.npgsql.org/efcore/)
* [EF Core tools reference (.NET CLI) - EF Core | Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet)

### 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
```csharp
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
```csharp
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
```csharp
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.