Using sql server in razor pages project

Suggested Videos
Part 26 - ASP.NET Core view component tag helper | Text | Slides
Part 27 - Implement search page in ASP.NET Core | Text | Slides
Part 28 - Install entity framework core in class library project | Text | Slides

In this video we will discuss how to configure and use SQL Server with entity framework core in ASP.NET Core razor pages project.


One of the very important classes in Entity Framework Core is the DbContext class. This is the class that we use in our application code to interact with the underlying database. It is this class that manages the database connection and is used to retrieve and save data in the database.

• To use the DbContext class in our application
• We create a class that derives from the DbContext class.
• DbContext class is in Microsoft.EntityFrameworkCore namespace.

public class AppDbContext : DbContext
{ }

DbContextOptions in Entity Framework Core

• For the DbContext class to be able to do any useful work, it needs an instance of the DbContextOptions class.
• The DbContextOptions instance carries configuration information such as the connection string, database provider to use etc.
• To pass the DbContextOptions instance we use the constructor as shown in the example below.

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }
}

Entity Framework Core DbSet

• The DbContext class includes a DbSet<TEntity> property for each entity in the model.
• At the moment in our application we have, only one entity class - Employee.
• So in our AppDbContext class we only have one DbSet<Employee> property.
• We will use this DbSet property Employees to query and save instances of the Employee class.
• The LINQ queries against the DbSet<TEntity> will be translated into queries against the underlying database.

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    public DbSet<Employee> Employees { get; set; }
}

Database Connection String in ASP.NET Core

We are using SQL Server localdb which is automatically installed along with Visual Studio. If you want to use a full blown SQL Server instead of localdb, simply change the connection string in appsettings.json configuration file to point to your instance of SQL Server.

During development database connection string is stored in appsettings.json file.

"ConnectionStrings": {
  "EmployeeDBConnection": "server=(localdb)\\MSSQLLocalDB;database=HRDB;Integrated Security=true"

Add SQL Server and Entity Framework Services

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

    public IConfiguration Configuration { get; }

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContextPool<AppDbContext>( options => 
options.UseSqlServer(Configuration.GetConnectionString("EmployeeDBConnection")));

        services.AddRazorPages();
        services.AddSingleton<IEmployeeRepository, MockEmployeeRepository>();
    }
}

• We want to configure and use Microsoft SQL Server with entity framework core.
• We usually specify this configuration in ConfigureServices() method in Startup.cs file.

Difference between AddDbContext() and AddDbContextPool() methods

• We can use either AddDbContext() or AddDbContextPool() method to register our application specific DbContext class with the ASP.NET Core dependency injection system.
• The difference is, AddDbContextPool() method provides DbContext pooling.
• With DbContext pooling, an instance from the DbContext pool is provided if available, rather than creating a new instance.
• DbContext pooling is conceptually similar to how connection pooling works in ADO.NET.
• From a performance standpoint AddDbContextPool() method is better over AddDbContext() method.
• AddDbContextPool() method is introduced in ASP.NET Core 2.0. So if you are using ASP.NET Core 2.0 or later use AddDbContextPool() method over AddDbContext() method.

UseSqlServer() Extension Method

• UseSqlServer() extension method is used to configure our application specific DbContext class to use Microsoft SQL Server as the database.
• To connect to a database, we need the database connection string which is provided as a parameter to UseSqlServer() extension method.

services.AddDbContextPool<AppDbContext>(
    options => options.UseSqlServer(Configuration.GetConnectionString("EmployeeDBConnection")));

At the moment our application is still using MockEmployeeRepository which is an in-memory collection of employees. In our upcoming videos we will implement SQLRepository which stores and retrieves employees from sql server localdb that we have just configured.