Linq规范 · .Net 开发代码规范

## 非id查询时，索引方式必须添加TenantId 、OrgId ，因为索引会关联这两个属性 ~~~ Where(c => c.QCType.StartsWith(categoryName) && c.TenantId == AbpSession.TenantId && c.OrgId == AbpSession.OrgId) ~~~ 查询示例 --- select * from Am_recProScheme ``` var ss = from r in db.Am_recProScheme select r; 或 var ss1 = db.Am_recProScheme; ``` select * from Am_recProScheme where rpid>10 ``` var ss = from r in db.Am_recProScheme where r.rpId > 10 select r; 或 var ss1 = db.Am_recProScheme.Where(p => p.rpId > 10); ``` select max(rpId) from Am_recProSchem ``` var ss = (from r in db.Am_recProScheme select r).Max(p => p.rpId); 或 var ss1 = db.Am_recProScheme.Max(p=>p.rpId); ``` select min(rpId) from Am_recProScheme ``` var ss = (from r in db.Am_recProScheme select r).Min(p => p.rpId); 或 var ss1 = db.Am_recProScheme.Min(p => p.rpId); ``` select count(1) from Am_recProScheme ``` var ss = (from r in db.Am_recProScheme select r).Count() 或 var ss1 = db.Am_recProScheme.Count() ``` select sum(rpId) from Am_recProScheme ``` var ss = (from r in db.Am_recProScheme select r).Sum(p => p.rpId) 或 var ss1 = db.Am_recProScheme.Sum(p => p.rpId) ``` select * from Am_recProScheme where rpid>10 order by rpId [desc|asc] ``` var ss = from r in db.Am_recProScheme where r.rpId > 10 orderby r.rpId descending //倒序 // orderby r.rpId ascending //正序 select r; 或 var ss1 = db.Am_recProScheme.OrderBy(p => p.rpId).Where(p => p.rpId > 10).ToList(); 或 var ss2 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).ToList(); ``` select top(1) * from Am_recProScheme ``` var ss = (from r in db.Am_recProScheme select r).FirstOrDefault(); 或 var ss1 = db.Am_recProScheme.FirstOrDefault(); 或 var ss1 = db.Am_recProScheme.First(); ``` 跳过前面多少条数据取余下的数据 select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10 ``` var ss = (from r in db.Am_recProScheme orderby r.rpId descending select r).Skip(10); 或 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Skip(10).ToList(); ``` 分页 select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10 and rowNum<=20 ``` var ss = (from r in db.Am_recProScheme where r.rpId > 10 orderby r.rpId descending select r).Skip(10).Take(10); //取第11条到第20条数据或 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).Skip(10).Take(10).ToList(); ``` select * from Am_recProScheme where SortsText like '%张%' ``` var ss = from r in db.Am_recProScheme where r.SortsText.Contains("张") select r; 或 var ss1 = db.Am_recProScheme.Where(p => p.SortsText.Contains("张")).ToList(); ``` 分组group by select recType,min(rpId),max(rpId),sum(rpId) from Am_recProScheme group by recType ``` var ss = from r in db.Am_recProScheme orderby r.rpId descending group r by r.recType into n select new { n.Key, //这个Key是recType rpId = n.Sum(r => r.rpId), //组内rpId之和 MaxRpId = n.Max(r => r.rpId),//组内最大rpId MinRpId = n.Min(r => r.rpId), //组内最小rpId }; foreach (var t in ss) { Response.Write(t.Key + "--" + t.rpId + "--" + t.MaxRpId + "--" + t.MinRpId); } 或 var ss1 = from r in db.Am_recProScheme orderby r.rpId descending group r by r.recType into n select n; foreach (var t in ss1) { Response.Write(t.Key + "--" + t.Min(p => p.rpId)); } 或 var ss2 = db.Am_recProScheme.GroupBy(p => p.recType); foreach (var t in ss2) { Response.Write(t.Key + "--" + t.Min(p => p.rpId)); } 或 var result = (from item in data group item by new { item.Name, item.Type } into items select new { items.Key.Name, items.Key.Type, Cnt = items.Count() }).ToList(); 或 var s = data.GroupBy(p => new { p.Type, p.Name }).Select(p=>new { p.Key.Type, p.Key.Name, cnt=p.Count() }).ToList(); ``` 连接查询（内连接） select r.* from [Am_recProScheme] as r inner join [dbo].[Am_Test_Result] as t on r.[rpId] = t.[rsId] order by r.[rpId] desc ``` var ss = from r in db.Am_recProScheme join w in db.Am_Test_Result on r.rpId equals w.rsId orderby r.rpId descending select r; 或 var ss1 = db.Am_recProScheme.Join(db.Am_Test_Result, p => p.rpId, r => r.rsId, (p, r) => p).OrderByDescending(p => p.rpId).ToList(); ``` 左外连接 ``` var custs = from c in db.T_Customer join u in db.Sys_User on c.OwnerId equals u.Id into temp // 左外连接后的数据源放到临时数据源temp中 from t in temp.DefaultIfEmpty() //给temp做别名 where (c.PhoneNum1 == phone || c.PhoneNum2 == phone || c.PhoneNum3 == phone) select new { CustName = string.IsNullOrEmpty(c.CustName) ? "匿名" : c.CustName, UserName = t == null ? "暂无" : t.Name//这里主要第二个集合有可能为空。需要判断 }; ``` 案例分析 ``` var query = from m in material.GetAll() //内连接，连接可以用来查询下级数据 join route in item2Route.GetAll() on m.MaterialCode equals route.ItemCode into r //左外连接，左外连接可以用来查询上级数据 join c in materialCategory.GetAll() on new { TenantId = m.TenantId, OrgId = m.OrgId, Id = m.CategoryId } equals new { TenantId = c.TenantId, OrgId = c.OrgId, Id = c.Id } into mclist from mc in mclist.DefaultIfEmpty() select new MaterialAndCategory { Material = m, MaterialCategory = mc, IsBindRoute = ((r.Count()==0)?false:true) //内连接的r 不能直接拿过来用，判断是否为空可以用 count() 函数 }; ``` in 查询 select * from Am_recProScheme where rpId in(24,25,26) ``` var ss = from p in db.Am_recProScheme where (new int?[] { 24, 25,26 }).Contains(p.rpId) select p; foreach (var p in ss) { Response.Write(p.Sorts); } ``` 时间查询 ``` 当天时间 DateTime d = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")); predicate = predicate.And(t => t.CreationTime >= d); 本周 DateTime aa = Convert.ToDateTime(DateTime.Now.AddDays(-Convert.ToInt32(DateTime.Now.Date.DayOfWeek)).ToString("yyyy-MM-dd 23:59:59")); predicate = predicate.And(n => n.CreationTime > aa); 本月 DateTime bb = Convert.ToDateTime(DateTime.Now.AddDays(-Convert.ToInt32(DateTime.Now.Date.Day)).ToString("yyyy-MM-dd 23:59:59")); predicate = predicate.And(n => n.CreationTime > bb ); 昨天 DateTime s = Convert.ToDateTime(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 00:00:00")); DateTime e = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 00:00:00")); predicate = predicate.And(n => n.CreationTime > s && n.CreationTime < e); ```