How to Use Entity Framework Core

using Microsoft.EntityFrameworkCore;

// Custom DbContext
public class AppDbContext : DbContext
{
    // Define DbSet (corresponds to database table)
    public DbSet<User> Users { get; set; }

    // Core configuration: Override OnConfiguring to specify database connection
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // 1. Connection string, containing database address/authentication information
        string sqlServerConn = "Server=(localdb)\\mssqllocaldb;Database=MyDatabase;Trusted_Connection=True;";
        // PostgreSQL connection string example (requires installing Npgsql.EntityFrameworkCore.PostgreSQL package)
        // string pgsqlConn = @"Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase";

        // 2. Specify database provider (tell EF Core which database to use)
        optionsBuilder.UseSqlServer(sqlServerConn); // SQL Server
        // optionsBuilder.UseNpgsql(mySqlConn, ServerVersion.AutoDetect(pgsqlConn)); // PostgreSQL
    }
}

// Simple entity class
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    // Constructor receives configuration injected by DI container (core)
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

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

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// 1. Set the database connection string
string connStr = "Server=(localdb)\\mssqllocaldb;Database=MyDatabase;Trusted_Connection=True;";

// 2. Register DbContext to DI container
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connStr); // Specify database provider
});

var app = builder.Build();
app.Run();

// using statement: Automatically releases DbContext resources (database connection)
using (var dbContext = new AppDbContext())
{
    // Add data
    var newUser = new User { Name = "Zhang San", Age = 25 };
    dbContext.Users.Add(newUser);
    dbContext.SaveChanges(); // Submit changes

    // Query data
    var allUsers = dbContext.Users.ToList();
    Console.WriteLine($"Found {allUsers.Count} users in total");
}

// ASP.NET Core controller example
[ApiController]
[Route("api/[controller]")]
public class UserController : ControllerBase
{
    // Private field stores DbContext instance
    private readonly AppDbContext _dbContext;

    // Constructor injection: DI container automatically passes in AppDbContext
    public UserController(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    // Using DbContext in interface
    [HttpGet]
    public IActionResult GetAllUsers()
    {
        var users = _dbContext.Users.ToList();
        return Ok(users);
    }
}

// Example 1: User entity (default mapping to Users table)
public class User
{
    // Primary key: EF Core automatically recognizes Id as primary key, int type defaults to auto-increment
    public int Id { get; set; }
    // Regular field: string→database nvarchar(MAX) (default, SQL Server type)
    public string UserName { get; set; }
    // Regular field: int→database int
    public int Age { get; set; }
    // Navigation property: one-to-many relationship, pointing to Order (does not generate actual column, only used for relationship recognition)
    public List<Order> Orders { get; set; } = new List<Order>(); // Initialize to avoid null reference
}

// Example 2: Order entity (default mapping to Orders table)
public class Order
{
    public int Id { get; set; }
    public string OrderTitle { get; set; }
    public decimal Amount { get; set; }
    // Foreign key convention: "associated entity name+Id" (UserId) automatically recognized as foreign key, linking to User's Id
    public int UserId { get; set; }
    // Reverse navigation property: confirms the user to whom the order belongs
    public User User { get; set; }
}

// Data annotation example: Custom User entity rules
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("Sys_User")] // Custom table name (no longer default Users)
public class User
{
    [Key] // Explicitly mark primary key (optional, Id already recognized by default)
    [DatabaseGenerated(DatabaseGeneratedOption.None)] // Primary key does not auto-increment
    public int Id { get; set; }

    [Column("UserName", TypeName = "nvarchar(50)")] // Custom column name+length
    [Required] // Non-null constraint
    public string UserName { get; set; }

    [Range(0, 120)] // Numeric range constraint
    public int Age { get; set; }

    public List<Order> Orders { get; set; } = new List<Order>();
}

# 1. Create migration file: Generate table creation SQL script (stored in project Migrations folder)
Add-Migration InitialCreate

# 2. Apply migration: Execute SQL, create tables in database
Update-Database

-- 1. Create Users table (corresponding to User entity)
CREATE TABLE [Users] (
    [Id] INT NOT NULL IDENTITY(1,1), -- Corresponds to User.Id: primary key+auto-increment (EF Core int primary key defaults to IDENTITY)
    [UserName] NVARCHAR(MAX) NULL,    -- Corresponds to User.UserName: string defaults to mapping nvarchar(MAX), nullable
    [Age] INT NOT NULL,               -- Corresponds to User.Age: int defaults to mapping int, non-null (EF Core value types default to non-null)
    CONSTRAINT [PK_Users] PRIMARY KEY ([Id]) -- Primary key constraint: corresponds to User.Id as primary key
);

-- 2. Create Orders table (corresponding to Order entity)
CREATE TABLE [Orders] (
    [Id] INT NOT NULL IDENTITY(1,1),          -- Corresponds to Order.Id: primary key+auto-increment
    [OrderTitle] NVARCHAR(MAX) NULL,         -- Corresponds to Order.OrderTitle: string→nvarchar(MAX), nullable
    [Amount] DECIMAL(18, 2) NOT NULL,        -- Corresponds to Order.Amount: decimal defaults to mapping decimal(18,2), non-null
    [UserId] INT NOT NULL,                   -- Corresponds to Order.UserId: foreign key field, linking to Users.Id
    CONSTRAINT [PK_Orders] PRIMARY KEY ([Id]), -- Primary key constraint
    -- Foreign key constraint: corresponds to one-to-many relationship of navigation properties (Order.User + User.Orders)
    CONSTRAINT [FK_Orders_Users_UserId] FOREIGN KEY ([UserId])
        REFERENCES [Users] ([Id])
        ON DELETE CASCADE -- Cascade delete: deletes associated Order when deleting User (EF Core default behavior)
);

using (var dbContext = new AppDbContext())
{
    // 1. Create entity object
    var newUser = new User
    {
        UserName = "Zhang San",
        Age = 28
    };

    // 2. Mark as new status (DbSet<T>.Add)
    dbContext.Users.Add(newUser);

    // 3. Submit changes (EF Core generates INSERT SQL and executes)
    int affectedRows = dbContext.SaveChanges(); // Return affected rows (here is 1)
    Console.WriteLine($"Successfully added user, user ID: {newUser.Id}"); // Auto-fill primary key Id after addition
}

using (var dbContext = new AppDbContext())
{
    var newUser = new User
    {
        UserName = "Li Si",
        Age = 30,
        // Navigation property assignment: associate orders
        Orders = new List<Order>
        {
            new Order { OrderTitle = "Buy phone", Amount = 2999.99m },
            new Order { OrderTitle = "Buy headphones", Amount = 199.99m }
        }
    };

    dbContext.Users.Add(newUser);
    dbContext.SaveChanges();
    Console.WriteLine($"Added user ID: {newUser.Id}, associated orders count: {newUser.Orders.Count}");
    // Order's UserId will be automatically filled with newUser.Id, no manual assignment needed
}

using (var dbContext = new AppDbContext())
{
    var userList = new List<User>
    {
        new User { UserName = "Wang Wu", Age = 25 },
        new User { UserName = "Zhao Liu", Age = 35 }
    };

    // AddRange: batch mark as new
    dbContext.Users.AddRange(userList);
    dbContext.SaveChanges();
    Console.WriteLine($"Batch added {userList.Count} users completed");
}

using (var dbContext = new AppDbContext())
{
    // 1. Query all users (generate: SELECT * FROM Users)
    var allUsers = dbContext.Users.ToList();

    // 2. Query single user (by primary key, return null if not found, recommend using FirstOrDefault)
    var targetUser = dbContext.Users.FirstOrDefault(u => u.Id == 1);
    // Note: SingleOrDefault requires result to be 0 or 1 record, more will throw error; FirstOrDefault takes first record, safer

    // 3. Conditional query (users with age>25, generate: SELECT * FROM Users WHERE Age > 25)
    var adultUsers = dbContext.Users.Where(u => u.Age > 25).ToList();

    // 4. Query only specified fields (avoid SELECT *, improve performance)
    var userNames = dbContext.Users.Select(u => new { u.Id, u.UserName }).ToList();
}

using (var dbContext = new AppDbContext())
{
    // 1. Load user+associated all orders (generate JOIN SQL)
    var userWithOrders = dbContext.Users
        .Include(u => u.Orders) // Key: load navigation property Orders
        .FirstOrDefault(u => u.Id == 1);
    if (userWithOrders != null)
    {
        Console.WriteLine($"User {userWithOrders.UserName} has {userWithOrders.Orders.Count} orders");
    }

    // 2. Load order+associated user (reverse navigation)
    var orderWithUser = dbContext.Orders
        .Include(o => o.User)
        .FirstOrDefault(o => o.Id == 1);
    Console.WriteLine($"Order {orderWithUser.Id} belongs to user: {orderWithUser.User.UserName}");
}

using (var dbContext = new AppDbContext())
{
    int pageIndex = 1; // Page number (start from 1)
    int pageSize = 10; // Records per page

    var pagedUsers = dbContext.Users
        .OrderByDescending(u => u.Age) // Sort by age descending
        .Skip((pageIndex - 1) * pageSize) // Skip previous records
        .Take(pageSize) // Take specified number
        .ToList();

    // Total count (for calculating total pages)
    int totalCount = dbContext.Users.Count();
    Console.WriteLine($"Page {pageIndex}, total {totalCount} records");
}

using (var dbContext = new AppDbContext())
{
    // 1. First query entity to update (EF Core starts tracking this entity)
    var userToUpdate = dbContext.Users.FirstOrDefault(u => u.Id == 1);
    if (userToUpdate == null) return;

    // 2. Modify properties (EF Core automatically marks as "modified status")
    userToUpdate.Age = 29;
    userToUpdate.UserName = "Zhang San_Modified";

    // 3. Submit changes (no need to manually call Update, EF Core already tracks status)
    dbContext.SaveChanges();
    Console.WriteLine("User information update completed");
}

using (var dbContext = new AppDbContext())
{
    // 1. Create entity with only primary key and fields to modify
    var userToUpdate = new User { Id = 1, Age = 30 };

    // 2. Attach to DbContext and mark as "modified status"
    dbContext.Users.Attach(userToUpdate);
    dbContext.Entry(userToUpdate).Property(u => u.Age).IsModified = true; // Only mark Age as modified

    // 3. Submit changes
    dbContext.SaveChanges();
}

using (var dbContext = new AppDbContext())
{
    // 1. Query entities to batch update
    var usersToUpdate = dbContext.Users.Where(u => u.Age < 30).ToList();

    // 2. Batch modify
    foreach (var user in usersToUpdate)
    {
        user.Age += 1; // Age+1
    }

    // 3. Submit
    dbContext.SaveChanges();
}

using (var dbContext = new AppDbContext())
{
    // 1. Query entity to delete
    var userToDelete = dbContext.Users.FirstOrDefault(u => u.Id == 2);
    if (userToDelete == null) return;

    // 2. Mark as delete status
    dbContext.Users.Remove(userToDelete);

    // 3. Submit changes
    dbContext.SaveChanges();
    Console.WriteLine("User deletion completed");
}

using (var dbContext = new AppDbContext())
{
    var userToDelete = dbContext.Users.Include(u => u.Orders).FirstOrDefault(u => u.Id == 1);
    if (userToDelete == null) return;

    // Delete user → cascade delete all their orders
    dbContext.Users.Remove(userToDelete);
    dbContext.SaveChanges();
    Console.WriteLine($"Deleted user {userToDelete.Id}, simultaneously deleted {userToDelete.Orders.Count} orders");
}

using (var dbContext = new AppDbContext())
{
    // 1. Query entities to batch delete
    var ordersToDelete = dbContext.Orders.Where(o => o.Amount < 200).ToList();

    // 2. Batch mark delete
    dbContext.Orders.RemoveRange(ordersToDelete);

    // 3. Submit
    dbContext.SaveChanges();
    Console.WriteLine($"Batch deleted {ordersToDelete.Count} orders");
}

dotnet add package Microsoft.EntityFrameworkCore.InMemory

// OnConfiguring method
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Key: Use in-memory database, specify database name (identify unique in-memory database instance)
        optionsBuilder.UseInMemoryDatabase("MyDatabase");
    }

// Dependency injection configuration method
var services = new ServiceCollection();
services.AddDbContext<AppDbContext>(options =>
{
    // Configure in-memory database, create new instance each time (avoid test cases interfering with each other)
    options.UseInMemoryDatabase("MyDatabase");
});

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.InMemory

namespace ConsoleApp1;

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

using Microsoft.EntityFrameworkCore;

namespace ConsoleApp1;

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseInMemoryDatabase("MyDatabase");
    }
}

using ConsoleApp1;

using (var dbContext = new AppDbContext())
{
    // Add
    var newUser = new User { Name = "Zhang San", Age = 25 };
    dbContext.Users.Add(newUser);
    dbContext.SaveChanges();
    Console.WriteLine($"Added user: {newUser.Name}, age: {newUser.Age}, ID: {newUser.Id}");

    // Query
    var users = dbContext.Users.ToList();
    Console.WriteLine("Query all users:");
    foreach (var user in users)
    {
        Console.WriteLine($"ID: {user.Id}, Name: {user.Name}, Age: {user.Age}");
    }

    // Update
    var userToUpdate = dbContext.Users.FirstOrDefault(u => u.Name == "Zhang San");
    if (userToUpdate != null)
    {
        userToUpdate.Age = 26;
        dbContext.SaveChanges();
        Console.WriteLine($"Updated user age to: {userToUpdate.Age}");
    }

    // Delete
    var userToDelete = dbContext.Users.FirstOrDefault(u => u.Name == "Zhang San");
    if (userToDelete != null)
    {
        dbContext.Users.Remove(userToDelete);
        dbContext.SaveChanges();
        Console.WriteLine($"Deleted user: {userToDelete.Name}");
    }
}