Entity Framework (EF) – The Ultimate Guide

Entity Framework (EF) is Microsoft’s ORM (Object-Relational Mapper) for .NET applications. It simplifies database interactions by allowing developers to work with C# objects instead of raw SQL queries.

1. What is Entity Framework (EF)?

Entity Framework acts as a bridge between a .NET application and a relational database. Instead of writing SQL queries manually, you interact with C# classes, and EF translates them into SQL for you.

How It Works

• You define C# classes (models).
• EF maps them to database tables.
• EF generates SQL queries automatically.
• You interact with data using C# instead of SQL.

Example: Without EF vs. With EF

Without EF (Manual SQL)

string query = "SELECT * FROM Users WHERE Id = 1";
SqlCommand command = new SqlCommand(query, connection);

With EF

var user = dbContext.Users.Find(1);

✅ No need to write raw SQL!
✅ EF automatically translates it into:

SELECT * FROM Users WHERE Id = 1;

2. Why Use Entity Framework?

When NOT to Use EF?

• High-performance scenarios (EF can be slower than raw SQL).
• Complex queries (For advanced queries, raw SQL may be better).

3. How to Use Entity Framework?

Step 1: Install EF Core

Run this in your terminal:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

For MySQL:

dotnet add package Pomelo.EntityFrameworkCore.MySql

Step 2: Create a Database Model

EF uses C# classes to represent database tables.

Example: User Model

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

This User class represents a Users table in the database.

Step 3: Create a Database Context

The DbContext is the connection between your C# app and the database.

Example: AppDbContext.cs

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer("Server=.;Database=MyAppDB;Trusted_Connection=True;");
    }
}

🔹 DbSet<User> → Represents a Users table
🔹 UseSqlServer() → Connects to SQL Server (change it for MySQL, PostgreSQL, etc.)

Step 4: Apply Database Migrations

EF allows migrations, which create and update database tables automatically.

Run the following commands:

dotnet ef migrations add InitialCreate
dotnet ef database update

✅ This creates the Users table in the database automatically.

Step 5: Perform CRUD Operations

Now, let’s use EF to interact with the database.

1️⃣ Create a New User (INSERT)

using (var db = new AppDbContext())
{
    var user = new User { Name = "Alice", Email = "alice@example.com" };
    db.Users.Add(user);
    db.SaveChanges();
}

🔹 EF automatically generates:

INSERT INTO Users (Name, Email) VALUES ('Alice', 'alice@example.com');

2️⃣ Get All Users (SELECT)

using (var db = new AppDbContext())
{
    var users = db.Users.ToList();
}

🔹 EF translates it into:

SELECT * FROM Users;

3️⃣ Get a Single User by ID

var user = db.Users.Find(1);

🔹 Equivalent SQL:

SELECT * FROM Users WHERE Id = 1;

4️⃣ Update a User (UPDATE)

var user = db.Users.Find(1);
if (user != null)
{
    user.Email = "newemail@example.com";
    db.SaveChanges();
}

🔹 EF translates it into:

UPDATE Users SET Email = 'newemail@example.com' WHERE Id = 1;

5️⃣ Delete a User (DELETE)

var user = db.Users.Find(1);
if (user != null)
{
    db.Users.Remove(user);
    db.SaveChanges();
}

🔹 Equivalent SQL:

DELETE FROM Users WHERE Id = 1;

4. Advanced Features

1. LINQ Queries

EF allows LINQ (Language-Integrated Query) to filter data.

🔹 Find users with name “Alice”:

var users = db.Users.Where(u => u.Name == "Alice").ToList();

🔹 Equivalent SQL:

SELECT * FROM Users WHERE Name = 'Alice';

2. Relationships (One-to-Many, Many-to-Many)

EF supports relationships without manual foreign keys.

Example: User & Posts (One-to-Many)

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }

    public int UserId { get; set; }  // Foreign Key
    public User User { get; set; }   // Navigation Property
}

🔹 This allows:

var posts = db.Posts.Include(p => p.User).ToList();

3. Migrations: Changing Database Schema

If you add a new field to the User model:

public string PhoneNumber { get; set; }

Run:

dotnet ef migrations add AddPhoneNumber
dotnet ef database update

✅ This updates the database without losing data.

5. Alternatives to Entity Framework

🔹 EF vs. Dapper
If performance is critical, use Dapper because it’s faster than EF.
EF is better for rapid development when speed is not the primary concern.

6. When to Use EF vs. Raw SQL?

Final Summary

Next Steps

✅ Do you want a full tutorial on EF with authentication (JWT)?
✅ Need a step-by-step guide to hosting an EF-based API in Azure?
✅ Want to connect EF to Unity and save game data to a database?

Let me know how you’d like to continue in the comments! 🚀