Use SQLite in .NET Core 3 With Entity Framework Core

As you might know, ASP.NET is able to support many different types of Database providers. Today, I decided to test the use of SQLite in ASP.NET Core 3 with Entity Framework Core. For a quick demonstration, I’ll create a simple console application. You can follow along.

Create Sample Console Project

Create a .NET Core 3.x console application in Visual Studio 2019.

Next Dialog, give the project a name.

Install Entity Framework Core

When working with EF Core, you will want to install the correct package for the Entity Framework Core database provider you want to target in your project. Since this demo project is regarding SQLite, I am going to install the EF Core that supports SQLite.

Add NuGet package reference

Install-Package Microsoft.EntityFrameworkCore.Sqlite

Now that we have SQLite Support added, let’s add a simple model to the project to use a test.
Right-Click the project name, Add, and Class to add the new model as shown below.


Replace content of the employee model class with the following snippet.

namespace SQLiteAspNetCoreDemo
{
    public class Employee
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }
    }
}

Create DBContext

Now we need to create the DBContext. Add a new class file and name it SQLiteDBContext.cs and add following code.

using Microsoft.EntityFrameworkCore;

namespace SQLiteAspNetCoreDemo
{
    public class SQLiteDBContext : DbContext
    {
        public DbSet Employees { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite("Data Source=sqlitedemo.db");
    }
}

The code above contains a basic DbSet which is used to create the Employees table.
OnConfiguring() method is used for configuring the DBContext. Here the option is set to use Sqlite database and also setting a connection string or data source to connect.

Now, we’re ready to test things out. Let’s try to add some employee data. Open Program.cs and add the following code.

The moment of truth. Before we can try to run the project we’ll need to run migration first using the following commands in Package Manager Console.
First we’ll need to add Microsoft.EntityFrameworkCore.Tools to allow us to create and apply migration.

Install-Package Microsoft.EntityFrameworkCore.Tools

Next, we need to add migration. Since this is the first migration we’re running we’ll call it InitialCreate.

Add-Migration InitialCreate

A new file will be created in the project with the following content

using Microsoft.EntityFrameworkCore.Migrations;

namespace SQLiteAspNetCoreDemo.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Employees",
                columns: table => new
                {
                    Id = table.Column(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    FirstName = table.Column(nullable: true),
                    LastName = table.Column(nullable: true),
                    Age = table.Column(nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Employees", x => x.Id);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Employees");
        }
    }
}

Update-Database

After running the update command above, we can see the result in the output windows as shown below.

Update the program

We can start testing things out and see if it works. Find the Program.cs file and replace its content with the following.
In the code below, we are creating a new employee object and save it in the database.
Next, we read and get the first employee from the database. Of course, it’s a new database therefore, we’re expecting just one entry in the database. Then, we’re updating the employee first name and the age. Last, we’re deleting that employee.

using System;
using System.Linq;

namespace SQLiteAspNetCoreDemo
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var db = new SQLiteDBContext())
            {
                // Create
                Console.WriteLine("Add New Employee: ");
                db.Employees.Add(new Employee { FirstName = "John", LastName = "Doe", Age = 55 });
                db.SaveChanges();

                Console.WriteLine("Employee has been added sucessfully.");

                // Read
                Console.WriteLine("Querying table for that employee.");
                var employee = db.Employees
                    .OrderBy(b => b.Id)
                    .First();

                Console.WriteLine("The employee found: {0} {1} and is {2} years old.", employee.FirstName, employee.LastName, employee.Age);

                // Update
                Console.WriteLine("Updating the employee first name and age.");

                employee.FirstName = "Louis";
                employee.Age = 90;

                Console.WriteLine("Newly updated employee is: {0} {1} and is {2} years old.", employee.FirstName, employee.LastName, employee.Age);

                db.SaveChanges();

                // Delete
                Console.WriteLine("Delete the employee.");

                db.Remove(employee);
                db.SaveChanges();
            }
        }
    }
}

Test SQLite in ASP.NET Core 3

Finally, the project was run successfully. Below is the output. In case you run into issue with the SQLite table not found, check this post which might help.

How To Add Swagger To Your Web API In ASP.Net Core 3

What is Swagger?
According to Swagger.io, it is basically an open-source software framework with a large ecosystem of tooling that helps developers design, build, document, and consume RESTful web services. For more reading on Swagger check out there Tools Documentation.

Let’s build a WEB API for this demo

In order for me to be able to show how to add swagger, I will need to implement a simple WEB API in ASP.NET Core. If you would like to follow along, here’s a link to the demo project on GitHub

In this post, you learn how to:

  • Create a web API project.
  • Add a model class and a database context.
  • Scaffold a controller with CRUD methods.
  • Add Swagger

Prerequisites

Create Web API Project

From the File menu, select New > Project.
Select the ASP.NET Core Web Application template and click Next.

Give the project a name or if you would like to follow along, name the project WebApiSwaggerMoviesDemo and click Create.

In the Create a new ASP.NET Core Web Application dialog, confirm that .NET Core and ASP.NET Core 3.0 are selected. Select the API template and click Create.

By default a new API called “WeatherForecast” is created for you.

Test the WeatherForecast API

Before I add to this project, I wanted to test it first to make sure everything is good to go. As, you can see I get a list of temperatures below in JSON format.

Add New Movie API

Now that I know the application is working as expected. I need to add a new Model folder to house the movie model.

In Solution Explorer, right-click the project. Select Add > New Folder. Name the folder Models.

Right-click the Models folder and select Add > Class. Name the class Movie and select Add. Therefore, a new file called Movie.cs is added in the Models folder.

Replace the template code with the following code:

using System;

namespace WebApiSwaggerMoviesDemo.Models
{
    public class Movie
    {
        public string Name { get; set; }
        public string Description { get; set; }
        public string Classification { get; set; }
        public DateTime? ReleaseDate { get; set; }
    }
}

Configure the DB Context

Right-click the Models folder and select Add > Class. Name the class MovieContext and click Add. Then, make sure to replace the content of the file with the following piece of code.

using Microsoft.EntityFrameworkCore;

namespace WebApiSwaggerMoviesDemo.Models
{
    public class MovieContext: DbContext
    {
        public MovieContext(DbContextOptions options)
             : base(options)
        {
        
        }
        public DbSet Movies { get; set; }
    }
}

While developing this demo, I did not have Microsoft.EntityFrameworkCore installed, so I had to get it install via NuGet Package Manager.

Register the DBContext

As you already know ASP.NET Core is designed from the start to support Dependency Injection, therefore I need to register the Database context with the IOC container. Something to note here is that I am also specifying to use In-memory database. The name is also specified.

Replace startup.cs file with the following code below.

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

namespace WebApiSwaggerMoviesDemo
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
           
services.AddDbContext(opt => { opt.UseInMemoryDatabase("MovieList"); });
services.AddControllers(); } // This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IWebHostEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } app.UseHttpsRedirection(); app.UseRouting(); app.UseAuthorization(); app.UseEndpoints(endpoints => { endpoints.MapControllers(); }); } } }

Add Movies Controller

In order to add the movie controller, I used the scaffold feature as followed.
Right-click the Controllers folder.

Select Add > New Scaffolded Item.

Select API Controller with actions, using Entity Framework, and then select Add.

In the Add API Controller with actions, using Entity Framework dialog:

Pick Movie (WebApiSwaggerMoviesDemo.Models) in the Model class.
Next,select MovieContext (WebApiSwaggerMoviesDemo.Models) in the Data context class.
Then, click Add.

Install Swagger

I have everything I need in the Web API. It’s time to install Swagger. So, I will use NuGet Package Manager to add SwashBuckle

Install-Package Swashbuckle.AspNetCore -Version 5.0.0-rc4

Once the package has been installed, navigate to the project folder and then open startup.cs and then add the middleware as you see in the below file.

       public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext(opt =>
            {
                opt.UseInMemoryDatabase("MovieList");
            });
            services.AddControllers();
 
services.AddSwaggerGen(c => { c.SwaggerDoc("v1", new OpenApiInfo { Title = "Swagger Movies Demo", Version = "v1" }); });
}

And last, you’ll need to enable Swagger and Swagger UI by updating the configure method as followed.

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

            app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthorization();
            app.UseSwagger();
app.UseSwaggerUI(c => { c.SwaggerEndpoint("/swagger/v1/swagger.json", "Swagger Movies Demo V1"); });
app.UseEndpoints(endpoints => { endpoints.MapControllers(); }); }

Start a new debugging session (F5) and navigate to this url http://localhost:[PORT_NUMBER]/swagger. Then, you should see Swagger UI help pages for your APIs.

All in all, let’s add a few movies to test the API. Expand the post ribbon.

After adding a few movies, let use the GET to pull all the movies.

How to Send Asp.net Core Email With Sendgrid API

I remember a few months back I wrote a quick post on how I managed to send email using MailKit in one of the projects I was on. Well, as you know there are many different services out there making thing simple to send e-mails from application. Since, I like to test things out first before actually implementing it in a real application, I decided to give Sendgrid a try. Therefore, in this post I will walk you through the steps I took to send emails using the Sendgrid API. Are you ready? Let’s do it!!! Find demo project on GitHub here.

I need to create a brand new application for this post. I am currently using Visual Studio 2019 for this project but feel free to use any other version you may have.

Project Setup

  • First, create a new ASP.NET Core application with the following name “SendgridEmailInAspNetCore”
    “File” –> “New” –> “Project”
    Create new project in Visual Studio 2019
  • A “New Project” dialog will open where you can configure your project such as giving a name and location.
    Configure new project in Visual Studio 2019
  • Make sure the project build properly.
    Now that we have the project setup we need to add the Sendgrid Package using the NuGet Package manager tool.
    Open NuGet free and open-source package manager
    Search and Find the Sendgrid Library in NuGet

Add services folder

  • Let’s add a new folder to the project and called it “Services”. This folder will contain the interface that will be used to send the e-mail.
  • Next we need to add the interface called “IEmailSender.cs”
        
    
        using System.Collections.Generic;
        using System.Threading.Tasks;
    
         namespace SendgridEmailInAspNetCore.Services
         {
           public interface IEmailSender
           {
              Task SendEmailAsync(List<string> emails, string subject, string message);
           }
         }
    
    
  • Add an Implementation class called “EmailSender.cs.
    
    using Microsoft.Extensions.Options;
    using SendGrid;
    using SendGrid.Helpers.Mail;
    using System;
    using System.Collections.Generic;
    using System.Threading.Tasks;
    
    namespace SendgridEmailInAspNetCore.Services
    {
        public class EmailSender:IEmailSender
        {
            public EmailSender(IOptions<EmailAuthOptions> optionsAccessor)
            {
                Options = optionsAccessor.Value;
            }
    
            public EmailAuthOptions Options { get; } //set only via Secret Manager
    
            public Task SendEmailAsync(List<string> emails, string subject, string message)
            {
              
                return Execute(Environment.GetEnvironmentVariable("SENDEMAILDEMO_ENVIRONMENT_SENDGRID_KEY"), subject, message, emails);
            }
    
            public Task Execute(string apiKey, string subject, string message, List<string> emails)
            {
                var client = new SendGridClient(apiKey);
                var msg = new SendGridMessage()
                {
                    From = new EmailAddress("noreply@domain.com", "Bekenty Jean Baptiste"),
                    Subject = subject,
                    PlainTextContent = message,
                    HtmlContent = message
                };
    
                foreach (var email in emails)
                {
                    msg.AddTo(new EmailAddress(email));
                }
    
                Task response = client.SendEmailAsync(msg);
                return response;
            }
        }
    }
    
    
  • Next, we’ll add a new class in that same services folder called “EmailAuthOptions.cs” This will only contain the email secrets from sendgrid.

    namespace SendgridEmailInAspNetCore.Services
    {
        public class EmailAuthOptions
        {
            public string SendGridUser { get; set; }
            public string SendGridKey { get; set; }
        }
    }
    

Register the services

  • Find the Startup.cs file, and the ConfigureServices method. Then, add the following two lines.

            services.Configure(Configuration);
            services.AddTransient<IEmailSender, EmailSender>();
    

Setup the Environment Variable

  • Right click on the project and go to the properties. Then, click the debug tab and add a new environment variable with the following key “SENDEMAILDEMO_ENVIRONMENT_SENDGRID_KEY”. set the value to what you have gotten from Sendgrid.
    Way to configure sendgrid api key

Build E-mail Interace to capture message from user

  • Let’s add a new model called “EmailModel.cs” with the following properties.
    using System.ComponentModel.DataAnnotations;
    
    namespace SendgridEmailInAspNetCore.Models
    {
        public class EmailModel
        {
            [Required]
            [EmailAddress]
            public string Email { get; set; }
            [Required]
            public string Subject { get; set; }
            [Required]
            public string Message { get; set; }
        }
    }
    
  • Now, let’s add a new view called “SendEmail.cshtml” in the HOME folder with the following content.
    
    @model SendgridEmailInAspNetCore.Models.EmailModel
    
    @{
        ViewData["Title"] = "SendEmail";
        Layout = "~/Views/Shared/_Layout.cshtml";
    }
    
    <h2>Send Email With Sendgrid in Asp.Net Core</h2>
    
    <hr />
    <div class="row">
        <div class="col-md-4">
            <form asp-action="SendEmail">
                <div asp-validation-summary="ModelOnly" class="text-danger"></div>
                <div class="form-group">
                    <label asp-for="Email" class="control-label"></label>
                    <input asp-for="Email" class="form-control" />
                    <span asp-validation-for="Email" class="text-danger"></span>
                </div>
                <div class="form-group">
                    <label asp-for="Subject" class="control-label"></label>
                    <input asp-for="Subject" class="form-control" />
                    <span asp-validation-for="Subject" class="text-danger"></span>
                </div>
                <div class="form-group">
                    <label asp-for="Message" class="control-label"></label>
                    <textarea asp-for="Message" class="form-control" cols="100"></textarea>
                    <span asp-validation-for="Message" class="text-danger"></span>
                </div>
                <div class="form-group">
                    <input type="submit" value="Send" class="btn btn-default" />
                </div>
            </form>
        </div>
    </div>
    
    @section Scripts {
        @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
    }
    
           
  • In order to be able to send the email in the controller we’ll need to inject the email service via the constructor so that we can have access to send the emails.
    start the start of the home controller we need to add the follow code snippet.

            
    private readonly IEmailSender _emailSender;
    
     public HomeController(IEmailSender emailSender)
     {
       _emailSender = emailSender;
     }
    
  • Now, we’re ready to add the email action methods to the controller.
        
    public IActionResult SendEmail()
    {
      return View();
    }
    public async Task SendEmail(EmailModel model)
    {
     if (ModelState.IsValid)
     {
       var emails = new List();
       emails.Add(model.Email);
       await _emailSender.SendEmailAsync(emails, model.Subject, model.Message);
     }
     return View(model);
    }
    

Final Asp.net Core Email With Sendgrid

  • Now that we have everything in place let’s run the application and Test it out.
    Voila, the application loaded with no issues. Let’s navigate to the SendEmail page. Try to send email without entering anything, you should notice the validations.
    Testing the Asp.net Core Email With Sendgrid with empty inputs
  • Now, enter a valid email, subject, and a message, then click the send button.
    Send a valid email with Asp.net Core Email With Sendgrid
  • The moment of truth. Bingo, I got the e-mail in my inbox as shown below.
    Verify that the email was sent
    Verify that the email was received.

ObjectDisposedException: Cannot access a disposed object

I am working on this application using In-Memory Cache feature in Asp.Net Core and got this error below.
I have dealt with this error multiple times before and it can be hard to debug sometimes because the cause could be different every time. In this one, I was caching some data that way I did not have to keep going to the database every request. To keep this short, I am going to show the piece of code that was causing the issue below.

Problem

As you can see the category object in code below was an IEnumerable type.


viewModelInfo.Categories = _repository.GetAllCategories().Where(x => x.Businesses.Count > 0).Select(x => new SelectListItem
{
Value = x.Name.ToString(),
Text = x.Name
});

“ObjectDisposedException: Cannot access a disposed object. A common cause of this error is disposing a context that was resolved from dependency injection and then later trying to use the same context instance elsewhere in your application. This may occur if you are calling Dispose() on the context, or wrapping the context in a using statement. If you are using dependency injection, you should let the dependency injection container take care of disposing context instances.
Object name: ‘ApplicationDbContext’.”

Solution

Because of lazy-loading here, the query is not fully executed on the linq statement line. Therefore, adding .ToList() will force it to loop through and generate the category list needed.

Just as the error stated, I verify the ApplicationDbContext making sure Dispose() is not being called in there. Then, I place some breakpoints in the action in the controller and step through until I found the cause of my issue. Below is the final piece of code with the fix.


viewModelInfo.Categories = _repository.GetAllCategories().Where(x => x.Businesses.Count > 0).Select(x => new SelectListItem
{
Value = x.Name.ToString(),
Text = x.Name
}).ToList();

How To Quickly Create A New Database Using EntityFramework Core On ASP.Net Core

Last post I’ve gone over how to disable a specific user in ASP.NET Core Identity 2.0, so in this post here, we’ll take a look at how we can leverage Entity Framework to quickly create new databases in no time. Therefore, I will be creating an employee manager using ASP.NET Core 2.1 and Entity Framework (EF) core Code First approach. This employee manager tracks all employees and their respective department.

Prerequisites

Install .NET Core 2.1 SDK from here
Install the latest version of Visual Studio 2017 from here
SQL Server 2008 or above
Source Code
Before proceeding, I recommend you get the source code from GitHub.
Quick read & Credit to: Entity Framework Core on ASP.NET Core

Create the ASP.NET Core project

Open Visual Studio and select “File” > “New” > “Project”.

After selecting the project, a “New Project” dialog will open. Select “.NET Core” in the left panel inside the Visual C# menu.

Then, select “ASP.NET Core Web Application” from the available project types. Put the name of the project as “EmployeeManagerWithEFCore” and press “OK” to create the ASP.NET Core Project.

A new dialog box will open asking you to select the template to be used for the project. So, make sure to select the following:

  • Make sure that .NET Core and ASP.NET Core 2.1 are selected in the drop-down lists
  • Select the Web Application (Model-View-Controller) project template
  • Make sure that Authentication is set to No Authentication
  • Click OK

Selecting the correct project template

By default the “No Authentication” option will be selected, however, if you are required to add login, you can change that in the change authentication button and select Individual User Accounts . Since this tutorial is simply to show how to get started with EntityFramework Core, we’ll keep things simple and not required user to login in in order to add new employees.

To make sure the project is created correctly, let’s run it and make sure there’s no errors before we start adding the models.
Load asp.net net project to ensure it's working

Adding the necessary Models

  • Employee
  • Department

Now, that we know we have a working application, let’s add the models.
Right-click on the Models folder and select Add > Class.

Enter Employee.cs as the name and click OK as seen in the image below.

Replace the contents of the file with the following code:

using System.ComponentModel.DataAnnotations;

namespace EmployeeManagerWithEFCore.Models
{
    public class Employee
    {
        [Key]
        public int Id { get; set; }
        [Required]
        [MaxLength(60)]
        public string FirstName { get; set; }
        [MaxLength(60)]
        public string LastName { get; set; }
        [Required]
        public int Salary { get; set; }
        [Required]
        [MaxLength(20)]
        public string Title { get; set; }
    }
}

Next, right-click on the Models folder and select Add > Class.

Enter Department.cs as the name and click OK.

Replace the contents of the file with the following code:

using System;
using System.Collections.Generic;

namespace EmployeeManagerWithEFCore.Models
{
    public class Department
    {
        public Department()
        {
            Employees = new List();
        }
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string MapIcon { get; set; }
        public bool Deleted { get; set; }
        public DateTime UpdatedAt { get; set; }
        public DateTime CreatedAt { get; set; }
        public virtual ICollection Employees { get; set; }
    }
}

Add ConnectionString

Now, let’s add a new connectionstring in the appSettings.json file to be use for the database as followed.

Add the DbContext

Right-click on the Models folder and select Add > Class.

Enter ApplicationDbContext.cs as the name and click OK.

Replace the contents of the file with the following code:

using Microsoft.EntityFrameworkCore;

namespace EmployeeManagerWithEFCore.Models
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions options)
            : base(options)
        { }

        public DbSet Employees { get; set; }
        public DbSet Departments { get; set; }

    }
}

Some key notes about the above code snippet:

The class ApplicationDbContext is derived from DbContext which is a class that manages the database connection.
The DbSet properties are used to map with the database tables.

Register the db context

Now, we can register the context via the service configuration in the startup.cs file as shown below.

We have everything we need in the project to build it and make sure there’s no error. Once done, we can start creating the database.

Add Database

Tools > NuGet Package Manager > Package Manager Console

Run the following commands:
In order to generate the database and add the necessary tables, we need to run migration commands from the package manager console.

In Visual Studio select Tools > NuGet Package Manager > Package Manager Console.
Run the following commands:

 Add-Migration InitialDatabaseCreation
 Update-Database

In case you run into some error as I did during in this step, simply close Visual Studio and re-open the project. Then, re-run add-migration again.

Voila, after closing and re-open Visual Studio, I was able to successfully run Migration as expected.

PM> Add-Migration
cmdlet Add-Migration at command pipeline position 1
Supply values for the following parameters:
Name: InitialDatabaseCreation
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.1.0-preview2-30571 initialized 'ApplicationDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
To undo this action, use Remove-Migration.
PM> Update-Database
Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.1.0-preview2-30571 initialized 'ApplicationDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (447ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      CREATE DATABASE [EmployeeManagerWithEFCoreDB];
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (61ms) [Parameters=[], CommandType='Text', CommandTimeout='60']
      IF SERVERPROPERTY('EngineEdition') <> 5
      BEGIN
          ALTER DATABASE [EmployeeManagerWithEFCoreDB] SET READ_COMMITTED_SNAPSHOT ON;
      END;
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [__EFMigrationsHistory] (
          [MigrationId] nvarchar(150) NOT NULL,
          [ProductVersion] nvarchar(32) NOT NULL,
          CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (33ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [MigrationId], [ProductVersion]
      FROM [__EFMigrationsHistory]
      ORDER BY [MigrationId];
Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20190114032255_InitialDatabaseCreation'.
Applying migration '20190114032255_InitialDatabaseCreation'.
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Departments] (
          [Id] int NOT NULL IDENTITY,
          [Name] nvarchar(max) NULL,
          [Description] nvarchar(max) NULL,
          [MapIcon] nvarchar(max) NULL,
          [Deleted] bit NOT NULL,
          [UpdatedAt] datetime2 NOT NULL,
          [CreatedAt] datetime2 NOT NULL,
          CONSTRAINT [PK_Departments] PRIMARY KEY ([Id])
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Employees] (
          [Id] int NOT NULL IDENTITY,
          [FirstName] nvarchar(60) NOT NULL,
          [LastName] nvarchar(60) NULL,
          [Salary] int NOT NULL,
          [Title] nvarchar(20) NOT NULL,
          [DepartmentId] int NULL,
          CONSTRAINT [PK_Employees] PRIMARY KEY ([Id]),
          CONSTRAINT [FK_Employees_Departments_DepartmentId] FOREIGN KEY ([DepartmentId]) REFERENCES [Departments] ([Id]) ON DELETE NO ACTION
      );
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX [IX_Employees_DepartmentId] ON [Employees] ([DepartmentId]);
Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
      VALUES (N'20190114032255_InitialDatabaseCreation', N'2.1.0-preview2-30571');
Done.
PM> 

To conclude, if you check in sql server management and load your local databases, we should be seeing the new database created.

Add Department and Employee controller as shown below.

Controller.
Select MVC Controller with views, using Entity Framework and click Add.
Set Model class to respective to the controller being created and Data context class to ApplicationDbContext.
Make sure to select the “_Layout.cshtml”
Click Add.

Once those controllers are created you should notice two files “DepartmentsController.cs”,”EmployeesController.cs” in the controller folder and the view folder for each of those controllers with with the necessary view pages with (.cshtml).

Let’s run the application and see what we get and start populating the database.

Example of the department creation page:

For simplicity, I have hard-coded the department to get the employees added to the department we have created. For production, you might want to add a dropdown list for user to select the department while creating the employee.