Use MySQL Connector/NET for Entity Framework driver with ASP.NET Core 5.0 Web App

Although the current version of ASP.NET is 6.0, I discovered that the official MySQL EF Core driver for .NET 6.0, at this time, does not work very well. Therefore, I decided to write this article for ASP.NET 5.0 Core. The NuGet package is MySql.EntityFrameworkCore Nuget located at https://www.nuget.org/packages/MySql.EntityFrameworkCore/. 

Source code: https://github.com/medhatelmasry/MySqlWeb

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

This article assumes the following:

1. You have .NET 5.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

Note: if you are using macOS with the M1 chip, you can use this docker image instead:

docker run -d -p 3333:3306 --name db -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_ROOT_HOST=% mysql/mysql-server:latest-aarch64

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 an ASP.NET 5.0 Core MVC web app

In a working directory, run the following command to create an ASP.NET MVC application named MySqlWeb using .NET 5.0:

dotnet new mvc -f net5.0 --auth individual -o MySqlWeb

This creates a web application that uses the SQLite database for individual authentication. Instead of SQLite, we will use MySQL instead.

Change directory to the newly created folder with:

cd MySqlWeb 

Firstly, let us remove support for SQLite by removing the appropriate package with this command:

dotnet remove package Microsoft.EntityFrameworkCore.Sqlite

Next, let us add two packages that will provide us with MySQL support using the Connector/NET for Entity Framework driver:

dotnet add package Microsoft.EntityFrameworkCore.Design -v 5.0.14

dotnet add package MySql.EntityFrameworkCore -v 5.0.10

Replace the connection string value for DefaultConnection in appsettings.json to the following pertaining to our MySQL database:

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

Open Startup.cs in an editor and change 'options.UseSqlite' to 'options.UseMySQL' around line 31.

Entity Framework Migrations

The migration files that were created in the /Data/Migrations folder contain commands for creating SQLite artifacts. These are not valid in our situation because we will be using MySQL and not SQLite. Therefore, delete the Migrations folder under /Data and create new migrations with the following command:

dotnet-ef migrations add m1 -o Data/Migrations

Next, let us apply these migrations to the database with:

dotnet-ef database update

Test our app

Now, let us test our web app and see whether or not it is able to talk to the containerized MySQL database server. 

Run the web application with the following terminal command:

dotnet run

If all goes well, you will see a message that indicates that the web server is listening on port numbers 5000 and 5001 (SSL). Point your browser to https://localhost:5001. Click on the Register link on the top right-side.

Enter an email address, password and confirm password then click on the Register button:

The website then displays the following page that requires that you confirm the email address:

Click on the “Click here to confirm your account” link. This leads you to a confirmation page:

Login with the email address and password that you registered with. You will see a welcome message in the top right-side indicating that you are logged-in.

This proves that your email and password was saved and that the connection to the MySQL database works as expected.

Happy coding.

docker-compose with MySQL pomelo driver and ASP.NET 7.0

It is customary to develop ASP.NET with either SQL Server or SQLite databases. How about if you want to use the popular MySQL database server? This article discussed one approach to making this possible by having your ASP.NET 7.0 development environment connect with MySQL running in a docker container.

Source code: https://github.com/medhatelmasry/AspMySQL-docker-compose

This article assumes the following:

1. You have .NET 7.0 installed on your computer. 
2. You have Docker Desktop installed on your computer.
3. You have the dotnet-ef tool installed. 

Let's get started.

Setting up the mariadb:10.7.3 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 mariadb:10.7.3

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 like the following:

CONTAINER ID   IMAGE         COMMAND                  CREATED        STATUS        PORTS                    NAMES

67335fb804e4   mariadb:10.7.3                     "docker-entrypoint.s…"   5 seconds ago   Up 3 seconds   0.0.0.0:3333->3306/tcp

Creating our ASP.NET 7.0 MVC App

The first step is to create a working directory somewhere on your computer's hard drive. I did so by creating a folder named AspMySQL with the following terminal command:

mkdir AspMySQL

Thereafter, go into the new folder with:

cd AspMySQL

We will create an ASP.NET 7.0 application that uses individual authentication with the following command:

dotnet new mvc -f net7.0 --auth individual

To run the web application and see what it looks like, enter the following command:

dotnet run

You will see a message in the terminal window that resembles the following:

info: Microsoft.Hosting.Lifetime[14]

      Now listening on: https://localhost:7042

info: Microsoft.Hosting.Lifetime[14]

      Now listening on: http://localhost:5035

info: Microsoft.Hosting.Lifetime[0]

      Application started. Press Ctrl+C to shut down.

info: Microsoft.Hosting.Lifetime[0]

      Hosting environment: Development

info: Microsoft.Hosting.Lifetime[0]

      Content root path: /Users/medhatelmasry/AspMySQL/

The above message indicates that the Kestrel web server is running and listening on port 7042. The port number you get is probably different. Start your browser with the appropriate URL. You should see a page that looks like this:

Look into the root folder of the project, you will find a file named app.db. This is an SQLite database file. The web application that we scaffolded is configured to work with SQLite. We will change it so that it works with the popular MySQL database instead. Go ahead and delete app.db.

Close your browser and stop the web server in the terminal window by hitting CTRL + C.

There is only one NuGet package that is needed to talk to MySQL. Add the package by typing the following command from within a terminal window:

dotnet add package Pomelo.EntityFrameworkCore.MySql -v 7.0.0

Let us configure our web application to use MySQL instead of SQLite. Open the Program.cs file in your favourite editor and comment out (or delete) the following statements found at around line 8:

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

builder.Services.AddDbContext<ApplicationDbContext>(options =>

    options.UseSqlite(connectionString));

Replace the above code with the following:

var host = builder.Configuration["DBHOST"] ?? "localhost";

var port = builder.Configuration["DBPORT"] ?? "3333";

var password = builder.Configuration["DBPASSWORD"] ?? "secret";

var db = builder.Configuration["DBNAME"] ?? "test-db";

var user = builder.Configuration["DBUSER"] ?? "root";

string connectionString = $"server={host}; userid={user}; pwd={password};"

        + $"port={port}; database={db};SslMode=none;allowpublickeyretrieval=True;";

var serverVersion = new MySqlServerVersion(new Version(10,7,3));

builder.Services.AddDbContext<ApplicationDbContext>(options =>

    options.UseMySql(connectionString, serverVersion));

Five environment variables are used in the database connection string. These are: DBHOST, DBPORT , DBPASSWORD, DBNAME and DBUSER. If these environment variables are not found then they will take up default values: localhost, 3333, secret, test-db and test-dbroot respectively.

Entity Framework Migrations

The migration files that were created in the /Data/Migrations folder contain commands for creating SQLite artifacts. These are not valid in our situation because we will be using MySQL and not SQLite. Therefore, delete the Migrations folder under /Data and create new migrations with the following command:

dotnet-ef migrations add M1 -o Data/Migrations

We can instruct our application to automatically process any outstanding Entity Framework migrations. This is done by adding the following statement to Program.cs right before the last app.Run() statement:

using (var scope = app.Services.CreateScope()) {

    var services = scope.ServiceProvider;

    var context = services.GetRequiredService<ApplicationDbContext>();    

    context.Database.Migrate();

}

Test our app

Now, let us test our web app and see whether or not it is able to talk to the containerized MySQL database server. 

Run the web application with the following terminal command:

dotnet run

If all goes well, you will see a message that indicates that the web server is listening on some random port number. Point your browser to http://localhost:???? (where ???? is your port number). The same web page will appear as before. Click on the Register link on the top right side.

I entered an Email, Password and Confirm password, then clicked on the Register button. The website then displays the following page that requires that you confirm the email address:

Click on the “Click here to confirm your account” link. This leads you to a confirmation page:

Login with the email address and password that you registered with.

The message on the top right side confirms that the user was saved and that communication between the ASP.NET MVC app and MySQL is working as expected.

Dockeri-zing solution

We will generate the release version of the application by executing the following command from a terminal window in the root directory of your web app:

dotnet publish -o dist

The above command instructs the dotnet utility to produce the release version of the application in the dist directory.

If you inspect the dist directory, you will see content similar to the following:

The highlighted file in the above image is the main DLL file that is the entry point into the web application.

Let us run the release version of the web app. To do this, change to the dist directory with the following terminal instruction:

cd dist

You can then run your main DLL file. In my case, this file is AspMySQL.dll. I executed the following command:

dotnet AspMySQL.dll

This displays the familiar messages from the web server that the app is ready to be accessed from a browser. Hit CTRL C to stop the web server.

We now have a good idea about what ASP.NET 7.0 artifacts need to be copied into a container. We shall simply copy contents of the dist directory into a Docker image that has the .NET 7.0 runtime.

Stop the web server by hitting CTRL C in the terminal window.

Also, in a terminal window, stop and remove the MySQL container with:

docker rm -f db

Return to the root directory of your project by typing the following in a terminal window:

cd ..

We need to create a docker image that will contain the .NET 7.0 runtime. A suitable image for this purpose is: mcr.microsoft.com/dotnet/aspnet:7.0

Create a text file named Dockerfile and add to it the following content:

FROM mcr.microsoft.com/dotnet/aspnet:7.0

COPY dist /app

WORKDIR /app

          ENV ASPNETCORE_URLS http://+:80

EXPOSE 80/tcp

ENTRYPOINT ["dotnet", "AspMySQL.dll"]

Above are instructions to create a Docker image that will contain our ASP.NET application. I describe each line below:

FROM mcr.microsoft.com/dotnet/aspnet:7.0	Base image mcr.microsoft.com/dotnet/aspnet:7.0 will be used
COPY dist /app	Contents of the dist directory on the host computer will be copied to directory /app in the container
WORKDIR /app	The working directory in the container is /app
EXPOSE 80/tcp	Port 80 will be exposed in the container
ENTRYPOINT ["dotnet", "AspMySQL.dll"]	The main ASP.NET web app will be launched by executing "dotnet AspMySQL.dll"

We will next compose a docker yml file that orchestrates the entire system which involves two containers: a MySQL database server container and a container that holds our application. In the root folder of your application, create a text file named docker-compose.yml and add to it the following content:

version: '3.8'

volumes:

  datafiles:

services:

  db:

    image: mariadb:10.7.3 

    volumes:

      - datafiles:/var/lib/mysql

    #restart: always

    environment:

      MYSQL_ROOT_PASSWORD: secret

      MYSQL_TCP_PORT: 3306

  webapp:

    build:

      context: .

    depends_on:

      - db

    ports:

      - "8888:80"

    #restart: always

    environment:

      - DBHOST=db

      - DBPORT=3306

      - DBPASSWORD=secret

      - DBNAME=bingo-db

      - ASPNETCORE_ENVIRONMENT=Development

 

Below is an explanation of what this file does.

We will be having two containers. Each container is considered a service. The first service is named db and will host MySQL. The second service is named webapp and will host our ASP.NET web app.

The most current version of docker-compose is version 3.8. This is the first line in our docker-compose file.

The MySQL Container

Image mariadb:10.7.3 will be used for the MySQL container.

A volume named datafiles is declared that will host MySQL data outside of the container. This ensures that even if the MySQL container is decommissioned, data will not be lost.

restart: always is so that if the container stops, it will be automatically restarted.

The root password will be secret when MySQL is configured. This is set by the MYSQL_ROOT_PASSWORD environment variable.

The ASP.NET Web Application Container

The container will be built using the instructions in the Dockerfile file and the context used is the current directory.

depends_on indicates that the web app relies on the MySQL container (db) to properly function.

Port 80 in the mvc container is mapped to port 8888 on the host computer.

The environment variables needed by the web app are:

DBHOST	points to the MySQL service
DBPORT	Should be set to 3306 because it is the depault port # that MySQL listens on
DBPASSWORD	this is the root password for MySQL
DBNAME	We shall call the database for our web app bingo
ASPNETCORE_ENVIRONMENT	set to Development more. In reality, you should change this to Production one you determine that your web app container works as expected.

Running the yml file

To find out if this all works, go to a terminal window and run the following command:

docker-compose up

Point your browser to http://localhost:8888/ and you should see the main web page. 

NOTE: If you cannot view the home page, check that the web container is running. If it is stopped then start it with docker start …

To ensure that the database works properly, register a user by clicking on the Register link in the top right corner.

You will then receive a “Register Confirmation”:

Click on the “Click here to confirm your account” so that the app accepts the email address that was used. On the “Confirm Email” page. 

Login with the account you created.

As you can see in the top-right corner, the user with email a@a.a has been successfully registered.

This opens a whole new world for containerizing your ASP.NET web apps.

Cleanup

Hit CTRL C in the terminal window to stop docker-compose, then run the following command:

docker-compose down

Build REST API using EF with Azure Functions v3 & .NET Core 3.1

In this tutorial I will demonstrate how to build a REST API application using Azure Functions v3. The application we will build together uses Entity Framework Core Migrations, Dependency Injection and .NET Core 3.1. We will use the light-weight VS Code editor so that you can go through this tutorial on Windows 10, Mac or Linux.

Source code: https://github.com/medhatelmasry/AzureFunctionsEF.git

Install the appropriate Azure CLI for your operating system from https://docs.microsoft.com/en-us/cli/azure/install-azure-cli.

You need to install the Azure Functions extension for Visual Studio Code before proceeding with this tutorial. Once the extension is installed, you will find it among your extensions.

In your working directory, create the following folder structure:

Inside the AzureFunctionsEF directory, execute the following terminal window commands:

dotnet new sln

dotnet new classlib -f netcoreapp3.1 -o DataLayer

dotnet sln add DataLayer/DataLayer.csproj

Start Visual Studio Code.  Under the Functions tab, select your Azure subscription. You will then be able to create a new Azure Functions project.

Choose the AzureFunctionsEF/Functions folder.

Select C#.

Select HttpTrigger.

Name your Azure function HttpWebAPI.

Give your function app the namespace Snoopy.Function.

Select Anonymous for access rights.

Finally, select "Open in current window".

Let us see what the app does. Enter the following in the terminal window inside the AzureFunctionsEF directory:

cd Functions

func start

The following will appear:

Copy the URL (http://localhost:7071/api/HttpWebAPI) and paste it in the address line of your browser. You will see a response like this:

The message in your browser suggests that you should pass a name query string. I appended the following to the URL: ?name=Superman. I got the following result:

Hit CTRL C to terminate the running app.

Back in the terminal window inside the AzureFunctionsEF directory, execute the following commands:

dotnet sln add Functions/Functions.csproj

dotnet add Functions/Functions.csproj reference DataLayer/DataLayer.csproj

DataLayer class library project

We will work on the DataLayer project by adding a Student class, an Entity Framework database context class, a connection string, and a class that is capable of reading configurations settings.

Add the following packages to the DataLayer project by executing the following commands in a terminal window inside the DataLayer folder:

dotnet add package Microsoft.EntityFrameworkCore -v 3.1.9

dotnet add package Microsoft.EntityFrameworkCore.Tools -v 3.1.9

dotnet add package Microsoft.EntityFrameworkCore.Design -v 3.1.9

dotnet add package Microsoft.EntityFrameworkCore.SqlServer -v 3.1.9

dotnet add package Microsoft.Extensions.Configuration.Json -v 3.1.9

dotnet add package Microsoft.Extensions.Configuration.EnvironmentVariables -v 3.1.9 

Open the DataLayer folder in Visual Studio Code.

Delete DataLayer/Class1.cs.

Add a Models folder. Inside the Models folder, add a C# Student class file with the following code:

public class Student {

  public string StudentId { get; set; }

  [Required]

  public string FirstName { get; set; }

  [Required]

  public string LastName { get; set; }

  [Required]

  public string School { get; set; }

}

Also, in the Models folder, add another C#  Config class file with the following code:

public static class Config {

    private static IConfiguration configuration;

    static Config() {

        var builder = new ConfigurationBuilder()

            .SetBasePath(Directory.GetCurrentDirectory())

            .AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)

            .AddEnvironmentVariables();

        configuration = builder.Build();

    }

    public static string Get(string name) {

        string appSettings = configuration[name];

        return appSettings;

    }

}

Now let us add a settings file named local.settings.json right inside the root DataLayer folder with the following content:

{

  "DefaultConnection":"Server=(localdb)\\mssqllocaldb;Database=SchoolDB;Trusted_Connection=True;MultipleActiveResultSets=true"

}

Since we are using Entity Framework, we need to add a database context class. Add the following ApplicationDbContext.cs class to the DataLayer root directory:

public class ApplicationDbContext : DbContext {

  public DbSet<Student> Students { get; set; }

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

  public ApplicationDbContext(DbContextOptions options) : base(options) { }

  public ApplicationDbContext() : base() { }

  protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseSqlServer(Config.Get("DefaultConnection"));

  protected override void OnModelCreating(ModelBuilder builder) {

    base.OnModelCreating(builder);

    builder.Entity<Student>().HasData(

      new {

        StudentId = Guid.NewGuid().ToString(),

        FirstName = "Jane",

        LastName = "Smith",

        School = "Medicine"

      }, new {

        StudentId = Guid.NewGuid().ToString(),

        FirstName = "John",

        LastName = "Fisher",

        School = "Engineering"

      }, new {

        StudentId = Guid.NewGuid().ToString(),

        FirstName = "Pamela",

        LastName = "Baker",

        School = "Food Science"

      }, new {

        StudentId = Guid.NewGuid().ToString(),

        FirstName = "Peter",

        LastName = "Taylor",

        School = "Mining"

      }

    );

  }

}

Now that we have out models and database context classes in place, let us go ahead and run Entity Framework Migrations. In a terminal window inside the DataLayer root folder, execute the following commands:

dotnet-ef migrations add M1 -o Data/Migrations

dotnet-ef database update

At this point, if all goes well, the database would be created and seeded with sample data.

Azure Functions Project

Add the following package to the Functions project by executing the following command in a terminal window inside the Functions folder:

dotnet add package Microsoft.Extensions.Http -v 3.1.10

Open the Functions folder in Visual Studio Code.

Add the following connection string to local.settings.json.

"DefaultConnection":"Server=(localdb)\\mssqllocaldb;Database=SchoolDB;Trusted_Connection=True;MultipleActiveResultSets=true",

We will use dependency injection to access the database context and HttpClient objects. Therefore, create a Startup.cs file in the root Functions folder and add to it this code:

[assembly: WebJobsStartup(typeof(StartUp))]

namespace Functions {

  public class StartUp : IWebJobsStartup {

    public void Configure(IWebJobsBuilder builder) {

      builder.Services.AddDbContext<ApplicationDbContext>(options1 => {

        options1.UseSqlServer(

          Config.Get("DefaultConnection"),

          builder =>

          {

            builder.EnableRetryOnFailure(5, TimeSpan.FromSeconds(10), null);

            builder.CommandTimeout(10);

          }

        );

      });

      builder.Services.AddHttpClient();

    }

  }

}

Delete the static keyword from the class declaration of HttpWebAPI class:

public static class HttpWebAPI

We will use dependency injection inside our Functions class to access the database context and HttpClient objects. Therefore, add these instance variables and constructor to HttpWebAPI.cs:

private readonly HttpClient _client;

private readonly ApplicationDbContext _context;

public HttpWebAPI(IHttpClientFactory httpClientFactory,

    ApplicationDbContext context) {

    _client = httpClientFactory.CreateClient();

    _context = context;

}

Add the following method to the HttpWebAPI.cs class:

[FunctionName("GetStudents")]

public IActionResult GetStudents(

[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "students")] HttpRequest req,

ILogger log) {

  log.LogInformation("C# HTTP GET/posts trigger function processed a request.");

  var studentsArray = _context.Students.OrderBy(s => s.School).ToArray();

  return new OkObjectResult(studentsArray);

}

All that is left for us to find out is whether or not our API works. Hit CTRL F5 inside the Visual Studio Code.

CTRL Click with your mouse on http://localhost:7071/api/students. It should open up a browser window with the following data:

It is left up to you to complete this tutorial with POST, PUT and DELETE functionality.

Using EF 3.0 Core database provider for Azure CosmosDB with ASP.NET MVC

Azure Cosmos DB is Microsoft's globally distributed, multi-model database service. You can interact with the database using four favorite API including SQL, MongoDB, Cassandra, Tables, or Gremlin. In this tutorial we will be accessing Azure Cosmos DB using the SQL API.

A native Entity Framework Core provider for Azure Cosmos DB was released with .NET Core 3.0. This makes it much easier for developers who are familiar with EF Core to work with Azure Cosmos DB.

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

Video: https://youtu.be/VklO8olYTL8 

Prerequisites

Before you can start this tutorial, it is assumed that you have the following:

1) At the time of writing, the latest version of .NET Core is 3.1. As a result, I am using .NET Core 3.1. You can, however, use and version of .NET Core as long as it is 3.0 or later.

2) You need to have an Azure account in order to work with Azure Cosmos DB.

3) In order to make this tutorial work on any operating system (Linux, Mac and Windows), I will be using the VS Code editor.

Prior knowledge of ASP.NET MVC is a big bonus because I will not explain the what the roles and responsibilities of Models, Views and Controllers in this article.

Creating a database in Azure Cosmos DB

The first step is to create a database in on Azure Cosmosdb. Login into your Azure account by going to https://portal.azure.com. Click on the "Create a resource" button.


In the filter field, enter the word "cosmos" the select "Azure Cosmos DB".


On the next page, click on the blue Create button.


On the "Create Azure Cosmos DB Account" page:

1) Create a new resource group. I named my resource group CosmosEfWeb-RG.

2) Give your Cosmos DB an account name. I named mine cosmos-ef-web. Not that the name needs to be all in lower case letters.

3) Do not change the API. To use Entity Framework Core we must use "Core (SQL)".

4) Finally, choose the location closest you. Since I reside on the west coast of Canada, I chose "(US) West US 2".



Click on the "Review + create" button. The next page will look like this:


Note the message that indicates that it will take about six minutes to create the account. Click on the blue Create button.

Once the provisioning is completed, you will see a page that looks like this.



Click on the blue "Go to resource" button. Click on "Data Explorer" on the left-side navigation.



Click on the "New Container" dropdown-list then select "New Database".


Give the database a name. I named my database "CollegeDB" because I will soon create an MVC app that does CRUD operations with student data.


Click on the blue OK button. After the database is created, click on the three dots on the right-side of the database then select "New Container". A container is equivalent to a collection or table in a database.


Note that this is where you can delete the entire database.

Enter a container-id. I named my container students because I will be adding a list of students. I added a partition key of /id.

Note: The Partition Key is used to automatically partition data among multiple servers for scale-ability. Choose a JSON property name that has a wide range of values and is likely to have evenly distributed access patterns.

Click on the blue OK button.

At this point our database is ready and we can start populating it with data with an application that we are about to build. We need, however, some credentials. Click on Keys on the left-side menu.



On the next page, you will need the URI and the "PRIMARY KEY". Copy both values and park them in a text editor.



We can now start developing our application.

Building an ASP.NET Core MVC app.

Open a terminal window in a working directory on your computer.

Type the following command to determine which version of .NET Core SDK you have.

dotnet --list-sdks

The following versions were listed on my computer:

2.2.104 [C:\Program Files\dotnet\sdk]
3.0.100 [C:\Program Files\dotnet\sdk]
3.1.100 [C:\Program Files\dotnet\sdk]

Create a folder named CosmosEfWeb with the following terminal command:

mkdir CosmosEfWeb

Change to the new folder with this command:

cd CosmosEfWeb

Now, if you want to use the exact version of .NET Core that I created my application with, then you can create a global.json file for version 3.1.100 with next command. Note that is optional.

dotnet new globaljson --sdk-version 3.1.100

Next, let us create a new MVC app with this command:

dotnet new mvc

In order to use the new Entity Framework .NET Core 3.0 provider, you must add the Nuget package Microsoft.EntityFrameworkCore.Cosmos. Add this package with the following terminal command:

dotnet add package Microsoft.EntityFrameworkCore.Cosmos

Now let's open our application in the VS Code editor. I was able to do so by simply entering the following command in a terminal window:

code .

We are going to model students. Therefore, add the following very simple Student.cs class inside the Models folder:

public class Student {
  [JsonProperty(PropertyName = "id")]
  public string Id { get; set; }
  [Required]
  [JsonProperty(PropertyName = "firstname")]
  public string FirstName { get; set; }
  [Required]
  [JsonProperty(PropertyName = "lastname")]
  public string LastName { get; set; }
  [Required]
  [JsonProperty(PropertyName = "school")]
  public string School { get; set; }
}

Let us add the database credentials. Add the following section to the appsettings.json file:

"CosmosDb": {
  "Endpoint": "https://cosmos-ef-web.documents.azure.com:443/",
  "Key": "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ1234451234567890ABCDEFGHIJKLMN==",
  "DatabaseName": "CollegeDB",
  "ContainerName": "Students"
}

Endpoint comes from URI and Key comes from PRIMARY KEY.

Service class

We will next create a service that is the middle tier between our controller and the database. Create a folder named Services.

In the Services folder, add the following interface file:

public interface ICosmosDbService {
  Task<IEnumerable<Student>> GetStudentsAsync(string query);
  Task<Student> GetStudentAsync(string id);
  Task AddStudentAsync(Student student);
  Task UpdateStudentAsync(string id, Student student);
  Task DeleteStudentAsync(string id);
}

Also, in the same Services folder, add a class file named CosmosDbService, that implements ICosmosDbService. The CosmosDbService.cs file contains the following code:

public class CosmosDbService : ICosmosDbService {
  private Microsoft.Azure.Cosmos.Container _container;
  public CosmosDbService(
    CosmosClient dbClient,
    string databaseName,
    string containerName) {
    this._container = dbClient.GetContainer(databaseName, containerName);
  }
  public async Task AddStudentAsync(Student student) {
    await this._container.CreateItemAsync<Student>(student, new PartitionKey(student.Id));
  }
  public async Task DeleteStudentAsync(string id) {
    await this._container.DeleteItemAsync<Student>(id, new PartitionKey(id));
  }
  public async Task<Student> GetStudentAsync(string id) {
    try {
        ItemResponse<Student> response = await this._container.ReadItemAsync<Student>(id, new PartitionKey(id));
        return response.Resource;
    }
    catch (CosmosException ex) when (ex.StatusCode == System.Net.HttpStatusCode.NotFound) {
        return null;
    }
  }
  public async Task<IEnumerable<Student>> GetStudentsAsync(string queryString) {
    var query = this._container.GetItemQueryIterator<Student>(new QueryDefinition(queryString));
    List<Student> results = new List<Student>();
    while (query.HasMoreResults) {
        var response = await query.ReadNextAsync();
        results.AddRange(response.ToList());
    }
    return results;
  }
  public async Task UpdateStudentAsync(string id, Student student) {
      await this._container.UpsertItemAsync<Student>(student, new PartitionKey(id));
  }
}

To Startup.cs, add a method named InitializeCosmosClientInstanceAsync that is responsible for reading the database credentials from appsettings.json and returning the service. Add the following method to Startup.cs:

/// <summary>
/// Creates a Cosmos DB database and a container with the specified partition key.
/// </summary>
/// <returns></returns>
private static async Task<CosmosDbService> InitializeCosmosClientInstanceAsync(IConfigurationSection configurationSection)
{
  string databaseName = configurationSection.GetSection("DatabaseName").Value;
  string containerName = configurationSection.GetSection("ContainerName").Value;
  string account = configurationSection.GetSection("Endpoint").Value;
  string key = configurationSection.GetSection("Key").Value;
  CosmosClientBuilder clientBuilder = new CosmosClientBuilder(account, key);
  CosmosClient client = clientBuilder
                      .WithConnectionModeDirect()
                      .Build();
  CosmosDbService cosmosDbService = new CosmosDbService(client, databaseName, containerName);
  DatabaseResponse database = await client.CreateDatabaseIfNotExistsAsync(databaseName);
  await database.Database.CreateContainerIfNotExistsAsync(containerName, "/id");
  return cosmosDbService;
}

Also in Startup.cs, add the following code to the ConfigureServices method in order to create a singleton object representing the service:

services.AddSingleton<ICosmosDbService>(InitializeCosmosClientInstanceAsync(Configuration.GetSection("CosmosDb")).GetAwaiter().GetResult());

Students controller

Let us now create a controller with methods that enable listing, add, edit, and display of student data. In the Controllers folder add a file named StudentsController.cs with the following code:

public class StudentsController : Controller {
  private readonly ICosmosDbService _cosmosDbService;
  public StudentsController(ICosmosDbService cosmosDbService) {
    _cosmosDbService = cosmosDbService;
  }
  [ActionName("Index")]
  public async Task<IActionResult> Index() {
    return View(await _cosmosDbService.GetStudentsAsync("SELECT * FROM c"));
  }
  [ActionName("Create")]
  public IActionResult Create() {
    return View();
  }
  [HttpPost]
  [ActionName("Create")]
  [ValidateAntiForgeryToken]
  public async Task<ActionResult> CreateAsync(Student student) {
    if (ModelState.IsValid) {
      student.Id = Guid.NewGuid().ToString();
      await _cosmosDbService.AddStudentAsync(student);
      return RedirectToAction("Index");
    }
    return View(student);
  }
  [ActionName("Edit")]
  public async Task<ActionResult> EditAsync(string id) {
    if (id == null) return BadRequest();
    Student student = await _cosmosDbService.GetStudentAsync(id);
    if (student == null) return NotFound();
    return View(student);
  }
  [HttpPost]
  [ActionName("Edit")]
  [ValidateAntiForgeryToken]
  public async Task<ActionResult> EditAsync(Student student) {
    if (ModelState.IsValid) {
      await _cosmosDbService.UpdateStudentAsync(student.Id, student);
      return RedirectToAction("Index");
    }
    return View(student);
  }
  [ActionName("Delete")]
  public async Task<ActionResult> DeleteAsync(string id) {
    if (id == null) return BadRequest();
    Student student = await _cosmosDbService.GetStudentAsync(id);
    if (student == null) return NotFound();
    return View(student);
  }
  [HttpPost]
  [ActionName("Delete")]
  [ValidateAntiForgeryToken]
  public async Task<ActionResult> DeleteConfirmedAsync(string id) {
    await _cosmosDbService.DeleteStudentAsync(id);
    return RedirectToAction("Index");
  }
  [ActionName("Details")]
  public async Task<ActionResult> DetailsAsync(string id) {
    return View(await _cosmosDbService.GetStudentAsync(id));
  }
}

Views

Let us add a menu item on the layout that leads to the Students controller. Add the following line item to the ordered list in _Layout.cshtml:

<li class="nav-item">
  <a class="nav-link text-dark" asp-area="" asp-controller="Students" asp-action="Index">Students</a>
</li>

The next step is to add all the views necessary for listing, adding, editing, displaying and deleting data. 

Create folder Students under Views. In the Views/Students folder add files Index.cshtml, Create.cshtml, Edit.cshtml, Details.cshtml and Delete.cshtml. Below is the code for each of these files.

Index.cshtml

@model IEnumerable<CosmosEfWeb.Models.Student>
@{
  ViewData["Title"] = "List of students";
}
<h1>@ViewData["Title"]</h1>
<p>
  <a asp-action="Create" class="btn btn-success btn-sm">Create New Student</a>
</p>
<table class="table">
  <thead>
    <tr>
      <th>
        @Html.DisplayNameFor(model => model.FirstName)
      </th>
      <th>
        @Html.DisplayNameFor(model => model.LastName)
      </th>
      <th>
        @Html.DisplayNameFor(model => model.School)
      </th>
      <th></th>
    </tr>
  </thead>
  <tbody>
@foreach (var item in Model) {
      <tr>
        <td>
          @Html.DisplayFor(modelItem => item.FirstName)
        </td>
        <td>
          @Html.DisplayFor(modelItem => item.LastName)
        </td>
        <td>
          @Html.DisplayFor(modelItem => item.School)
        </td>
        <td>
          @Html.ActionLink("Edit", "Edit", new { id=item.Id }, new { @class = "btn btn-primary btn-sm" }) |
          @Html.ActionLink("Details", "Details", new { id=item.Id }, new { @class = "btn btn-primary btn-sm" }) |
          @Html.ActionLink("Delete", "Delete", new { id=item.Id }, new { @class = "btn btn-primary btn-sm" })
        </td>
      </tr>
}
  </tbody>
</table>

Create.cshtml

@model CosmosEfWeb.Models.Student
@{
  ViewData["Title"] = "Create New Student";
}
<h1>@ViewData["Title"]</h1>
<hr />
<div class="row">
  <div class="col-md-4">
    <form asp-action="Create">
      <div asp-validation-summary="ModelOnly" class="text-danger"></div>
      <div class="form-group">
        <label asp-for="FirstName" class="control-label"></label>
        <input asp-for="FirstName" class="form-control" />
        <span asp-validation-for="FirstName" class="text-danger"></span>
      </div>
      <div class="form-group">
        <label asp-for="LastName" class="control-label"></label>
        <input asp-for="LastName" class="form-control" />
        <span asp-validation-for="LastName" class="text-danger"></span>
      </div>
      <div class="form-group">
        <label asp-for="School" class="control-label"></label>
        <input asp-for="School" class="form-control" />
        <span asp-validation-for="School" class="text-danger"></span>
      </div>
      <div class="form-group">
        <input type="submit" value="Create" class="btn btn-primary" />
      </div>
    </form>
  </div>
</div>
<div>
  <a asp-action="Index">Back to List</a>
</div>
@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

Edit.cshtml

@model CosmosEfWeb.Models.Student
@{
  ViewData["Title"] = "Edit Student";
}
<h1>@ViewData["Title"]</h1>
<hr />
<div class="row">
  <div class="col-md-4">
    <form asp-action="Edit">
      <div asp-validation-summary="ModelOnly" class="text-danger"></div>
      <input type="hidden" asp-for="Id" />
      <div class="form-group">
        <label asp-for="FirstName" class="control-label"></label>
        <input asp-for="FirstName" class="form-control" />
        <span asp-validation-for="FirstName" class="text-danger"></span>
      </div>
      <div class="form-group">
        <label asp-for="LastName" class="control-label"></label>
        <input asp-for="LastName" class="form-control" />
        <span asp-validation-for="LastName" class="text-danger"></span>
      </div>
      <div class="form-group">
        <label asp-for="School" class="control-label"></label>
        <input asp-for="School" class="form-control" />
        <span asp-validation-for="School" class="text-danger"></span>
      </div>
      <div class="form-group">
        <input type="submit" value="Save" class="btn btn-primary btn-sm" /> |
        <a asp-action="Index" class="btn btn-primary btn-sm">Back to List</a>
      </div>
    </form>
  </div>
</div>
@section Scripts {
  @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

Details.cshtml

@model CosmosEfWeb.Models.Student
@{
  ViewData["Title"] = "Student Details";
}
<h1>@ViewData["Title"]</h1>
<div>
  <hr />
  <dl class="row">
    <dt class = "col-sm-2">
      @Html.DisplayNameFor(model => model.FirstName)
    </dt>
    <dd class = "col-sm-10">
      @Html.DisplayFor(model => model.FirstName)
    </dd>
    <dt class = "col-sm-2">
      @Html.DisplayNameFor(model => model.LastName)
    </dt>
    <dd class = "col-sm-10">
      @Html.DisplayFor(model => model.LastName)
    </dd>
    <dt class = "col-sm-2">
      @Html.DisplayNameFor(model => model.School)
    </dt>
    <dd class = "col-sm-10">
          @Html.DisplayFor(model => model.School)
    </dd>
  </dl>
</div>
<div>
  @Html.ActionLink("Edit", "Edit", new { id = Model.Id }, new { @class = "btn btn-primary btn-sm" }) |
  <a asp-action="Index" class="btn btn-primary btn-sm">Back to List</a>
</div>

Delete.cshtml

@model CosmosEfWeb.Models.Student
@{
  ViewData["Title"] = "Delete Student";
}
<h1>@ViewData["Title"]</h1>
<h3>Are you sure you want to delete this student?</h3>
<div>
  <hr />
  <dl class="row">
    <dt class = "col-sm-2">
      @Html.DisplayNameFor(model => model.FirstName)
    </dt>
    <dd class = "col-sm-10">
      @Html.DisplayFor(model => model.FirstName)
    </dd>
    <dt class = "col-sm-2">
      @Html.DisplayNameFor(model => model.LastName)
    </dt>
    <dd class = "col-sm-10">
      @Html.DisplayFor(model => model.LastName)
    </dd>
    <dt class = "col-sm-2">
      @Html.DisplayNameFor(model => model.School)
    </dt>
    <dd class = "col-sm-10">
          @Html.DisplayFor(model => model.School)
      </dd>
  </dl>
 
  <form asp-action="Delete">
    <input type="submit" value="Delete" class="btn btn-danger btn-sm" /> |
    <a asp-action="Index" class="btn btn-primary btn-sm">Back to List</a>
  </form>
</div>

Run application

We have not yet run our application. I am sure you are very curious about the fruits of your very hard work. 

From a terminal window, run the following command:

dotnet run

To view the application, point your browser to http://localhost:5000 or https://localhost:5001. You will see a home page that looks like this:

Click on Students on the top menu bar. Click on green "Create New Student" button on the next page:

Next add some data pertaining to a student in the "Create New Student" form:

When I added Bob Fox in the school of Wildlife, I was redirected to the Index.cshtml page with the following list:

Below are the Edit, Details and Delete pages that should work as expected.

I hope you use this tutorial as a launching pad for much more useful applications that take advantage of the many good features that Azure Cosmos DB has to offer.