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）：
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]
1. public object GetListAdmin()
2. {
3. //return db_C56.Admins
4. // .Where(a => a.Status != "D").ToList();
5.
6. 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. in db_C56.Admins
19. in db_C56.Area
20. on a.AreaID equals ar.ID
21. "D"
22. 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]
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]
1. var query1 = from p in dbContent.PostService
2. "product" &&
3. in dbContent.OrderItemmetaService
4. where
5. in dbContent.OrderItemsService
6. "line_item" &&
7. in dbContent.PostService
8. "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. new
13. {
14. id = p.ID,
15. name = p.post_title
16. };
17.
18. var query2 = dbContent.PostService.Where(p =>
19. "product" &&
20. (dbContent.OrderItemmetaService.Where(ot1 =>
21. (dbContent.OrderItemsService.Where(ot2 =>
22. "line_item" && (dbContent.PostService.Where(p1 =>
23. "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. 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)
}
)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
