• 【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;
            }

        }
    }

  • 相关阅读:
    html5新特性和移除的元素
    ⽹站性能优化
    JavaScript回调函数
    iframe
    JavaScript数组的方法
    Unity 新版本与 UniRx 冲突
    计算机速成课 第十八集 操作系统
    低代码 APaas
    EFCore从已有数据库反向生成Models
    TimescaleDB 使用日常记录
  • 原文地址:https://www.cnblogs.com/lanofsky/p/14034086.html
Copyright © 2020-2023  润新知