Linq 语法及数据库查询

最新推荐文章于 2023-12-23 13:22:24 发布

原创最新推荐文章于 2023-12-23 13:22:24 发布 · 414 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍了使用LINQ进行各种数据操作的方法，包括获取最大值、分组、常规查询、条件查询、连接查询、分页查询等，提供了丰富的代码示例，帮助读者掌握LINQ的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

获取某一列的最大值

int currentMax = db.ZR_T_Dump.Select(p =>  p.OBJECTID).Max();

GroupBy:

 var x = subs.GroupBy(s => s.ParentID).Select(p => p.Key).ToArray();

获取一行

data = db.Demo.FirstOrDefault(p => p.ID.Equals(model.FacilityID.Trim()) && p.DeleteFlag.Equals("0"));

常规查询及条件查询：

var datas= from A in db.ZR_M_Village
              .Where(p => p.DeleteFlag.Equals("0"))
              select new Synch_CellListData
              {
                  CellID = A.VillageGuid, //小区ID
                  CommunityID = A.Community_ID, //社区ID
                  CellCode = A.VillageCode, //小区代码
              };
              
	           if (StringUtils.isNotBlank(CellName))
	           {
	               datas= datas.Where(p => p.CellName.Equals(CellName));
	           }

或者: 查询到的结构是一个IQueryable 对象

var cellObj = from A in db.ZR_M_Village
          .Where(p => p.DeleteFlag.Equals("0")) select A;

连接查询：

 var cellObj = from A in buildingObj
               join B in db.ZR_M_Village on A.BuildingID equals B.VillageGuid
               select new Synch_CellListData
               {
                   CellID = B.VillageGuid, //小区ID
                   CommunityID = B.Community_ID, //社区ID
                   CellCode = B.VillageCode, //小区代码
                   CellName = B.VillageName, //小区名称
                   Remark = B.Remark,  //备注
                   Introduce = B.Introduce,  //简介
                   _DeleteFlag = B.DeleteFlag,
                   _UpdateTime = B.UpdateTime,
               };

相当于： inner join 查询，得到两个表相对应的值，如果右边表没有数据，左边表也不显示（适用通过左边表查询右边表的数值）

  SELECT * FROM ZR_M_Building b 
  join ZR_M_Door d  
   ON  b.BuildingGuid = d.Building_ID 
  WHERE d.DoorGuid = '6001df92-7d38-4dea-8c1b-24c5d9be17b2'

连接查询：(左连接)

var buildingObj = from A in cellObj
              join B in db.ZR_M_Building.Where(p => p.DeleteFlag.Equals("0") )
              on A.CellID equals B.Village_ID 
              into temp
              from v in temp.DefaultIfEmpty()
              select new Synch_BuildingInfoData
              {
                  BuildingID = v.BuildingGuid,
                  CellID = v.Village_ID, //小区ID
                  BuildingCode = v.BuildingCode, //楼栋代码
                  BuildingName = v.BuildingName, //楼栋名称      
                  _DeleteFlag = v.DeleteFlag,
                  _UpdateTime = v.UpdateTime,
              };

相当于：左边的表的数据全部显示出来，得到的数据（这是右边表如果没有数据，则字段全部显示为null）

  SELECT * FROM ZR_M_Village v 
  LEFT JOIN ZR_M_Building b ON v.VillageGuid = b.Village_ID
  LEFT JOIN ZR_M_Door d ON b.BuildingGuid = d.Building_ID
  WHERE v.VillageGuid = 'd7fe7f54-31f8-4ec7-835b-2107b6657186'

在这里插入图片描述

如果右边表有多条左边表可以重复显示

分页查询：

var page = 1;
var pageSize = 10;
var query = (from user in db.Set<User>()
             join userRole in db.Set<UserRole>() on user.Id equals userRole.UserId
             join rolePrivilege in db.Set<RolePrivilege>() on userRole.RoleId equals rolePrivilege.RoleId
             join priviege in db.Set<Privilege>() on rolePrivilege.PrivilegeId equals priviege.Id
             join role in db.Set<Role>() on userRole.RoleId equals role.Id
             where user.Id == 1 && userRole.RoleId == 1
             orderby user.Id descending
             select new
             {
                 user.Id,
                 userRole.RoleId,
                 user.Username,
                 PrivilegeName = priviege.Name,
                 RoleName = role.Name
             }).Skip((page - 1) * pageSize).Take(pageSize);