# Adding Entity Framework for Blog Posts (Part 1, Setting up the Database)

<!--category-- ASP.NET, Entity Framework -->
<datetime class="hidden">2024-08-11T04:53</datetime>

Buckle in because this will be a long one!

You can see parts 2 and 3 [here](/blog/addingentityframeworkforblogpostspt2) and [here](/blog/addingentityframeworkforblogpostspt3).

## Introduction
While I've been happy with my file based approach to blogging, as an excercise I decided to move to using Postgres for storing blog posts and comments. In this post I'll show how that's done along with a few tips and tricks I've picked up along the way.


[TOC]

## Setting up the Database
Postgres is a free database with some great features. I'm a long time SQL Server user (I even ran performance workshops at Microsoft a few years back) but Postgres is a great alternative. It's free, open source, and has a great community; and PGAdmin, to tool for administering it is head and shoulders above SQL Server Management Studio.

To get started, you'll need to install Postgres and PGAdmin. You can set it up either as a windows service or using Docker as I presented in a previous post on [Docker](/blog/dockercomposedevdeps).

## EF Core
In this post I'll be using Code First in EF Core, in this way you can manage your database entirely through code. You can of course set up the database manually and use EF Core to scaffold the models. Or of course use Dapper or another tool and write your SQL by hand (or with a MicroORM approach).

The first thing you'll need to do is install the EF Core NuGet packages. Here I use:
- Microsoft.EntityFrameworkCore - The core EF package
- Microsoft.EntityFrameworkCore.Design - This is needed for the EF Core tools to work
- Npgsql.EntityFrameworkCore.PostgreSQL - The Postgres provider for EF Core

You can install these packages using the NuGet package manager or the dotnet CLI.

Next we need to think about the models for the Database objects; these are distinct from ViewModels which are used to pass data to the views. I'll be using a simple model for the blog posts and comments.

```csharp
public class BlogPost
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    public string Title { get; set; }
    public string Slug { get; set; }
    public string HtmlContent { get; set; }
    public string PlainTextContent { get; set; }
    public string ContentHash { get; set; }

    
    public int WordCount { get; set; }
    
    public int LanguageId { get; set; }
    public Language Language { get; set; }
    public ICollection<Comments> Comments { get; set; }
    public ICollection<Category> Categories { get; set; }
    
    public DateTimeOffset PublishedDate { get; set; }
    
}
```

Note that I've decorated these with a couple of attributes

```csharp
 [Key]
 [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
 ```
These let EF Core know that the Id field is the primary key and that it should be generated by the database.

I also have Category

```csharp
public class Category
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<BlogPost> BlogPosts { get; set; }
}
```

Languages
```csharp
public class Language
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<BlogPost> BlogPosts { get; set; }
}
```
And comments
```csharp
public class Comments
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Comment { get; set; }
    public string Slug { get; set; }
    public int BlogPostId { get; set; }
    public BlogPost BlogPost { get; set; } 
}
```

You'll see I refer to the BlogPost in Comments, and ICollections of Comments and Categories in B;ogPost. These are navigation properties and is how EF Core knows how to join the tables together.

## Setting up the DbContext
In the DbContext class you'll need to define the tables and relationships. Here's mine:

<details>
<summary>Expand to see the full code</summary>

```csharp
public class MostlylucidDbContext : DbContext
{
    public MostlylucidDbContext(DbContextOptions<MostlylucidDbContext> contextOptions) : base(contextOptions)
    {
    }

    public DbSet<Comments> Comments { get; set; }
    public DbSet<BlogPost> BlogPosts { get; set; }
    public DbSet<Category> Categories { get; set; }

    public DbSet<Language> Languages { get; set; }


    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder
            .Properties<DateTimeOffset>()
            .HaveConversion<DateTimeOffsetConverter>();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BlogPost>(entity =>
        {
            entity.HasIndex(x => new { x.Slug, x.LanguageId });
            entity.HasIndex(x => x.ContentHash).IsUnique();
            entity.HasIndex(x => x.PublishedDate);

            entity.HasMany(b => b.Comments)
                .WithOne(c => c.BlogPost)
                .HasForeignKey(c => c.BlogPostId);

            entity.HasOne(b => b.Language)
                .WithMany(l => l.BlogPosts).HasForeignKey(x => x.LanguageId);

            entity.HasMany(b => b.Categories)
                .WithMany(c => c.BlogPosts)
                .UsingEntity<Dictionary<string, object>>(
                    "BlogPostCategory",
                    c => c.HasOne<Category>().WithMany().HasForeignKey("CategoryId"),
                    b => b.HasOne<BlogPost>().WithMany().HasForeignKey("BlogPostId")
                );
        });

        modelBuilder.Entity<Language>(entity =>
        {
            entity.HasMany(l => l.BlogPosts)
                .WithOne(b => b.Language);
        });

        modelBuilder.Entity<Category>(entity =>
        {
            entity.HasKey(c => c.Id); // Assuming Category has a primary key named Id

            entity.HasMany(c => c.BlogPosts)
                .WithMany(b => b.Categories)
                .UsingEntity<Dictionary<string, object>>(
                    "BlogPostCategory",
                    b => b.HasOne<BlogPost>().WithMany().HasForeignKey("BlogPostId"),
                    c => c.HasOne<Category>().WithMany().HasForeignKey("CategoryId")
                );
        });
    }
}
```
</details>

In the OnModelCreating method I define the relationships between the tables. I've used the Fluent API to define the relationships between the tables. This is a bit more verbose than using Data Annotations but I find it more readable.

You can see that I set up a couple of Indexes on the BlogPost table. This is to help with performance when querying the database; you should select the Indices based on how you'll be querying the data. In this case hash, slug, published date and language are all fields I'll be querying on.

### Setup
Now we have our models and DbContext set up we need to hook it into the DB. My usual practice is to add extension methods, this helps keep everything more organised:
```csharp
public static class Setup
{
    public static void SetupEntityFramework(this IServiceCollection services, string connectionString)
    {
        services.AddDbContext<MostlylucidDbContext>(options =>
            options.UseNpgsql(connectionString));
    }

    public static async Task InitializeDatabase(this WebApplication app)
    {
        try
        {
            await using var scope = 
                app.Services.CreateAsyncScope();
            
            await using var context = scope.ServiceProvider.GetRequiredService<MostlylucidDbContext>();
            await context.Database.MigrateAsync();
            
            var blogService = scope.ServiceProvider.GetRequiredService<IBlogService>();
            await blogService.Populate();
        }
        catch (Exception e)
        {
            Log.Fatal(e, "Failed to migrate database");
        }        
    }
}
```

Here I set up the database connection and then run the migrations. I also call a method to populate the database (in my case I'm still using the file based approach so I need to populate the database with the existing posts).

Your connection string will look something like this:
```json
 "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=Mostlylucid;port=5432;Username=postgres;Password=<PASSWORD>;"
  },
```
Using the extension approach means that my Program.cs file is nice and clean:
```csharp
services.SetupEntityFramework(config.GetConnectionString("DefaultConnection") ??
                              throw new Exception("No Connection String"));

//Then later in the app section

await app.InitializeDatabase();
```

The section below is responsible for running the migration and actually setting up the database. The `MigrateAsync` method will create the database if it doesn't exist and run any migrations that are needed. This is a great way to keep your database in sync with your models.

```csharp
     await using var scope = 
                app.Services.CreateAsyncScope();
            
            await using var context = scope.ServiceProvider.GetRequiredService<MostlylucidDbContext>();
            await context.Database.MigrateAsync();
```

## Migrations
Once you have all this set up you need to create your initial migration. This is a snapshot of the current state of your models and will be used to create the database. You can do this using the dotnet CLI (see [here](https://learn.microsoft.com/en-us/ef/core/cli/dotnet) for details on installing the dotnet ef tool if needed):
```bash
dotnet ef migrations add InitialCreate
```

This will create a folder in your project with the migration files. You can then apply the migration to the database using:
```bash
dotnet ef database update
```

This will create the database and tables for you.