【C#】【Demo】.net的linq左连接

using ElevatorAdvertising.Common;
using ElevatorAdvertising.Model;
using ElevatorAdvertising.Model.Entites;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace ElevatorAdvertising.DALMSSQL
{
    public partial class Staff_BaseInfoDAL
    {
        /// <summary>
        /// 查询员工信息
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public PageModel<IList<Staff_BaseInfoModel>> GetPageList(SearchStaff search)
        {
            //左表条件
            var where_base = PredicateBuilder.True<Staff_BaseInfo>();
            Expression<Func<Staff_BaseInfo, bool>> ex = a => a.CompanyId==search.CompanyId;

            //整体条件
            var wheres = PredicateBuilder.True<Staff_BaseInfoModel>();
            if (search != null)
            {
                if (search.StaffId.HasValue)
                {
                    ex = x => x.StaffId == search.StaffId.Value;
                    where_base = where_base.And(ex);

                }
                else
                {
                    //关联左表条件
                    if (!string.IsNullOrWhiteSpace(search.StaffName))
                    {
                        ex = x => x.StaffName.Contains(search.StaffName);
                        where_base = where_base.And(ex);
                    }
                    if (!string.IsNullOrWhiteSpace(search.StaffPhone))
                    {
                        ex = x => x.StaffPhone.Contains(search.StaffPhone);
                        where_base = where_base.And(ex);
                    }
                    if (!string.IsNullOrWhiteSpace(search.LoginAccount))
                    {
                        ex = x => x.LoginAccount.Contains(search.LoginAccount);
                        where_base = where_base.And(ex);
                    }

                    //关联表条件
                    if (!string.IsNullOrWhiteSpace(search.RoleId))
                    {
                        wheres = wheres.And(x => x.RoleId.Contains(search.RoleId));
                    }
                    if (search.StaffTypeId.HasValue)
                    {
                        wheres = wheres.And(x => x.StaffTypeId == search.StaffTypeId.Value);
                    }
                    if (!string.IsNullOrWhiteSpace(search.JobNumber))
                    {
                        wheres = wheres.And(x => x.JobNumber.Contains(search.JobNumber));
                    }

                }

            }

            var items = (from list in db.Set<Staff_BaseInfo>().Where(where_base)
                         join type in db.Set<Staff_CategoryInfo>() on list.StaffTypeId equals type.CategoryId

                         //左连接start
                         join role in db.Set<Staff_RoleInfo>() on list.RoleId equals role.RoleId.ToString()
                         into roleEmp
                         from role in roleEmp.DefaultIfEmpty()

                         join jobNumber in db.Set<SysBase_JobNumber>() on list.JobNumberId equals jobNumber.JobId
                         into jobNumberEmp
                         from jobNumber in jobNumberEmp.DefaultIfEmpty()
                        //左连接end

                         select new Staff_BaseInfoModel
                         {
                             StaffId = list.StaffId,
                             StaffName = list.StaffName,
                             StaffPhone = list.StaffPhone,
                             LoginAccount = list.LoginAccount,
                             LoginPwd = list.LoginPwd,
                             HeadImg = list.HeadImg,
                             CreateTime = list.CreateTime,
                             LastLoginTime = list.LastLoginTime,
                             LockingState = list.LockingState,
                             ActivationTime = list.ActivationTime,
                             ActivationState = list.ActivationState,
                             CompanyId = list.CompanyId,
                             RoleId = list.RoleId,
                             RoleName = list.RoleName,
                             StaffTypeId = list.StaffTypeId,
                             JobNumberId = list.JobNumberId,
                             JobNumber = jobNumber.JobName,//工号
                             StaffTypeName = type.CategoryName,//员工类型名称
                             RoleNameNow = role.RoleName,//角色名称
                         }).Where(wheres);


            //分页返回总数
            PageModel<IList<Staff_BaseInfoModel>> pm = new PageModel<IList<Staff_BaseInfoModel>>();
            pm.RowCount = items.Count();

            //返回分页
            pm.Data = items.OrderByDescending(x => x.CreateTime).Skip((search.PageIndex - 1) * search.PageSize).Take(search.PageSize).ToList();
            pm.PageIndex = search.PageIndex;
            pm.PageSize = search.PageSize;

            return pm;
        }

    }
}