使用C#的Entity Framework与Lambda表达式实现ASP.NET数据库列表生成

Let's walk through how to build your small ASP.NET site using Entity Framework (EF) and Lambda expressions to work with your 6-table database. I'll cover model setup, key queries, and a quick example of displaying the data in a page.

1. Define Your EF Entity Models #

First, create entity classes that mirror your database tables. For junction tables with extra fields (like ProjectCategoryPart has PartExists), we can't rely on EF's automatic many-to-many mapping—we'll need to define those as explicit entities:

public class Project
{
    public int ID { get; set; }
    public string ProjectName { get; set; }
    // Navigation properties
    public ICollection<ProjectCategoryPart> ProjectCategoryParts { get; set; }
    public ICollection<ProjectCategoryUser> ProjectCategoryUsers { get; set; }
}

public class Category
{
    public int ID { get; set; }
    public string CategoryName { get; set; }
    public ICollection<ProjectCategoryPart> ProjectCategoryParts { get; set; }
    public ICollection<ProjectCategoryUser> ProjectCategoryUsers { get; set; }
}

public class User
{
    public int ID { get; set; }
    public string UserName { get; set; }
    public ICollection<ProjectCategoryUser> ProjectCategoryUsers { get; set; }
}

public class Part
{
    public int ID { get; set; }
    public string PartName { get; set; }
    public ICollection<ProjectCategoryPart> ProjectCategoryParts { get; set; }
}

// Junction table with extra field
public class ProjectCategoryPart
{
    public int ProjectID { get; set; }
    public int CategoryID { get; set; }
    public int PartID { get; set; }
    public bool PartExists { get; set; }
    // Navigation properties
    public Project Project { get; set; }
    public Category Category { get; set; }
    public Part Part { get; set; }
}

// Junction table for Project-Category-User
public class ProjectCategoryUser
{
    public int ProjectID { get; set; }
    public int CategoryID { get; set; }
    public int UserID { get; set; }
    public Project Project { get; set; }
    public Category Category { get; set; }
    public User User { get; set; }
}

Then set up your DbContext to map these entities:

public class YourDbContext : DbContext
{
    public DbSet<Project> Projects { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<Part> Parts { get; set; }
    public DbSet<ProjectCategoryPart> ProjectCategoryParts { get; set; }
    public DbSet<ProjectCategoryUser> ProjectCategoryUsers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Replace with your connection string
        optionsBuilder.UseSqlServer("Your_Connection_String_Here");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure composite primary keys for junction tables
        modelBuilder.Entity<ProjectCategoryPart>()
            .HasKey(pcp => new { pcp.ProjectID, pcp.CategoryID, pcp.PartID });

        modelBuilder.Entity<ProjectCategoryUser>()
            .HasKey(pcu => new { pcu.ProjectID, pcu.CategoryID, pcu.UserID });
    }
}

2. Key Lambda Queries for Displaying Data #

Now let's write Lambda-based queries to fetch the data you need for display. Here are some common scenarios:

Get a Single Project with All Related Categories, Parts, and Users #

This query will pull a specific project, along with every category linked to it, plus the parts (with their PartExists status) and users assigned to each category:

using var context = new YourDbContext();

var projectDetails = context.Projects
    .Where(p => p.ID == 1) // Replace with your target Project ID
    .Include(p => p.ProjectCategoryParts)
        .ThenInclude(pcp => pcp.Category)
    .Include(p => p.ProjectCategoryParts)
        .ThenInclude(pcp => pcp.Part)
    .Include(p => p.ProjectCategoryUsers)
        .ThenInclude(pcu => pcu.Category)
    .Include(p => p.ProjectCategoryUsers)
        .ThenInclude(pcu => pcu.User)
    .FirstOrDefault();

Get All Projects with Their Associated Categories #

If you want to list all projects and their linked categories:

var projectsWithCategories = context.Projects
    .Select(p => new 
    {
        p.ID,
        p.ProjectName,
        Categories = p.ProjectCategoryParts
            .Select(pcp => pcp.Category.CategoryName)
            .Distinct()
            .ToList()
    })
    .ToList();

3. Display Data in an ASP.NET Razor Page #

Let's take the projectDetails query and display it in a Razor Page. First, pass the data from your PageModel:

public class ProjectDetailsModel : PageModel
{
    private readonly YourDbContext _context;

    public ProjectDetailsModel(YourDbContext context)
    {
        _context = context;
    }

    public Project Project { get; set; }

    public async Task<IActionResult> OnGetAsync(int id)
    {
        Project = await _context.Projects
            .Include(p => p.ProjectCategoryParts)
                .ThenInclude(pcp => pcp.Category)
            .Include(p => p.ProjectCategoryParts)
                .ThenInclude(pcp => pcp.Part)
            .Include(p => p.ProjectCategoryUsers)
                .ThenInclude(pcu => pcu.Category)
            .Include(p => p.ProjectCategoryUsers)
                .ThenInclude(pcu => pcu.User)
            .FirstOrDefaultAsync(p => p.ID == id);

        if (Project == null)
        {
            return NotFound();
        }

        return Page();
    }
}

Then in your Razor View (ProjectDetails.cshtml):

@page
@model ProjectDetailsModel

<h1>@Model.Project.ProjectName</h1>

<h2>Categories & Associated Data</h2>
@foreach (var categoryGroup in Model.Project.ProjectCategoryParts.GroupBy(pcp => pcp.Category))
{
    var category = categoryGroup.Key;
    <div class="category-section">
        <h3>@category.CategoryName</h3>
        
        <h4>Parts:</h4>
        <ul>
            @foreach (var pcp in categoryGroup)
            {
                <li>@pcp.Part.PartName - @(pcp.PartExists ? "Exists" : "Not Present")</li>
            }
        </ul>

        <h4>Assigned Users:</h4>
        <ul>
            @foreach (var pcu in Model.Project.ProjectCategoryUsers.Where(u => u.CategoryID == category.ID))
            {
                <li>@pcu.User.UserName</li>
            }
        </ul>
    </div>
}

Quick Tips #

• EF Core vs EF6: If you're using EF Core, the Include/ThenInclude syntax is the same, but make sure you have the necessary NuGet packages installed (Microsoft.EntityFrameworkCore.SqlServer, etc.).
• Performance: For large datasets, consider using Select to project only the fields you need instead of loading full entities—this reduces database load and memory usage.
• Error Handling: Always check for null values (like if a project isn't found) to avoid NullReferenceException in your views.

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