如何在C# LINQ中创建嵌套子查询并关联三张数据表

我有三张数据表，结构如下：

• 表1：主分类表，存储一级分类数据，包含ID、编码、分类名称等字段
• 表2：子分类表，是表1的子分类，存储二级分类数据，包含ID、关联的主分类ID、子分类名称等字段
• 表3：项目表，存储归属表1和表2的项目数据，部分项目无所属子分类，包含ID、关联的主分类ID、可选的子分类ID、项目名称等字段

整体关联逻辑：表1的每个主分类下可包含多个表2的子分类，表3的项目必须归属某个主分类，可选择归属该主分类下的子分类，也可不关联子分类。

我已实现向表1插入数据的代码，但不知道如何建立三张表之间的关联并完成数据插入。

实体初始化类 #

public class values
{
    public string code { get; set; }
    public List<string> Table1Category { get; set; } = CreateCategory()

    private static IEnumerable<string> CreateCatergory()
    {
        return new List<string>
            {
                 "Bedroom",
                 "Balcony",
                 "Kitchen"
            };
        }
    }

表1数据插入方法 #

public async Task ExecuteAsync(values command)
{
    foreach (var category in command.Table1Category)
    {
        var categoryGuid = Guid.NewGuid().ToString();
        Model category = (new Model
                              {
                                  Name = category,
                                  Id = categoryGuid,
                                  code = command.code
                              });
        await Table1.AddAsync(category);
    }
}

1. 定义带关联属性的实体类（以EF Core为例） #

首先为三个表对应的实体添加外键和导航属性，明确表间关联关系：

// 表1实体（主分类）
public class MainCategory
{
    public string Id { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }

    // 导航属性：关联的子分类集合
    public ICollection<SubCategory> SubCategories { get; set; } = new List<SubCategory>();
    // 导航属性：关联的项目集合
    public ICollection<Project> Projects { get; set; } = new List<Project>();
}

// 表2实体（子分类）
public class SubCategory
{
    public string Id { get; set; }
    public string MainCategoryId { get; set; } // 外键，关联表1的Id
    public string Name { get; set; }

    // 导航属性：所属主分类
    public MainCategory MainCategory { get; set; }
    // 导航属性：关联的项目集合
    public ICollection<Project> Projects { get; set; } = new List<Project>();
}

// 表3实体（项目）
public class Project
{
    public string Id { get; set; }
    public string MainCategoryId { get; set; } // 外键，关联表1的Id
    public string? SubCategoryId { get; set; } // 可选外键，允许为空（无所属子分类时用）
    public string Name { get; set; }

    // 导航属性：所属主分类
    public MainCategory MainCategory { get; set; }
    // 导航属性：所属子分类（可选）
    public SubCategory? SubCategory { get; set; }
}

2. 实现关联数据的插入逻辑 #

修改或新增方法，同步插入主分类、子分类和项目，并维护关联关系：

public async Task InsertAssociatedDataAsync(string code, Dictionary<string, List<string>> mainSubMap, List<(string ProjectName, string MainCatName, string? SubCatName)> projects)
{
    // 1. 插入主分类，记录主分类名称与ID的映射
    var mainCatIdMap = new Dictionary<string, string>();
    foreach (var mainCatName in mainSubMap.Keys)
    {
        var mainCat = new MainCategory
        {
            Id = Guid.NewGuid().ToString(),
            Code = code,
            Name = mainCatName
        };
        await Table1.AddAsync(mainCat);
        mainCatIdMap[mainCatName] = mainCat.Id;
    }
    await _context.SaveChangesAsync();

    // 2. 插入子分类，关联对应主分类
    var subCatIdMap = new Dictionary<string, string>();
    foreach (var (mainCatName, subCatNames) in mainSubMap)
    {
        var mainCatId = mainCatIdMap[mainCatName];
        foreach (var subCatName in subCatNames)
        {
            var subCat = new SubCategory
            {
                Id = Guid.NewGuid().ToString(),
                MainCategoryId = mainCatId,
                Name = subCatName
            };
            await Table2.AddAsync(subCat);
            subCatIdMap[$"{mainCatName}_{subCatName}"] = subCat.Id;
        }
    }
    await _context.SaveChangesAsync();

    // 3. 插入项目，关联主分类和可选子分类
    foreach (var (projectName, mainCatName, subCatName) in projects)
    {
        var project = new Project
        {
            Id = Guid.NewGuid().ToString(),
            MainCategoryId = mainCatIdMap[mainCatName],
            Name = projectName
        };
        // 若存在子分类，设置子分类ID
        if (!string.IsNullOrEmpty(subCatName))
        {
            project.SubCategoryId = subCatIdMap[$"{mainCatName}_{subCatName}"];
        }
        await Table3.AddAsync(project);
    }
    await _context.SaveChangesAsync();
}

3. 调用示例 #

// 构造测试数据：主分类-子分类映射，项目列表
var mainSubMap = new Dictionary<string, List<string>>
{
    { "Bedroom", new List<string> { "Bed", "Wardrobe" } },
    { "Kitchen", new List<string> { "Fridge", "Stove" } }
};

var projects = new List<(string, string, string?)>
{
    ("King Size Bed", "Bedroom", "Bed"),
    ("Built-in Wardrobe", "Bedroom", "Wardrobe"),
    ("Small Balcony Table", "Balcony", null), // 无所属子分类
    ("Stainless Steel Fridge", "Kitchen", "Fridge")
};

// 执行插入
await InsertAssociatedDataAsync("HOME001", mainSubMap, projects);

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