Connecting SQL Server with a C# application using Entity Framework Core is one of the most common and essential tasks in modern .NET development. Entity Framework Core (EF Core) is an Object Relational Mapper (ORM) that allows developers to work with databases using strongly typed C# objects instead of writing raw SQL queries.

This approach improves productivity, reduces boilerplate code, and makes applications easier to maintain.

What is Entity Framework Core

Entity Framework Core is a lightweight, extensible, and cross-platform ORM framework. It enables developers to:

Prerequisites

Before starting, ensure you have:

Step 1: Install Required NuGet Packages

Install the following packages in your project:

You can install them using Package Manager Console:

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

Step 2: Create a Model Class

Create a simple model that represents a database table.

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Step 3: Create DbContext Class

DbContext acts as a bridge between your application and the database.

using Microsoft.EntityFrameworkCore;

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

    public DbSet<User> Users { get; set; }
}

Step 4: Configure Connection String

Add the connection string in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=MyDatabase;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

Step 5: Register DbContext in Program.cs

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

This step enables dependency injection for the database context.

Step 6: Run Migrations

Migrations help create and update the database schema.

Add-Migration InitialCreate
Update-Database

This will generate the database and tables automatically.

Step 7: Perform CRUD Operations

Insert Data

var user = new User { Name = "John" };
context.Users.Add(user);
context.SaveChanges();

Fetch Data

var users = context.Users.ToList();

Update Data

var user = context.Users.First();
user.Name = "Updated Name";
context.SaveChanges();

Delete Data

context.Users.Remove(user);
context.SaveChanges();

Common Mistakes to Avoid

Real-World Use Case

In a typical web application, EF Core is used to:

It simplifies database interaction and improves development speed.

Conclusion

Connecting SQL Server with C# using Entity Framework Core is a fundamental skill for any .NET developer. By following the steps above, you can set up a clean, scalable, and efficient data access layer. EF Core not only reduces the need for raw SQL but also provides powerful features like migrations, LINQ querying, and change tracking.