学习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());
        }

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));

一个常见的查询写法

        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)
        {
            var orderDirection = string.IsNullOrEmpty(order) || order.ToLower().Equals("asc") ? Extend.OrderDirection.ASC : Extend.OrderDirection.DESC;

            var logIdExpression = from log in Db_OA.Censu_Logs
                                  group log by log.CensuID into logCollection
                                  select logCollection.Max(p => p.ID);
            var logExpression = from log in Db_OA.Censu_Logs
                                join logId in logIdExpression on log.ID equals logId
                                select log;
            var queryExpression = from censu in Db_OA.Censu_Census
                                  join log in logExpression on censu.ID equals log.CensuID
                                  join udwFilter in Db_OA.Human_UDW on censu.NetID equals udwFilter.NETID into udwCollection
                                  from udw in udwCollection.DefaultIfEmpty()
                                  join enclosureFilter in Db_OA.Enclosure_Enclosures on censu.Permit equals enclosureFilter.ID into enclosureCollection
                                  from enclosure in enclosureCollection.DefaultIfEmpty()
                                  select new
                                  {
                                      censu.ID,
                                      censu.NetID,
                                      udw.FirstName,
                                      udw.LastName,
                                      censu.Name,
                                      censu.Class,
                                      Permit = enclosure.RelativePath,
                                      censu.Remark,
                                      censu.UpdateTime,
                                      censu.RelocationAddress,
                                      censu.RelocationRemark,
                                      censu.RelocationType,
                                      log.Status,
                                      LogTime = log.UpdateTime,
                                      log.UpdateBy,
                                      censu.AccountType,
                                      censu.NativePlace,
                                      censu.IDNumber,
                                      censu.Contact
                                  };
            queryExpression = queryExpression
                .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))
                .WhereIF(!string.IsNullOrEmpty(status), p => p.Status.Equals(status))
                .WhereIF(!string.IsNullOrEmpty(graduationType), p => p.RelocationType.Equals(graduationType))
                .WhereIF(filteClass != null, p => p.Class.Equals(filteClass.Value))
                .WhereIF(Completed, p => p.Status == "Received")
                .WhereIF(!Completed, p => p.Status != "Received")
                .OrderByIF(string.IsNullOrEmpty(sort), p => p.LogTime, Extend.OrderDirection.DESC)
                .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("NetID"), p => p.NetID, orderDirection)
                .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("FirstName"), p => p.FirstName, orderDirection)
                .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("Class"), p => p.Class, orderDirection)
                .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("UpdateTime"), p => p.UpdateTime, orderDirection)
                .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("Status"), p => p.Status, orderDirection)
                .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("RelocationAddress"), p => p.RelocationAddress, orderDirection)
                .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("RelocationType"), p => p.RelocationType, orderDirection)
                .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("UpdateBy"), p => p.UpdateBy, orderDirection)
                .OrderByIF(!string.IsNullOrEmpty(sort) && sort.Equals("LogTime"), p => p.LogTime, orderDirection);

            var total = queryExpression.AsNoTracking().Select(p => p.ID).Count();

            queryExpression = queryExpression.OrderByIF(string.IsNullOrEmpty(sort), p => p.UpdateTime, Extend.OrderDirection.DESC);

            var rows = queryExpression.AsNoTracking().Skip(offset).Take(limit).ToList();

            return Print_Message(new { rows, total });
        }

事务

 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             }