Linq 多表连接查询join

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);

LINQ，EF联合查询join

[csharp]  view plain  copy

1. public object GetListAdmin()  
2.         {  
3.             //return db_C56.Admins  
4.             //   .Where(a => a.Status != "D").ToList();  
5.   
6.             var query1 = db_C56.Admins.Join(db_C56.Area, a => a.AreaID, ar => ar.ID, (a, ar) => new  
7.             {  
8.                 userName = a.UserName,  
9.                 pwd = a.Password,  
10.                 dName = a.DisplayName,  
11.                 areaId = a.AreaID,  
12.                 hasNode = a.HasNode,  
13.                 roleName = a.RoleName,  
14.                 status = a.Status,  
15.                 areaName = ar.Name  
16.             });  
17.   
18.             var query = from a in db_C56.Admins  
19.                         join ar in db_C56.Area  
20.                         on a.AreaID equals ar.ID  
21.                         where a.Status != "D"  
22.                         select new  
23.                         {  
24.                             userName = a.UserName,  
25.                             pwd = a.Password,  
26.                             dName = a.DisplayName,  
27.                             areaId = a.AreaID,  
28.                             hasNode = a.HasNode,  
29.                             roleName = a.RoleName,  
30.                             status = a.Status,  
31.                             areaName = ar.Name  
32.                         };  
33.             return query.ToList().Select(C => new Admin  
34.             {  
35.                 UserName = C.userName,  
36.                 Password = C.pwd,  
37.                 DisplayName = C.dName,  
38.                 AreaID = C.areaId,  
39.                 AreaPath = C.areaName,  
40.                 HasNode = C.hasNode,  
41.                 RoleName = C.roleName,  
42.                 Status = C.status,  
43.             });  
44.         }  

[html]  view plain  copy

1. from v in Pdt_Versions  
2. join t in Tb_TypeDics   
3. on v.TypeName equals t.TypeName into ignored  
4. from i in ignored.DefaultIfEmpty()  
5. where v.Status != "D"  
6. select new   
7. {  
8. ID = v.ID,  
9. VersionName = v.VersionName,  
10. VersionCode = v.VersionCode,  
11. DownloadName = v.DownloadName,  
12. DownloadURL = v.DownloadURL,  
13. VType = v.VType,  
14. TypeName = v.TypeName,  
15. DisplyTypeName = i.DisplyTypeName,  
16. }  

Linq 多层嵌套查询

[csharp]  view plain  copy

1. var query1 = from p in dbContent.PostService  
2.              where p.post_type == "product" &&  
3.                  (from ot1 in dbContent.OrderItemmetaService  
4.                   where  
5.                       (from ot2 in dbContent.OrderItemsService  
6.                        where ot2.order_item_type == "line_item" &&  
7.                            (from p1 in dbContent.PostService  
8.                             where p1.post_type == "shop_order" && p1.post_author == userid && p1.post_status == "wc-completed"  
9.                             select p1.ID).Contains(ot2.order_id)  
10.                        select ot2.order_item_id).Contains(ot1.meta_id)  
11.                   select ot1.meta_value).Contains(p.ID)  
12.              select new  
13.              {  
14.                  id = p.ID,  
15.                  name = p.post_title  
16.              };  
17.   
18. var query2 = dbContent.PostService.Where(p =>  
19.     p.post_type == "product" &&  
20.     (dbContent.OrderItemmetaService.Where(ot1 =>  
21.         (dbContent.OrderItemsService.Where(ot2 =>  
22.             ot2.order_item_type == "line_item" && (dbContent.PostService.Where(p1 =>  
23.                 p1.post_type == "shop_order" && p1.post_author == userid && p1.post_status == "wc-completed").Select(p1 => p1.ID).Contains(ot2.order_item_id))  
24.                 ).Select(ot2 => ot2.order_item_id).Contains(ot1.meta_id))  
25.                 ).Select(ot1 => ot1.meta_value).Contains(p.ID))  
26.                 ).Select(p => new  
27.                 {  
28.                     id = p.ID,  
29.                     name = p.post_title  
30.                 }).ToList();  

Left Join 查询

from d in Doctors
join c in (
(from t in Commentaries where t.State != 'D' group t by new { t.DoctorID } into g 
select new {
DoctorID = (Int64?)g.Key.DoctorID,
Total = (Int32?)g.Sum(p => p.Rating),
Evaluate = (System.Double?)g.Average(p => p.Rating)
})) on new { UserID = d.UserID } equals new { UserID = (Int64)c.DoctorID }into a_join
from p in a_join.DefaultIfEmpty()
select new {
  d.ID,
  UserID = (Int64?)d.UserID,
  d.Name,
  Evaluate = ((int?)p.Evaluate ?? (int?)0)
}

Lambda表达式

Doctors
   .GroupJoin (
      Commentaries
         .Where (t => ((Int32)(t.State) != 68))
         .GroupBy (
            t => 
               new  
               {
                  DoctorID = t.DoctorID
               }
         )
         .Select (
            g => 
               new  
               {
                  DoctorID = (Int64?)(g.Key.DoctorID), 
                  Total = (Int32?)(g.Sum (p => p.Rating)), 
                  Evaluate = (Double?)(g.Average (p => p.Rating))
               }
         ), 
      d => 
         new  
         {
            UserID = d.UserID
         }, 
      c => 
         new  
         {
            UserID = (Int64)(c.DoctorID)
         }, 
      (d, a_join) => 
         new  
         {
            d = d, 
            a_join = a_join
         }
   )
   .SelectMany (
      temp0 => temp0.a_join.DefaultIfEmpty (), 
      (temp0, p) => 
         new  
         {
            ID = temp0.d.ID, 
            UserID = (Int64?)(temp0.d.UserID), 
            Name = temp0.d.Name, 
            Evaluate = ((Int32?)(p.Evaluate) ?? (Int32?)0)
         }
   )

======================================================================

多个left join

from d in Doctors
join f in Functions on new { FunctionID = d.FunctionID } equals new { FunctionID = f.ID } into b_join
from f in b_join.DefaultIfEmpty()
join c in (
(from t in Commentaries where t.State != 'D' group t by new {t.DoctorID } into g
select new {
 DoctorID = (Int64?)g.Key.DoctorID,
 Total = (Int32?)g.Sum(p => p.Rating),
 Evaluate = (System.Double?)g.Average(p => p.Rating)
})) on new { UserID = d.UserID } equals new { UserID = (Int64)c.DoctorID } into a_join
from c in a_join.DefaultIfEmpty()
select new {
  d.ID,
  UserID = (Int64?)d.UserID,
  d.AvatarPic,
  d.Name,
  f.Title,
  f.ContentDescribe,
  Evaluate = ((int?)c.Evaluate ?? (int?)0)
}

Lambda表达式

Doctors
   .GroupJoin (
      Functions, 
      d => 
         new  
         {
            FunctionID = d.FunctionID
         }, 
      f => 
         new  
         {
            FunctionID = f.ID
         }, 
      (d, b_join) => 
         new  
         {
            d = d, 
            b_join = b_join
         }
   )
   .SelectMany (
      temp0 => temp0.b_join.DefaultIfEmpty (), 
      (temp0, f) => 
         new  
         {
            temp0 = temp0, 
            f = f
         }
   )
   .GroupJoin (
      Commentaries
         .Where (t => ((Int32)(t.State) != 68))
         .GroupBy (
            t => 
               new  
               {
                  DoctorID = t.DoctorID
               }
         )
         .Select (
            g => 
               new  
               {
                  DoctorID = (Int64?)(g.Key.DoctorID), 
                  Total = (Int32?)(g.Sum (p => p.Rating)), 
                  Evaluate = (Double?)(g.Average (p => p.Rating))
               }
         ), 
      temp1 => 
         new  
         {
            UserID = temp1.temp0.d.UserID
         }, 
      c => 
         new  
         {
            UserID = (Int64)(c.DoctorID)
         }, 
      (temp1, a_join) => 
         new  
         {
            temp1 = temp1, 
            a_join = a_join
         }
   )
   .SelectMany (
      temp2 => temp2.a_join.DefaultIfEmpty (), 
      (temp2, c) => 
         new  
         {
            ID = temp2.temp1.temp0.d.ID, 
            UserID = (Int64?)(temp2.temp1.temp0.d.UserID), 
            AvatarPic = temp2.temp1.temp0.d.AvatarPic, 
            Name = temp2.temp1.temp0.d.Name, 
            Title = temp2.temp1.f.Title, 
            ContentDescribe = temp2.temp1.f.ContentDescribe, 
            Evaluate = ((Int32?)(c.Evaluate) ?? (Int32?)0)
         }
   )