学习LINQ to SQL查询
join，left join
static void Main(string[] args) { var db = new dbEntities(); var query = from s in db.Store join o in db.Organization on s.OrgId equals o.OrgId join o2 in db.Organization on o.ParentId equals o2.OrgId //自连接 join f in db.Tbl_FenZu on s.StoreId equals f.StoreId into f0 from f in f0.DefaultIfEmpty() //LEFT OUTER JOIN join k in db.Tbl_KaZuo on f.Id equals k.FenZuId into k0 from k in k0.DefaultIfEmpty() where s.InsertTime > new DateTime(2018, 1, 1) orderby o2.OrgId, o.OrgId, s.StoreId, f.Id, k.Id select new { 省 = o2.OrgName, 市 = o.OrgName, s.StoreId, s.StoreCode, s.FullName, f.Name, k.Code }; var dt = ToDataTable(query.ToList()); }
join 多条件
result = from c in xxx join t in xxx on new { a = c.a, b = c.b } equals new { a = t.a, b = t.b } //c.a和t.a类型要完全一致， select new { };
或者
result = from c in xxx join t in xxx on new { c.a,c.b } equals new { t.a,t.b } //c.a和t.a类型要完全一致， select new { };
group，having，聚合
var query = from s in db.Store join f in db.Tbl_FenZu on s.StoreId equals f.StoreId into f0 from f in f0.DefaultIfEmpty() group f by new { s.StoreId, s.FullName } into g where g.Count() > 1 orderby g.Key.StoreId select new { ID = g.Key.StoreId, Name = g.Key.FullName, FzCount = g.Count() };
当前sqlserver版本没有原生字符串聚合函数的问题
之前都是直接使用sql语句可以使用 for xml path 或者用C#给sqlserver写自定义聚合函数
而现在想用 EF 就遇到了问题，LINQ to Entities 不识别方法。。。linq无法翻译成sql
//var query = from s in db.Store // join f in db.Tbl_FenZu on s.StoreId equals f.StoreId into f0 // from f in f0.DefaultIfEmpty() // group f by new { s.StoreId, s.FullName } into g // where g.Count() > 1 // orderby g.Key.StoreId // //select new { ID = g.Key.StoreId, Name = g.Key.FullName, FzCount = g.Count(), aa = g.Select(d => d.Name).Aggregate((a, b) => $"{a},{b}") }; //LINQ to Entities 不识别方法 System.String Aggregate // select new { ID = g.Key.StoreId, Name = g.Key.FullName, FzCount = g.Count(), aa = string.Join(",", g.Select(d => d.Name)) }; // LINQ to Entities 不识别方法 System.String Join
只能换个思路
var query = from s in db.Store join f in db.Tbl_FenZu on s.StoreId equals f.StoreId into f0 from f in f0.DefaultIfEmpty() group f by new { s.StoreId, s.FullName } into g where g.Count() > 1 orderby g.Key.StoreId select new { ID = g.Key.StoreId, Name = g.Key.FullName, FzCount = g.Count(), Fz = g }; //生成的sql很大很傻，实际上也没有分组。没必要这样写 var lst = query.ToList(); //这种思路：先查询出sql数据，再用LINQ to Object来处理“字符串聚合” var lst00 = lst.Select(d => new { d.ID, d.Name, d.FzCount, Fz = d.Fz.Select(o => o.Name).Aggregate((a, b) => $"{a},{b}") }).ToList(); var dt = ToDataTable(lst); var dt00 = ToDataTable(lst00);
目的达到了：
但是 这个 linq 语句可不怎么好（生成的sql很大很傻，实际上也没有分组。没必要这样写），改进下
var query = from s in db.Store join f in db.Tbl_FenZu on s.StoreId equals f.StoreId select new { ID = s.StoreId, Name = s.FullName, Fz = f.Name }; //先让sql生成 var lst = query.ToList(); //再加工数据 var lst_g = lst.GroupBy(d => d.ID).Select(g=>new {ID= g.Key,Name=g.First().Name, FzCount = g.Count(), Fz =g.Select(d=>d.Fz).Aggregate((a, b) => $"{a},{b}") }).ToList(); var dt = ToDataTable(lst_g);
以前公司的表动不动就是上千万甚至上亿行，未分组的原始数据的量非常大的情况下也可以用EF执行直接sql
以前公司使用ado.net配合sqlhelper多个查询条件经常会字符串动态拼sql，linq如何实现呢，园子里逛了逛主流的有这几种：
1.使用 Expression表达式，动态构造，有点高端，不是很熟悉这些高级东西，主要是各种委托参数
2.直接拼linq语句，还是这个好理解哈
var search = from User u in myDataCcontext.User select u; if(name!=null && name!=string.Empty) search=search.Where(u=>u.姓名==name); if(addr!=null && addr!=string.Empty) search=search.Where(u=>u.住址.StartsWith(Addr));
一个常见的查询写法
1 public JsonResult GetList(int offset, int limit, string order = null, string sort = null, string search = null, int? filteClass = null, string status = null, string graduationType = null,bool Completed=false) 2 { 3 var orderDirection = string.IsNullOrEmpty(order) || order.ToLower().Equals("asc") ? Extend.OrderDirection.ASC : Extend.OrderDirection.DESC; 4 5 var logIdExpression = from log in Db_OA.Censu_Logs 6 group log by log.CensuID into logCollection 7 select logCollection.Max(p => p.ID); 8 var logExpression = from log in Db_OA.Censu_Logs 9 join logId in logIdExpression on log.ID equals logId 10 select log; 11 var queryExpression = from censu in Db_OA.Censu_Census 12 join log in logExpression on censu.ID equals log.CensuID 13 join udwFilter in Db_OA.Human_UDW on censu.NetID equals udwFilter.NETID into udwCollection 14 from udw in udwCollection.DefaultIfEmpty() 15 join enclosureFilter in Db_OA.Enclosure_Enclosures on censu.Permit equals enclosureFilter.ID into enclosureCollection 16 from enclosure in enclosureCollection.DefaultIfEmpty() 17 select new 18 { 19 censu.ID, 20 censu.NetID, 21 udw.FirstName, 22 udw.LastName, 23 censu.Name, 24 censu.Class, 25 Permit = enclosure.RelativePath, 26 censu.Remark, 27 censu.UpdateTime, 28 censu.RelocationAddress, 29 censu.RelocationRemark, 30 censu.RelocationType, 31 log.Status, 32 LogTime = log.UpdateTime, 33 log.UpdateBy, 34 censu.AccountType, 35 censu.NativePlace, 36 censu.IDNumber, 37 censu.Contact 38 }; 39 queryExpression = queryExpression 40 .WhereIF(!string.IsNullOrEmpty(search), p => p.NetID.Contains(search) || p.FirstName.Contains(search) || p.LastName.Contains(search) || p.Status.Contains(search) || p.RelocationAddress.Contains(search)) 41 .WhereIF(!string.IsNullOrEmpty(status), p => p.Status.Equals(status)) 42 .WhereIF(!string.IsNullOrEmpty(graduationType), p => p.RelocationType.Equals(graduationType)) 43 .WhereIF(filteClass != null, p => p.Class.Equals(filteClass.Value)) 44 .WhereIF(Completed, p => p.Status == "Received") 45 .WhereIF(!Completed, p => p.Status != "Received") 46 .OrderByIF(string.IsNullOrEmpty(sort), p => p.LogTime, Extend.OrderDirection.DESC) 47 .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("NetID"), p => p.NetID, orderDirection) 48 .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("FirstName"), p => p.FirstName, orderDirection) 49 .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("Class"), p => p.Class, orderDirection) 50 .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("UpdateTime"), p => p.UpdateTime, orderDirection) 51 .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("Status"), p => p.Status, orderDirection) 52 .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("RelocationAddress"), p => p.RelocationAddress, orderDirection) 53 .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("RelocationType"), p => p.RelocationType, orderDirection) 54 .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("UpdateBy"), p => p.UpdateBy, orderDirection) 55 .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("LogTime"), p => p.LogTime, orderDirection); 56 57 var total = queryExpression.AsNoTracking().Select(p => p.ID).Count(); 58 59 queryExpression = queryExpression.OrderByIF(string.IsNullOrEmpty(sort), p => p.UpdateTime, Extend.OrderDirection.DESC); 60 61 var rows = queryExpression.AsNoTracking().Skip(offset).Take(limit).ToList(); 62 63 return Print_Message(new { rows, total }); 64 }
事务
1 var transaction = Db_OA.Database.BeginTransaction(); 2 try 3 { 4 ... 5 6 db.SaveChanges(); 7 8 transaction.Commit(); 9 10 return resSuccess(); 11 } 12 catch (Exception ex) 13 { 14 transaction.Rollback(); 15 ... 16 return resError(ex.InnerException?.Message ?? ex.Message); 17 }
