SQL Server  

How to Connect SQL Server with C# Using Entity Framework Core

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:

  • Map database tables to C# classes

  • Perform CRUD operations using LINQ

  • Handle relationships and migrations

Prerequisites

Before starting, ensure you have:

  • .NET SDK installed

  • SQL Server installed (or SQL Server Express)

  • Visual Studio or any preferred IDE

Step 1: Install Required NuGet Packages

Install the following packages in your project:

  • Microsoft.EntityFrameworkCore

  • Microsoft.EntityFrameworkCore.SqlServer

  • Microsoft.EntityFrameworkCore.Tools

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

  • Incorrect connection string configuration

  • Forgetting to run migrations

  • Not registering DbContext in dependency injection

  • Using synchronous calls in high-load applications

Real-World Use Case

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

  • Store user data

  • Manage product catalogs

  • Handle orders and transactions

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.

Membership not found