• Linq 多表连接查询join


    在查询语言中,通常需要使用联接操作。在 LINQ 中,可以通过 join 子句实现联接操作。join 子句可以将来自不同源序列,并且在对象模型中没有直接关系(数据库表之间没有关系)的元素相关联,唯一的要求是每个源中的元素需要共享某个可以进行比较,以判断是否相等的值。

    LINQ 中,join 句可以实现 3 种类型的分别是内部联接、接和部联接。

    1、内部连接(相对于sql:join | inner join)

    格式:join element in dataSource on exp1 equals exp2

    int[] intAry1 = {5, 15, 25, 30, 33, 40};//创建整数数组 intAry1 作为数据源
    int[] intAry2 = {10, 20, 30, 50, 60, 70, 80};//创建整数数组 intAry2 作为数据源
    //查询 query1 使用 join 子句从两个数据源获取数据
    //演示内部联接的使用
    var query1 =
    from val1 in intAry1
    join val2 in intAry2 on val1%5 equals val2%15
    select new {VAL1=val1, VAL2=val2};

    2、分组连接

    格式: join element in dataSource on exp1 equals exp2 into grpName

    其中,into 字表示将这些数据组并保存到 grpName 中,grpName 是保存一组数据的集合。(感觉和sql不同,sql查询的结果是平面矩形的,而linq则是平面树形的,意思是像对象的元素也是个对象)  

    int[] intAry1 = { 5, 15, 25, 30, 33, 40 };//创建整数数组 intAry1 作为数据源
    int[] intAry2 = { 10, 20, 30, 50, 60, 70, 80 };//创建整数数组 intAry2 作为数据源
    //查询 query1 使用 join 子句从两个数据源获取数据
    //演示分组联接的使用
    var query1 =
    from val1 in intAry1
    join val2 in intAry2 on val1 % 5 equals val2 % 15 into val2Grp
    select new { VAL1 = val1, VAL2GRP = val2Grp};

    3、左外部联接 (相对于sql:left join | left outer join)

    第三种联接是左外部联接,它返回第一个集合中的所有元素,无论它是否在第二个集合中有相关元素。在 LINQ 中,通过对分组联接的结果调用 DefaultIfEmpty()方法来执行左外部联接。DefaultIfEmpty()方法从列表中获取指定元素。如果列表为空,则返回默认值。

    int[] intAry1 = { 5, 15, 23, 30, 33, 40 };//创建整数数组 intAry1 作为数据源
    int[] intAry2 = { 10, 20, 30, 50, 60, 70, 80 };//创建整数数组 intAry2 作为数据源
    //查询 query1 使用 join 子句从两个数据源获取数据
    //演示左联接的使用
    var query1 =
    from val1 in intAry1
    join val2 in intAry2 on val1 % 5 equals val2 % 15 into val2Grp
    from grp in val2Grp.DefaultIfEmpty()
    select new { VAL1 = val1, VAL2GRP = grp };

    查询方法Lambda示例(GroupJoin):

    原形:https://msdn.microsoft.com/zh-cn/library/bb534297(v=vs.105).aspx

    public static IEnumerable<TResult> GroupJoin<TOuter, TInner, TKey, TResult>(
        this IEnumerable<TOuter> outer,
        IEnumerable<TInner> inner,
        Func<TOuter, TKey> outerKeySelector,
        Func<TInner, TKey> innerKeySelector,
        Func<TOuter, IEnumerable<TInner>, TResult> resultSelector
    )

    重载

    public static IEnumerable<TResult> GroupJoin<TOuter, TInner, TKey, TResult>(
        this IEnumerable<TOuter> outer,
        IEnumerable<TInner> inner,
        Func<TOuter, TKey> outerKeySelector,
        Func<TInner, TKey> innerKeySelector,
        Func<TOuter, IEnumerable<TInner>, TResult> resultSelector,
        IEqualityComparer<TKey> comparer
    )
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace LinqDemo2
    {
        /// <summary>
        /// 学生实体
        /// </summary>
        public class Student
        {
            public int StudentId { get; set; }
            public string StudentName { get; set; }
            public int StandardId { get; set; }//水平
        }
    
        /// <summary>
        /// 水平/等级
        /// </summary>
        public class Standard
        {
            public int StandardId { get; set; }
            public string StandardName { get; set; }//
        }
        class Program
        {
            static void Main(string[] args)
            {
                #region 数据源
                IList<Student> studentList = new List<Student>()
                {
                    new Student() {StudentId = 1, StudentName = "John", StandardId = 1},
                    new Student() {StudentId = 2, StudentName = "Moin", StandardId = 1},
                    new Student() {StudentId = 3, StudentName = "Bill", StandardId = 2},
                    new Student() {StudentId = 4, StudentName = "Ram", StandardId = 2},
                    new Student() {StudentId = 5, StudentName = "Ron"}
                };
                IList<Standard> standardList = new List<Standard>()
                {
                    new Standard() {StandardId = 1, StandardName = "优秀"},
                    new Standard() {StandardId = 2, StandardName = "中等"},
                    new Standard() {StandardId = 3, StandardName = "差生"}
                };
                #endregion
    
                //查询公式
                var groupJoin = standardList.GroupJoin(studentList,
                    standard => standard.StandardId,
                    student => student.StandardId,
                    (standard, studentGroup) => new
                    {
                        StandarFullName = standard.StandardName,
                        Students = studentGroup
                    });//感觉和字典类型一样,一个key,对应一个velue, velue = IEnumerable<Student>
    
                //执行查询
                foreach (var item in groupJoin)
                {
                    Console.WriteLine(item.StandarFullName);
    
                    foreach (var student in item.Students)
                    {
                        Console.WriteLine(student.StudentName);
                    }
                }
    
                /* 输出:
                 * 
                优秀
                John
                Moin
                中等
                Bill
                Ram
                差生
                */
    
    
            }
        }
    }

    示例:分页查询

    var page = 1;
                var pageSize = 10;
                var query = (from user in db.Set<User>()
                             join userRole in db.Set<UserRole>() on user.Id equals userRole.UserId
                             join rolePrivilege in db.Set<RolePrivilege>() on userRole.RoleId equals rolePrivilege.RoleId
                             join priviege in db.Set<Privilege>() on rolePrivilege.PrivilegeId equals priviege.Id
                             join role in db.Set<Role>() on userRole.RoleId equals role.Id
                             where user.Id == 1 && userRole.RoleId == 1
                             orderby user.Id descending
                             select new
                             {
                                 user.Id,
                                 userRole.RoleId,
                                 user.Username,
                                 PrivilegeName = priviege.Name,
                                 RoleName = role.Name
                             }).Skip((page - 1) * pageSize).Take(pageSize);
  • 相关阅读:
    命令模式
    软件设计师_例题
    软件设计师_计算机系统基础(1.1)
    ForkJoin之ForkJoinTask框架学习笔记
    Oracle使用语句块之循环插入数据
    SpringCloud搭建分布式配置中心(基于git)
    Docker配置JDK1.8
    Linux命令查看文件内容
    Mac配置maven环境命令
    Docker安装mysql8
  • 原文地址:https://www.cnblogs.com/wanghaibin/p/6494309.html
Copyright © 2020-2023  润新知