Asp.Net Core 8 MVC: Connect to Sql Server Database with EF Using C#

In today's article I will show you a simple tutorial with example how you can connect to MS SQL server data base with entity framework core or EF core in asp.net core mvc using C#.net. Here in this tutorial, I have used DB first approach to make connection with the database. So, I will try to explain in short what is D first approach.

What is DB first In Entity Framework .NET Core?

DB first we use where we already have the database and with the help of mS SQL Database we create the model class which hold the detail of the tables in the database. This approach is helpful where the DB change is minimal.

How to Connect to MS SQL Database with Entity Framework?

To make the connection to the MS SQL server database using entity framework in asp.net core mvc using c#.net with the help of Entity Framework DB First approach. Now to make the connection we need to instal the below given three packages.

Package to install Microsoft.EntityFrameworkCore.Tools
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.SqlServer

The above mentioned packages are mandatory to make connection with the database using Entity framework code using c#.net. So, for this article first we will create a new data base with few tables.

In above screenshot I have created a data base foe employee, and this database is having two tables. now to proceed further we will create an asp.net core 8 mvc application using c#.net. After creating the .NET core mvc application we will install all the three above mention packages. To install the packages, we need to go to Solution Explorer -> Dependencies (Right click on it) -> Manage NuGet Packages (click).

Now search the above mention package and install it one by one. If we check the Packages folder, we will not get only one package.

After installing all the 3 mention packages we it will looks like as shown below.

After installing all the three packages we will be needed to run the Scaffold DBContext command. So what is Scaffold command, it is the command which we use for DB first approach to make connection with the DB and generate the model class for. Plese refer the below screen for the syntax of the Scaffold. This command we run through Project Manager Console.

Scaffold command syntax Scaffold-DbContext "Server=<MS SQL Server Name>;Database=<Database name>;Trusted_Connection=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir data

Above syntax describe the complete command detail which we cna execute. Here we are having following items.

Server: <MS SQL Server Name>;

Database: <Database name>;

Trusted_Connection: True;

TrustServerCertificate:True;

To avoid the below mention error Trusted_Connection=True have been used.

Error! A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

Microsoft.EntityFrameworkCore.SqlServer: Represent the provider for the MS SQL server

-OutputDir data: This shown the folder in root. So this folder will have all the table model class files and the DBContext class file after execution of the Scaffold command.

Before running the command, we will create a folder named as "data" where we will use in our command. You can create as per your choice. But you must use the same folder in the command.

Now lets the Scaffold command to make the connection to the MS DB. While executing the command first you will build for validating any error in the code. If no error, it will execute the command and complete process.

After successful execution we will the table model class file and the a extra DBContext file.

Ones open the context file we will get the connection string and other the table details.

Here is the complete code for the DB context file.

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace Project.data;

public partial class EmployeeContext : DbContext
{
    public EmployeeContext()
    {
    }

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

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

    public virtual DbSet<Project> Projects { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see https://go.microsoft.com/fwlink/?LinkId=723263.
        => optionsBuilder.UseSqlServer("Server=LAPTOP-CIVUVPJ5\\SQLEXPRESS;Database=Employee;Trusted_Connection=True;TrustServerCertificate=True;");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK_EmpMaster");

            entity.ToTable("Employee");

            entity.Property(e => e.Department)
                .HasMaxLength(50)
                .IsUnicode(false);
            entity.Property(e => e.EmployeeName)
                .HasMaxLength(50)
                .IsUnicode(false)
                .HasColumnName("Employee_Name");
        });

        modelBuilder.Entity<Project>(entity =>
        {
            entity.Property(e => e.EmployeeId).HasColumnName("Employee_Id");
            entity.Property(e => e.ProjectName)
                .HasMaxLength(50)
                .IsUnicode(false);
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Here in above code, you can see the EmployeeContext file code. In this code we can see the both the table which is Employee and Project. In this we are also getting the primary key detail. Now let's write a sample code to check the connectivity. For this in controller we will write a linq query to get the data from the data base. But before that let check the SQL data.

Here is the code to get the above detail form employee table.

 [HttpGet]
 public IActionResult Index()
 {
     EmployeeContext employeeContext = new EmployeeContext();
     var empData = employeeContext.Employees;
     return View();
 }

In above code I have created object of EmployeeContact class and through this i have used Linq query to get the data.