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

自己做了记录

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