EF使用linq和lamda实现sql查询的内连接（inner join）和左连接（left join）

背景介绍：
1.OperateLogInfo：日志表
2.SystemUserInfo：用户表
需求：查询日志表，要根据日志表的创建人id查询出创建人姓名（日志表连接用户表）

        public static string Test()
        {
            string result = null;
            var sqlLogs = new List<string>();
            EfDepositoryExtension.Execuse(dbContext =>
            {
                dbContext.Database.Log = r => sqlLogs.Add(r);

                //内连接（inner join）：lamda
                var dataListByInnerJoinLamda = dbContext.OperateLogInfo.Where(r => r.IsDel == false).Join(
                    dbContext.SystemUserInfo.Where(r => r.IsDel == false), r => r.CreateUser, r => r.UserId,
                    (o, u) => new
                    {
                        LogId = o.Id,
                        PageName = o.PageName,
                        LogContent = o.Des,
                        CreateDate = o.CreateDate,
                        CreateUserId = o.CreateUser,
                        CreateUserName = u.UserName
                    }).ToList();

                //内连接（inner join）：linq
                var dataListByInnerJoinLinq = from o in dbContext.OperateLogInfo.Where(r => r.IsDel == false)
                                              join u in dbContext.SystemUserInfo.Where(r => r.IsDel == false) on o.CreateUser equals u.UserId
                                              select new
                                              {
                                                  LogId = o.Id,
                                                  PageName = o.PageName,
                                                  LogContent = o.Des,
                                                  CreateDate = o.CreateDate,
                                                  CreateUserId = o.CreateUser,
                                                  CreateUserName = u.UserName
                                              };

                //左连接（inner join）：lamda
                var dataListByLeftJoinLamda = dbContext.OperateLogInfo.Where(r => r.IsDel == false)
                    .GroupJoin(dbContext.SystemUserInfo.Where(r => r.IsDel == false), r => r.CreateUser, r => r.UserId,
                        (o, uList) => new { Log = o, Users = uList }).SelectMany(r => r.Users.DefaultIfEmpty(),
                        (ou, u) => new { Log = ou.Log, User = u }).Select(r => new
                        {
                            LogId = r.Log.Id,
                            PageName = r.Log.PageName,
                            LogContent = r.Log.Des,
                            CreateDate = r.Log.CreateDate,
                            CreateUserId = r.Log.CreateUser,
                            CreateUserName = r.User.UserName
                        }).ToList();

                //左连接（inner join）：linq
                var dataListByLeftJoinLinq = from o in dbContext.OperateLogInfo.Where(r => r.IsDel == false)
                                             join u in dbContext.SystemUserInfo.Where(r => r.IsDel == false) on o.CreateUser equals u.UserId into
                                                 ouJoin
                                             from ou in ouJoin.DefaultIfEmpty()
                                             select new
                                             {
                                                 LogId = o.Id,
                                                 PageName = o.PageName,
                                                 LogContent = o.Des,
                                                 CreateDate = o.CreateDate,
                                                 CreateUserId = o.CreateUser,
                                                 CreateUserName = ou.UserName
                                             };
            });

            LogTestService.Info(null, sqlLogs.ToArray());
            return result;
        }

 

生成的sql：

 

Ending~