Linq 和 lambda 联接查询

如果有数据库相关的知识，应该都很熟联接查询。在 C# 里了提供了强大的 Linq 和 lambda 联接查询。今天我们就来看下它们的用法。

执行联接操作方法

Linq 中有两种方法可用于执行联接操作。

1. Join 基于公共属性来联接两个或多个数据源，并返回单个结果集。

2. GroupJoin 基于公共属性（或公共键）联接两个或多个数据源，并返回一组序列的结果集。

lambda 的用法只有一个 join 就可以执行联接操作。

准备

先建三个类，用户类（User），角色类（Role），用户角色（UserRole），具体如下 ：  

public class User {
    public int Id { get; set; }
    public string UserName { get; set; }
    
    public virtual UserRole UserRole { get; set; }
}

public class Role {
    public int Id { get; set; }
    public string Name { get; set; }
    
    public virtual UserRole UserRole { get; set; }
}

public class UserRole {
    public int UserId { get; set; }
    public int RoleId { get; set; }
    
    public virtual User User { get; set; }

    public virtual Role Role { get; set; }
}

 创建一个继承 DbContext 的类，类名叫 YldDbContext 。该类具体如下：

public class YldContext : DbContext {
    public YldContext(DbContextOptions<YldContext> options) : base(options)
    {

    }

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

    public DbSet<Role> Roles { get; set; }

    public DbSet<UserRole> UserRoles { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var roleBuilder = modelBuilder.Entity<Role>();
        roleBuilder.ToTable("roles");
        roleBuilder.HasKey(a => a.Id);
        roleBuilder.Property(a => a.Id).HasColumnName("RoleId").ValueGeneratedOnAdd();
        roleBuilder.HasOne(a => a.UserRole).WithOne(a => a.Role).HasForeignKey<UserRole>(a => a.RoleId);
        
        var userBuilder = modelBuilder.Entity<User>();
        userBuilder.ToTable("users");
        userBuilder.HasKey(a => a.Id);
        userBuilder.Property(a => a.Id).HasColumnName("UserId").ValueGeneratedOnAdd();
        userBuilder.HasOne(a => a.UserRole).WithOne(a => a.User).HasForeignKey<UserRole>(a => a.UserId);
        
        base.OnModelCreating(modelBuilder);

    }
}

 示例 1

查询用户与角色对应的数据，Linq 的用法如下：

private readonly YldContext _context;  //通过注入实例化

var results = await _context.Users.Join(_context.UserRoles, u => u.Id, ur => ur.UserId, (u, ur) => new { UserId = u.Id, u.UserName, RoleId = ur.RoleId })
                .Join(_context.Roles, t => t.RoleId, r => r.Id, (t, r) => new { t.UserId, t.UserName, t.RoleId, RoleName = r.Name })
                .ToListAsync();

返回的结果如下：

[
    {
        "userId": 48,
        "userName": "admin",
        "roleId": 1,
        "roleName": "管理员"
    },
    {
        "userId": 59,
        "userName": "sa",
        "roleId": 136,
        "roleName": "administrator"
    }
]

 Join 方法参数说明：

Join(右边集合，左边关联属性(左表主键)，右边关联属性(右表主键)，返回集合对象(临时表))

使用 Join 生成对应的 SQL 如下：

SELECT `u`.`UserId`, `u`.`UserName`, `r`.`RoleId`, `r`.`Name` AS RoleName
FROM `users` AS `u`
INNER JOIN `userroles` AS `ur` ON `u`.`UserId` = `ur`.`UserId`
INNER JOIN `roles` AS `r` ON `ur`.`RoleId` = `r`.`RoleId`

 返回的结果、生成的 SQL 与 Linq 一样。

lambda 关联主外键时是用 equals。但过滤条件仍是 Linq 表达式。

示例 2

假设我要查询一个用户配置和未配置的角色列表，如下图：

 Linq 提供了一个 GroupJoin 的方法。具体用法如下：

private readonly YldContext _context;  //通过注入实例化

var results = await _context.Roles.GroupJoin(_context.UserRoles, r => new { RoleId = r.Id, UserId = 48 }, ur => new { ur.RoleId, ur.UserId} , (r, ur) => new { Role = r, UserRole = ur })
    .SelectMany(t => t.UserRole.DefaultIfEmpty(), (r, ur) => new { RoleId = r.Role.Id, RoleName = r.Role.Name, IsSet = ur==null);

返回结果如下：

[
    {
        "id": 1,
        "name": "管理员",
        "isCheckd": true
    },
    {
        "id": 117,
        "name": "aaa",
        "isCheckd": false
    },
    {
        "id": 136,
        "name": "administrator",
        "isCheckd": false
    }
]

GroupJoin 方法参数说明：

GroupJoin(右边集合，左边关联属性(左表主键)，右边关联属性(右表主键)，返回集合对象(临时表))

在 GroupJoin 方法里使用匿名对象创建一个组合属性来联接两个资源，这里有一个注意的点：左右两边的匿名对象属性名及类型要一致。

使用 GroupJoin 方法生成对应的 SQL 语句如下：

SELECT `r`.`RoleId`, `r`.`Name` AS `RoleName`, `u`.`Id` IS NULL AS `IsSet`
FROM `roles` AS `r`
LEFT JOIN `userroles` AS `u` ON (`r`.`RoleId` = `u`.`RoleId`) AND (48 = `u`.`UserId`)

 再来看下 lambda 的写法：

from r in Roles
join ur in Userroles on new { RoleId = r.RoleId, UserId = 48 } equals new { ur.RoleId, ur.UserId } into temp
from t in temp.DefaultIfEmpty()
select new { r.RoleId, RoleName = r.Name, IsSet = t == null }

 返回的结果、生成的 SQL 与 Linq 一样。

今天的 Linq 和 lambda 联接查询的基本内容就到这。

最后，祝大家学习愉快！