Entity Framework Core — CRUD & Migrations

EF Core কী?

Microsoft-এর modern ORM (Object-Relational Mapper) — C# class দিয়ে DB query।

Setup

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

Entity (Model)

public class Product {
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }      // navigation
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
}

public class Category {
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Product> Products { get; set; } = new();
}

DbContext

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

    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();

    protected override void OnModelCreating(ModelBuilder mb) {
        mb.Entity<Product>(e => {
            e.Property(p => p.Name).IsRequired().HasMaxLength(100);
            e.Property(p => p.Price).HasColumnType("decimal(18,2)");
            e.HasIndex(p => p.Name);
            e.HasOne(p => p.Category)
             .WithMany(c => c.Products)
             .HasForeignKey(p => p.CategoryId);
        });
    }
}

Register in Program.cs

builder.Services.AddDbContext<AppDbContext>(opt =>
    opt.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

Migrations ⭐

# Install tool (একবার)
dotnet tool install --global dotnet-ef

# Add migration
dotnet ef migrations add InitialCreate

# Apply to DB
dotnet ef database update

# Specific migration
dotnet ef database update InitialCreate

# Remove last (if not applied)
dotnet ef migrations remove

# Generate SQL script
dotnet ef migrations script -o migration.sql

# Drop DB
dotnet ef database drop

CRUD Operations

public class ProductService {
    private readonly AppDbContext _db;
    public ProductService(AppDbContext db) => _db = db;

    // CREATE
    public async Task<Product> CreateAsync(Product p) {
        _db.Products.Add(p);
        await _db.SaveChangesAsync();
        return p;
    }

    // READ
    public Task<List<Product>> GetAllAsync() =>
        _db.Products.AsNoTracking().ToListAsync();

    public Task<Product?> GetByIdAsync(int id) =>
        _db.Products.Include(p => p.Category).FirstOrDefaultAsync(p => p.Id == id);

    public Task<List<Product>> SearchAsync(string q) =>
        _db.Products.Where(p => p.Name.Contains(q)).ToListAsync();

    // UPDATE
    public async Task<bool> UpdateAsync(int id, Product update) {
        var p = await _db.Products.FindAsync(id);
        if (p == null) return false;
        p.Name = update.Name;
        p.Price = update.Price;
        await _db.SaveChangesAsync();
        return true;
    }

    // DELETE
    public async Task<bool> DeleteAsync(int id) {
        var p = await _db.Products.FindAsync(id);
        if (p == null) return false;
        _db.Products.Remove(p);
        await _db.SaveChangesAsync();
        return true;
    }
}

Useful Operators

// Eager loading
_db.Products.Include(p => p.Category)
            .ThenInclude(c => c.SubCategories)

// AsNoTracking() — read-only, faster
_db.Products.AsNoTracking()

// Pagination
.Skip((page - 1) * size).Take(size)

// Bulk operations (.NET 7+)
await _db.Products.Where(p => p.Stock == 0)
                  .ExecuteDeleteAsync();
await _db.Products.Where(p => p.CategoryId == 5)
                  .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.1m));