Creating a MySQL Database with EF Core in .NET 6.0

In this tutorial I will show how to create a simple .NET 6 console application that interacts with data in MySQL version 8.0.0 using Entity Framework. I will be using the official Connector/NET for Entity Framework driver. The NuGet package is MySql.EntityFrameworkCore Nuget located at https://www.nuget.org/packages/MySql.EntityFrameworkCore/. 

Instead of installing and running a MySQL instance on my computer, I will run MySQL in a docker container for simplicity.

Source Code: https://github.com/medhatelmasry/MySqlOnFire

Companion Video: https://youtu.be/wGb2IWlZNl4

This article assumes the following:

1. You have .NET 6.0 installed on your computer. 
2. You have Docker installed on your computer. 

Setting up the MySQL 8.0.0 container

To download the MySQL version 8.0.0 image from Docker Hub and run it on your local computer, type the following command from within a terminal window:

docker run -p 3333:3306 --name db -e MYSQL_ROOT_PASSWORD=secret -d mysql:8.0.0

This starts a container named 'db' that listens on port 3333 on your local computer. The root password is 'secret'.

To ensure that the MySQL container is running, type the following from within a terminal window:

docker ps

You will see a message similar to the following:

CONTAINER ID   IMAGE         COMMAND                  CREATED        STATUS        PORTS                    NAMES

53e55f4991df   mysql:8.0.0   "docker-entrypoint.s…"   45 hours ago   Up 45 hours   0.0.0.0:3333->3306/tcp   db

Creating a console app

In a working directory, run the following command to create a console application named MySqlOnFire using .NET 6.0:

dotnet new console -f net6.0 -o MySqlOnFire 

Change directory to the newly created folder with:

cd MySqlOnFire

There is only one NuGet package that is needed to talk to MySQL. At the time of writing this article, the version of the MySql.EntityFrameworkCore package that supports .NET 6 is 6.0.0-preview3.1. Add the package by typing the following command from within a terminal window:

dotnet add package MySql.EntityFrameworkCore -v 6.0.0-preview3.1

Model Classes

We will be modeling the following Publisher & Book entities:

In .NET 6.0, we can create a global using file. This helps keep our code minimal. Let us take advantage of this feature. Add a file named GlobalUsings.cs and add to it the following code:

global using System.Text;

global using Microsoft.EntityFrameworkCore; 

global using System.ComponentModel.DataAnnotations.Schema;

Create a file named Publisher.cs and add to it the following class code:

public class Publisher {

    public int PublisherId { get; set; }

    public string? Name { get; set; }

    public virtual ICollection<Book>? Books { get; set; }

    public override string ToString() {

        var txt = new StringBuilder();

        txt.AppendLine($"ID: {PublisherId}");

        txt.AppendLine($"Publisher: {Name}");

        

        return txt.ToString();

    }

}

In the above code, we define the properties of the Publisher class and a ToString() method.

Create another class named Book.cs and add to it the following class code:

public class Book {

    public string? ISBN { get; set; }

    public string? Title { get; set; }

    public string? Author { get; set; }

    public string? Language { get; set; }

    public int Pages { get; set; }

    public int PublisherId { get; set; }

    [ForeignKey("PublisherId")]

    public virtual Publisher? Publisher { get; set; }

    public override string ToString() {

        var txt = new StringBuilder();

        txt.AppendLine($"ISBN: {ISBN}");

        txt.AppendLine($"Title: {Title}");

        txt.AppendLine($"Author: {Author}");

        txt.AppendLine($"Language: {Language}");

        txt.AppendLine($"Pages: {Pages}");

        txt.AppendLine($"Publisher: {Publisher!.Name}");

        return txt.ToString();

    }

}

In the above code, in addition to Book properties, PublisherId is clearly declared as the foreign key and a ToString() method is also defined.

The database context class

Entity Framework requires us to define a DbContext class that becomes the entry point into the database. Therefore, create a new file named LibraryContext.cs and add to it the following code:

public class LibraryContext : DbContext {
   public DbSet<Book>? Books { get; set; }
   public DbSet<Publisher>? Publishers { get; set; }
   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
     optionsBuilder.UseMySQL("server=localhost;database=library;user=root;port=3333;password=secret");
   }
   protected override void OnModelCreating(ModelBuilder modelBuilder) {
     base.OnModelCreating(modelBuilder);
     modelBuilder.Entity<Publisher>(entity => {
         entity.HasKey(e => e.PublisherId);
         entity.Property(e => e.Name).IsRequired();
     });
     modelBuilder.Entity<Book>(entity => {
         entity.HasKey(e => e.ISBN);
         entity.Property(e => e.Title).IsRequired();
         entity.HasOne(d => d.Publisher)
         .WithMany(p => p!.Books);
     });
     modelBuilder.Entity<Publisher>().HasData(
         new Publisher {
             PublisherId = 1,
             Name = "Mariner Books"
         },
         new Publisher {
             PublisherId = 2,
             Name = "Penguin Books"
         }
     );
     modelBuilder.Entity<Book>().HasData(
         new Book {
             ISBN = "978-0544003415",
             Title = "The Lord of the Rings",
             Author = "J.R.R. Tolkien",
             Language = "English",
             Pages = 1216,
             PublisherId = 1
         },
         new Book {
             ISBN = "978-0547247762",
             Title = "The Sealed Letter",
             Author = "Emma Donoghue",
             Language = "English",
             Pages = 416,
             PublisherId = 1
         },
         new Book {
             ISBN = "978-0143107569",
             Title = "Les Miserables",
             Author = "Victor Hugo",
             Language = "English",
             Pages = 1456,
             PublisherId = 2
         },
         new Book {
             ISBN = "978-0140449174",
             Title = "Anna Karenina",
             Author = "Leo Tolstoy",
             Language = "English",
             Pages = 880,
             PublisherId = 2
         }
     );
   }
}

What does the above code do?

• Two DbSet objects are defined: Books & Publishers
• The connection string to our MySQL database running in a docker container is:

server=localhost;database=library;user=root;port=3333;password=secret

• The OnModelCreating() method establishes the following rules:
• PublisherId is the primary key for the Publisher entity
• Name is a required column in the Publisher entity
• ISBN is the primary key in the Book entity
• The Book entity has a foreign key into the Publisher entity
• The OnModelCreating() method also inserts publishers & books seed data

Finally, let us add the code to create the database, seed data, and print data. Replace your Program.cs file with:

CreateDbSeedData();

PrintPublishers();

PrintBooks();

static void CreateDbSeedData() {

    using (var context = new LibraryContext()) {

        // Creates the database if not exists

        context.Database.EnsureCreated();

    }

}

static void PrintBooks() {

    // Gets and prints all books in database

    using (var context = new LibraryContext()) {

        var books = context.Books!

          .Include(p => p.Publisher);

        Console.WriteLine(new string('=', 30));

        foreach (var book in books!) {

            Console.WriteLine(book);

        }

    }

}

static void PrintPublishers() {

    // Gets and prints all books in database

    using (var context = new LibraryContext()) {

        var data = context.Publishers;

        Console.WriteLine(new string('=', 30));

        foreach (var item in data!) {

            Console.WriteLine(item);

        }

    }

}

What does the above code do:

• The CreateDbSeedData() method creates the database and seeds sample data if it does not already exist.
• The two print methods (PrintPublishers() & PrintBooks()) are self-explanatory.
• The following methods are called at the top of Program.cs:

CreateDbSeedData();

PrintPublishers();

PrintBooks();

The moment of truth is here. Let us run the application with:

dotnet run

The output should look like this:

==============================

ID: 1

Publisher: Mariner Books

ID: 2

Publisher: Penguin Books

==============================

ISBN: 978-0140449174

Title: Anna Karenina

Author: Leo Tolstoy

Language: English

Pages: 880

Publisher: Penguin Books

ISBN: 978-0143107569

Title: Les Miserables

Author: Victor Hugo

Language: English

Pages: 1456

Publisher: Penguin Books

ISBN: 978-0544003415

Title: The Lord of the Rings

Author: J.R.R. Tolkien

Language: English

Pages: 1216

Publisher: Mariner Books

ISBN: 978-0547247762

Title: The Sealed Letter

Author: Emma Donoghue

Language: English

Pages: 416

Publisher: Mariner Books

 

Peeking inside the MySQL database

You can peek into the MySQL database inside the container. To get into a bash session inside the container, enter the following command:

docker exec -it db bash

This takes you into a a bash session inside the container that looks like this:

root@53e55f4991df:/#

To get into the MySQL command interface, enter the following:

mysql -uroot -psecret

You can check existing databases with:

show databases;

The output will resemble this:

+--------------------+

| Database           |

+--------------------+

| information_schema |

| library            |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.00 sec)

To look into the contents of our library database, run the following MySQL commands:

use library;

show tables;

select * from Publishers;

select * from Books;

To exit from the container interactive session and return to the host operating system, type exit twice.

Cleanup the running MySQL container

You can do the following to stop & remove the MySQL container:

docker rm -f db

Conclusion

This gives you the option to use yet another relational database with your .NET apps. Of course, you can use MySQL with ASP.NET, Blazor, gRPC, desktop or any other types of .NET applications.

References:

Creating a Database with Code First in EF Core