MVC新手求助：Repository Pattern下用Lambda实现多表Join与Group By

Hey there! I totally get the confusion—switching from direct DbContext queries to the Repository and UnitOfWork patterns can feel like hitting a wall at first, even if you already know how to write joins and grouping logic. The good news is: the core LINQ Lambda logic you already know doesn't change—you just need to adjust how you access your data sources. Let's walk through this with concrete examples.

First, Let's Set Up Our Basics #

Let's assume we have two simple entities we want to work with:

// Customer entity
public class Customer
{
    public int CustomerId { get; set; }
    public string FullName { get; set; }
}

// Order entity
public class Order
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public decimal OrderTotal { get; set; }
    public DateTime OrderDate { get; set; }
}

We'll also need a basic generic Repository interface/implementation and a UnitOfWork to manage our contexts:

Generic Repository #

public interface IRepository<T> where T : class
{
    IQueryable<T> GetAll();
    // Add other methods like GetById, Add, Update as needed
}

public class Repository<T> : IRepository<T> where T : class
{
    protected readonly DbContext _dbContext;
    private readonly DbSet<T> _dbSet;

    public Repository(DbContext dbContext)
    {
        _dbContext = dbContext;
        _dbSet = dbContext.Set<T>();
    }

    public IQueryable<T> GetAll()
    {
        // Use AsNoTracking() for read-only queries to boost performance
        return _dbSet.AsNoTracking();
    }
}

UnitOfWork #

This class will act as a single point of access for all our repositories, ensuring they share the same DbContext:

public interface IUnitOfWork : IDisposable
{
    IRepository<Customer> Customers { get; }
    IRepository<Order> Orders { get; }
    int SaveChanges();
}

public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _dbContext;
    private IRepository<Customer> _customerRepo;
    private IRepository<Order> _orderRepo;

    public UnitOfWork(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public IRepository<Customer> Customers
    {
        get => _customerRepo ??= new Repository<Customer>(_dbContext);
    }

    public IRepository<Order> Orders
    {
        get => _orderRepo ??= new Repository<Order>(_dbContext);
    }

    public int SaveChanges() => _dbContext.SaveChanges();

    public void Dispose() => _dbContext.Dispose();
}

Example 1: Performing a Join with Lambda #

Let's say we want to get a list of orders along with their associated customer names. Here's how you'd do it using the patterns:

using (var unitOfWork = new UnitOfWork(new AppDbContext()))
{
    var ordersWithCustomerDetails = unitOfWork.Orders.GetAll()
        .Join(
            // Second data source: our customers repository
            unitOfWork.Customers.GetAll(),
            // Key selector for orders: match on CustomerId
            order => order.CustomerId,
            // Key selector for customers: match on CustomerId
            customer => customer.CustomerId,
            // Result selector: combine data into an anonymous type (or a DTO)
            (order, customer) => new 
            {
                OrderId = order.OrderId,
                CustomerName = customer.FullName,
                OrderTotal = order.OrderTotal,
                OrderDate = order.OrderDate
            })
        .ToList(); // Execute the query
}

Notice this is almost identical to how you'd write it with direct context.Orders—the only difference is we're pulling data from unitOfWork.Orders.GetAll() instead of the DbSet directly.

Example 2: GroupBy with Lambda (and Optional Join) #

Let's say we want to group orders by customer, then calculate the total number of orders and total spent per customer. Here's how:

Basic GroupBy #

using (var unitOfWork = new UnitOfWork(new AppDbContext()))
{
    var customerOrderStats = unitOfWork.Orders.GetAll()
        .GroupBy(order => order.CustomerId) // Group orders by CustomerId
        .Select(group => new 
        {
            CustomerId = group.Key,
            TotalOrders = group.Count(),
            TotalSpent = group.Sum(order => order.OrderTotal)
        })
        .ToList();
}

GroupBy + Join to Get Customer Names #

If we want to include the customer's name in the result, we can chain a join after the grouping:

using (var unitOfWork = new UnitOfWork(new AppDbContext()))
{
    var customerOrderSummary = unitOfWork.Orders.GetAll()
        .GroupBy(order => order.CustomerId)
        .Select(group => new 
        {
            CustomerId = group.Key,
            TotalOrders = group.Count(),
            TotalSpent = group.Sum(order => order.OrderTotal)
        })
        // Join with customers to get the name
        .Join(
            unitOfWork.Customers.GetAll(),
            stats => stats.CustomerId,
            customer => customer.CustomerId,
            (stats, customer) => new 
            {
                CustomerName = customer.FullName,
                stats.TotalOrders,
                stats.TotalSpent
            })
        .ToList();
}

Pro Tip: Encapsulate Complex Queries in Repositories #

For cleaner code (especially in controllers), you can encapsulate these queries directly in a specialized repository instead of writing them inline. For example:

1. Create a specialized repository interface:

public interface ICustomerRepository : IRepository<Customer>
{
    IEnumerable<CustomerOrderSummary> GetCustomerOrderSummaries();
}

// DTO to hold our summary data
public class CustomerOrderSummary
{
    public string CustomerName { get; set; }
    public int TotalOrders { get; set; }
    public decimal TotalSpent { get; set; }
}

2. Implement the repository:

public class CustomerRepository : Repository<Customer>, ICustomerRepository
{
    public CustomerRepository(AppDbContext dbContext) : base(dbContext) { }

    public IEnumerable<CustomerOrderSummary> GetCustomerOrderSummaries()
    {
        return _dbContext.Orders
            .GroupBy(o => o.CustomerId)
            .Select(g => new 
            {
                CustomerId = g.Key,
                TotalOrders = g.Count(),
                TotalSpent = g.Sum(o => o.OrderTotal)
            })
            .Join(
                _dbContext.Customers,
                s => s.CustomerId,
                c => c.CustomerId,
                (s, c) => new CustomerOrderSummary
                {
                    CustomerName = c.FullName,
                    TotalOrders = s.TotalOrders,
                    TotalSpent = s.TotalSpent
                })
            .ToList();
    }
}

3. Update your UnitOfWork to use the specialized repository:

public ICustomerRepository Customers { get; }

public UnitOfWork(AppDbContext dbContext)
{
    _dbContext = dbContext;
    Customers = new CustomerRepository(dbContext);
}

Now your controller code becomes super clean:

using (var unitOfWork = new UnitOfWork(new AppDbContext()))
{
    var summaries = unitOfWork.Customers.GetCustomerOrderSummaries();
    // Use the summaries as needed
}

Key Takeaways #

• The LINQ Lambda logic you already know works the same way—you're just accessing data via unitOfWork.RepoName.GetAll() instead of direct DbSets.
• Returning IQueryable<T> from your repository is crucial—it lets you chain LINQ operations (like Join/GroupBy) without executing the query until you call ToList()/First() etc.
• UnitOfWork ensures all repositories share the same DbContext, which avoids issues with multiple contexts and helps with transaction management.

Don't worry if it feels awkward at first—once you get the hang of accessing data through the UnitOfWork/repositories, it'll feel just as natural as your old approach!

内容的提问来源于stack exchange，提问作者papagallo