lambda表达式来实现Left Join和Inner Join

lambda表达式来实现Left Join和Inner Join_lambda leftjoin_陈熙之的博客-CSDN博客

 

自己做了记录

Left Join

lambda只能会写2表连接，多了就SB了。

DefaultIfEmpty()是关键。。。。。。

CustomerFollowup.Where(a => a.IsDelete == false && a.CustomerID == request.CustomerID).
GroupJoin(Org, a => a.CreateOrgId, b => b.Id, (a, b) => new { Followup = a, Org = b }).
SelectMany(t => t.Org.DefaultIfEmpty(), (a, b) => new
{ OrgName = b.Name,Contents = a.Followup.Contents,}).ToList();

 

得到的SQL是：

SELECT [o].[Name] AS [OrgName], [c].[Contents]
FROM [CustomerFollowup] AS [c]
LEFT JOIN [Org] AS [o] ON [c].[CreateOrgId] = [o].[Id]
WHERE ([c].[IsDelete] = CAST(0 AS bit)) AND ([c].[CustomerID] = @__request_CustomerID_0)

 

多表连接只好改编下，在linq里面写了，说真的，真心不喜欢linq，不能点。。。

var TT = from p in CustomerFollowup
from Org in Org.Where(c => c.Id == p.CreateOrgId).DefaultIfEmpty()
from User in User.Where(c => c.Id == p.CreateUserId).DefaultIfEmpty()
where p.IsDelete == false && p.CustomerID == request.CustomerID
select new CustomerFollowupShow
{
Contents = p.Contents,
OrgName = Org.Name,
UserName = User.Name,
};

 

var TT2 = from p in CustomerFollowup.Where(a => a.IsDelete == false && a.CustomerID == request.CustomerID)
from Org in Org.Where(c => c.Id == p.CreateOrgId).DefaultIfEmpty()
from User in User.Where(c => c.Id == p.CreateUserId).DefaultIfEmpty()
select new CustomerFollowupShow
{
Contents = p.Contents,
OrgName = Org.Name,
UserName = User.Name,
};

 

顺便提下，这个不能直接ToList，还得单独分开写。

var list1 = TT.ToList();
var list2 = TT2.ToList();

感觉不像纯的linq，lambda和linq的混合体。。。。

这2种写法，查询条件的位置不一样，得到的SQL结果是一样的：

SELECT [c].[Contents], [o].[Name] AS [OrgName], [u].[Name] AS [UserName]FROM [CustomerFollowup] AS [c]
LEFT JOIN [Org] AS [o] ON [c].[CreateOrgId] = [o].[Id]
LEFT JOIN [User] AS [u] ON [c].[CreateUserId] = [u].[Id]
WHERE ([c].[IsDelete] = CAST(0 AS bit)) AND ([c].[CustomerID] = @__request_CustomerID_0)

 

这是同事写的：

from cus in query join user in User(u => u.IsDelete == false) on cus.CreateUserId equals user.Id into usertemp from u in usertemp.DefaultIfEmpty()
join org in Org(o => o.IsDelete == false) on cus.CreateOrgId equals org.Id into orgtemp
from o in orgtemp.DefaultIfEmpty()
select new CustomersView
{
...
};

 

 

 

Inner Join

var objs = UnitWork.Find<CustomerFollowup>(a => a.IsDelete == false && a.CustomerID == request.CustomerID).

Join(UnitWork.Find<Org>(null), a => a.CreateOrgId, b => b.Id, (a, b) => new { a, OrgName = b.Name }).

Join(UnitWork.Find<User>(null), a => a.a.CreateUserId, b => b.Id, (a, b) => new { a, UserName = b.Name }).

Join(UnitWork.Find<SysParamInfo>(null), a => a.a.a.Type, b => b.ParamValue, (a, b) => new { a, b.ParamName }).

Join(UnitWork.Find<CustomerPhone>(null), a => a.a.a.a.ObjectUser, b => b.Id, (a, b) => new { a, b.Name }).

Select(a => new CustomerFollowupShow
{
Type = a.a.ParamName,
Contents = a.a.a.a.a.Contents,
CreateTime = a.a.a.a.a.CreateTime,
ObjectUser = a.Name,
OrgName = a.a.a.a.OrgName,
Other = a.a.a.a.a.Other,
UserName = a.a.a.UserName
});

 

 

感觉有点恶心

拿来做分页查询的

result.data = objs.OrderByDescending(u => u.CreateTime)
.Skip((request.page - 1) * request.limit)
.Take(request.limit);
result.count = objs.Count();

 

得到的结果：

SELECT COUNT(*)FROM[CustomerFollowup] AS[c]
INNER JOIN[Org] AS[o] ON[c].[CreateOrgId] = [o].[Id]
INNER JOIN[User] AS[u] ON[c].[CreateUserId] = [u].[Id]
INNER JOIN[SysParamInfo] AS[s] ON[c].[Type] = [s].[ParamValue]
WHERE([c].[IsDelete] = CAST(0 AS bit)) AND([c].[CustomerID] = @CustomerID)

SELECT[s].[ParamName] AS[Type], [c].[Contents], [c].[CreateTime], [c].[ObjectUser], [o].[Name] AS[OrgName], [c].[Other], [u].[Name] AS[UserName]
FROM[CustomerFollowup] AS[c]
INNER JOIN[Org] AS[o] ON[c].[CreateOrgId] = [o].[Id]
INNER JOIN[User] AS[u] ON[c].[CreateUserId] = [u].[Id]
INNER JOIN[SysParamInfo] AS[s] ON[c].[Type] = [s].[ParamValue]
WHERE([c].[IsDelete] = CAST(0 AS bit)) AND([c].[CustomerID] = @CustomerID)ORDER BY[c].[CreateTime] DES COFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

 

————————————————
版权声明：本文为CSDN博主「陈熙之」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/cmhdl521/article/details/118030452