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

 

 

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
在 Java 中,lambda 表达式通常用于函数式编程中的函数式接口,而与数据库相关的操作通常使用 SQL 语句进行处理。如果你想在 Java 中实现类似于 SQL 中的 left join 操作,可以使用一些 ORM 框架(例如 Hibernate、MyBatis 等)来简化这个过程。 下面是一个使用 MyBatis 实现 left join 的示例: 首先,在 MyBatis 的 mapper 文件中定义两个表的查询语句: ``` <select id="selectUsersWithOrders" resultMap="userResultMap"> SELECT u.*, o.order_id, o.order_date FROM user u LEFT JOIN orders o ON u.user_id = o.user_id </select> <resultMap id="userResultMap" type="User"> <id property="userId" column="user_id"/> <result property="username" column="username"/> <collection property="orders" ofType="Order"> <id property="orderId" column="order_id"/> <result property="orderDate" column="order_date"/> </collection> </resultMap> ``` 其中,第一段 SQL 查询语句实现left join,将用户表和订单表连接起来,查询出所有用户及其对应的订单信息。第二段 resultMap 定义了 User 实体类和 Order 实体类之间的映射关系。 然后,在 Java 代码中调用 MyBatis 的查询方法,即可获得 left join 后的结果: ``` List<User> users = sqlSession.selectList("selectUsersWithOrders"); ``` 这个查询操作将会返回一个包含所有用户及其对应订单信息的列表。其中,每个用户都有一个 List<Order> 类型的 orders 属性,包含了该用户的所有订单信息。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值