实战项目-连接查询

链接查询

1. 在 DAO 层进行单表查询，Service 层调用两个 DAO 并进行多表查询后封装

优点：

代码复用性：单表查询的方法可以在不同的业务场景中复用。
职责分离：每个 DAO 负责单一的数据表操作，保持了职责的清晰和单一。
测试方便：单表查询的方法更容易进行单元测试。
灵活性：可以根据需要灵活组合不同的 DAO 方法来满足复杂的业务需求。

缺点：

多次数据库访问：可能会导致多次数据库访问，增加了网络延迟和资源消耗。
事务管理复杂：如果需要跨多个 DAO 的事务管理，可能会增加复杂性。
性能问题：多次查询可能会影响性能，特别是在大数据量的情况下。

2. 直接在 DAO 层进行多表查询并封装 VO（视图对象）

优点：

减少数据库访问：一次查询可以获取所有需要的数据，减少了网络延迟和资源消耗。
性能优化：通过减少数据库访问次数，可以显著提高性能。
事务管理简单：在一个事务中完成所有操作，简化了事务管理。
封装性好：可以直接返回一个封装好的 VO 对象，减少了服务层的处理逻辑。

缺点：

代码复用性较低：多表查询的方法通常更特定于某个业务场景，复用性较低。
DAO 层复杂度增加：DAO 层的代码会变得更加复杂，因为需要处理多表关联和数据封装。
测试难度增加：多表查询的方法可能更难进行单元测试，尤其是当涉及到复杂的 SQL 查询时。
耦合性较高：DAO 层直接封装了多个表的数据，可能会导致更高的耦合性。

LINQ 查询

public DishPageResp GetPage(DishPageQueryReq dishPageQueryReq,
    bool isAsNoTracking = true)
{
    Queryable<xiaobai_cangqiong_Storage.Entity.Dish> pagedDishes = _dbContext.Dishes;

    if (isAsNoTracking)
    {
        pagedDishes = pagedDishes.AsNoTracking();
    }

    // 拼接查询条件
    if (dishPageQueryReq.Name != null)
    {
        pagedDishes = pagedDishes.Where(d => d.Name.Contains(dishPageQueryReq.Name));
    }

    if (dishPageQueryReq.CategoryId != 0)
    {
        pagedDishes = pagedDishes.Where(d => d.CategoryId == dishPageQueryReq.CategoryId);
    }

    if (dishPageQueryReq.Status != null)
    {
        pagedDishes = pagedDishes.Where(d => d.Status == dishPageQueryReq.Status);
    }

    var total = pagedDishes.Count();

    // 分页查询(以创建时间排序)
    pagedDishes = pagedDishes.OrderBy(d => d.CreateTime)
        .Skip((dishPageQueryReq.Page - 1) * dishPageQueryReq.PageSize)
        .Take(dishPageQueryReq.PageSize);
    
    // 链接查询
    var dishPageVos = pagedDishes.Join(_dbContext.Categories, dish => dish.CategoryId, category => category.Id,
        (dish, category) => new DishPageVo
        {
            Id = dish.Id,
            Name = dish.Name,
            CategoryId = dish.CategoryId,
            Price = dish.Price,
            Image = dish.Image,
            Description = dish.Description,
            Status = dish.Status,
            UpdateTime = dish.UpdateTime,
            CategoryName = category.Name
        });
    return new DishPageResp
    {
        Total = total,
        Records = dishPageVos.ToList(),
    };
}

内连接查询

在这里，我们链接查询后，手动将结果集映射到VO对象中

在这种对一关系中，我们可以直接简单的进行内连接查询，因为内连接查询效率最高

当有一个菜品的分类为空时，则需要用到外连接查询，但在咱们的项目中没有分类为空的菜品，所以没有这种需求

var dishPageVos = pagedDishes.Join(_dbContext.Categories, dish => dish.CategoryId, category => category.Id,
    (dish, category) => new DishPageVo
    {
        Id = dish.Id,
        Name = dish.Name,
        CategoryId = dish.CategoryId,
        Price = dish.Price,
        Image = dish.Image,
        Description = dish.Description,
        Status = dish.Status,
        UpdateTime = dish.UpdateTime,
        // 链接查询分类名称
        CategoryName = category.Name
    });

外连接查询

在这里，我们实际项目中的菜品数据是必须存在于分类的，那么使用链接查询即可完成业务需求

但如果存在菜品是没有被分类的，则需要用到外连接查询

GroupJoin

将主表（如 pagedDishes）中的每个元素与从表（如 _dbContext.Categories）中的元素进行分组连接。
结果是一个匿名对象，其中包含主表中的每个元素和一个从表中匹配的元素集合（如果有的话）。

SelectMany

使用 SelectMany 对 GroupJoin 的结果进行展平。
如果某个主表元素在从表中没有匹配项，则使用 DefaultIfEmpty() 方法提供一个默认值（通常为 null）。
最终结果是一个扁平化的集合，包含了所有主表元素及其对应的从表元素（如果有）或默认值（如果没有匹配项）。

var dishPageVos = pagedDishes
    // 使用 GroupJoin 进行分组连接
    .GroupJoin(
        _dbContext.Categories,  // 要连接的类别集合
        dish => dish.CategoryId,  // 从每个 dish 中提取 CategoryId
        category => category.Id,  // 从每个 category 中提取 Id
        (dish, categoriesGroup) => new { Dish = dish, CategoriesGroup = categoriesGroup.DefaultIfEmpty() }  // 结果选择器
    )
    // 使用 SelectMany 展平结果，并处理没有匹配 Category 的情况
    .SelectMany(
        x => x.CategoriesGroup,  // 如果没有匹配的 Category，则返回默认值（null）
        (x, c) => new DishVo  // 投影结果到 DishPageVo 对象
        {
            Id = x.Dish.Id,  // 设置 DishPageVo 的 Id 属性为 dish 的 Id
            Name = x.Dish.Name,  // 设置 DishPageVo 的 Name 属性为 dish 的 Name
            CategoryId = x.Dish.CategoryId,  // 设置 DishPageVo 的 CategoryId 属性为 dish 的 CategoryId
            Price = x.Dish.Price,  // 设置 DishPageVo 的 Price 属性为 dish 的 Price
            Image = x.Dish.Image,  // 设置 DishPageVo 的 Image 属性为 dish 的 Image
            Description = x.Dish.Description,  // 设置 DishPageVo 的 Description 属性为 dish 的 Description
            Status = x.Dish.Status,  // 设置 DishPageVo 的 Status 属性为 dish 的 Status
            UpdateTime = x.Dish.UpdateTime,  // 设置 DishPageVo 的 UpdateTime 属性为 dish 的 UpdateTime
            // 如果 category 为 null，则将 CategoryName 设置为 "未分类"，否则设置为 category 的 Name
            CategoryName = c != null ? c.Name : "未分类"
        }
    ).ToList();

代码详解：

在进行.GroupJoin分组查询之后，产生的结果集（x）为一个元素（Dish）和其关联的结果集（CategoriesGroup）的集合

.SelectMany的第一个参数为x => x.CategoriesGroup，其含义是将其关联的结果集（CategoriesGroup）展平

这里的两个参数(x, category)，x为结果集，category为关联的结果集（CategoriesGroup）的元素中的一个 Category 对象，或者 null（如果没有匹配的 Category）

一对多关系处理

在Mybatis中，我们使用resultMap和collection来完成一对多的关系处理

在使用GroupJoin方法进行外连接查询后，第一个类型中的每一个元素都包含一个第二个类型的一个元素集合

我们使用SelectMany的方式展开，就实现了数据库左外连接查询的结果

所以通过一个简单的GroupJoin配合Select，就在ASP.NETCore中完成了一对多关系的处理

在这里，我们不需要将dishFlavor结果集展平，因为我们需要的是Flavor（List<DishFlavor>）集合，所以直接ToList为其赋值即可

IQueryable<xiaobai_cangqiong_Storage.Entity.Dish> queryable = _dbContext.Dishes;
queryable.GroupJoin(_dbContext.DishFlavors
        , d => d.Id
        , d => d.DishId
        , (dish, dishFlavor) => new { Dish = dish, DishFlavor = dishFlavor.DefaultIfEmpty() })
    .Select(x => new DishListResp
        {
            Id = x.Dish.Id,
            Name = x.Dish.Name,
            CategoryId = x.Dish.CategoryId,
            Price = x.Dish.Price,
            Image = x.Dish.Image,
            Description = x.Dish.Description,
            Status = x.Dish.Status,
            UpdateTime = x.Dish.UpdateTime,
            Flavors = x.DishFlavor.ToList(),
        });

LINQ表达式语法

我们发现，在进行多表的连接查询时，LINQ的方法语法会比表达式语法更加麻烦

再表达式语法中，如果进行外连接查询，会自动处理Flavors = flavors.DefaultIfEmpty()，且这种写法简单，更符合SQL语义

// 使用 Join 进行内连接
var dishPageVos = from dish in pagedDishes
                  join category in _dbContext.Categories on dish.CategoryId equals category.Id
                  select new DishPageVo
                  {
                      Id = dish.Id,
                      Name = dish.Name,
                      CategoryId = dish.CategoryId,
                      Price = dish.Price,
                      Image = dish.Image,
                      Description = dish.Description,
                      Status = dish.Status,
                      UpdateTime = dish.UpdateTime,
                      CategoryName = category.Name
                  };

// 使用Join …… into进行左外连接
var dishListResps = (
    from dish in _dbContext.Dishes
    join dishFlavor in _dbContext.DishFlavors 
    on dish.Id equals dishFlavor.DishId into dishFlavorGroup
    select new DishListResp
    {
        Id = dish.Id,
        Name = dish.Name,
        CategoryId = dish.CategoryId,
        Price = dish.Price,
        Image = dish.Image,
        Description = dish.Description,
        Status = dish.Status,
        UpdateTime = dish.UpdateTime,
        Flavors = dishFlavorGroup.ToList()
    }
).ToList(); 

// 使用 from 子句展平结果，代替SelectMany
var dishPageVos = (
    from dish in pagedDishes
    // 使用 join...into 进行分组连接
    join category in _dbContext.Categories
        on dish.CategoryId equals category.Id into categoriesGroup
    // 使用 from 子句展平结果
    from c in categoriesGroup.DefaultIfEmpty()
    // 选择新的 DishVo 对象
    select new DishVo
    {
        Id = dish.Id,
        Name = dish.Name,
        CategoryId = dish.CategoryId,
        Price = dish.Price,
        Image = dish.Image,
        Description = dish.Description,
        Status = dish.Status,
        UpdateTime = dish.UpdateTime,
        // 如果 category 为 null，则将 CategoryName 设置为 "未分类"，否则设置为 category 的 Name
        CategoryName = c != null ? c.Name : "未分类"
    }
).ToList();

总结

对于数据库关系来说，对一关系使用内连接来完成，高效简单，对于有需求用到外连接的内容，使用from子句展平对象后取值即可

对多关系多用外连接处理，使用into关键字将被链接表做成集合，可调用ToList转至集合后直接赋值到VO对象的属性中

对于LINQ的语法方式选择来说，简单语句使用LINQ方法语法更简单，利用Lambda表达式合适

复杂语法，涉及到多表查询的语法要选择方法语法简单，连接写起来更类SQL

// 三表联查
var dishListResps = from dish in queryable
    join dishFlavor in _dbContext.DishFlavors
        on dish.Id equals dishFlavor.DishId into dishFlavorGroup
    join category in _dbContext.Categories
        on dish.CategoryId equals category.Id
    select new DishListResp
    {
        Id = dish.Id,
        Name = dish.Name,
        CategoryId = dish.CategoryId,
        Price = dish.Price,
        Image = dish.Image,
        Description = dish.Description,
        Status = dish.Status,
        UpdateTime = dish.UpdateTime,
        // 来自于分类表的分类名称
        CategoryName = category.Name,
        // 来自于口味表中的口味数据
        Flavors = dishFlavorGroup.ToList()
    };

NET框架相关 > net

实战项目-连接查询

http://blog.170827.xyz/2024/12/06/实战项目-连接查询/

作者

XIAOBAI

发布于

2024年12月6日

许可协议

实战项目-笔记上一篇

实战项目-双端验证下一篇