Linq 代码笔记

1、join例子

 var obj = from u in db.u_users

                          join u2 in db.u_userfriend
                              //on u.userID equals u2.friendID
                           on new { uid = u.userID, cruid = userID }
                          equals new { uid = u2.friendID, cruid = u2.userID }
                          into g
                          from u2 in g.DefaultIfEmpty()

                          join u3 in db.u_newfriend
                          on new { uid = u.userID, cruid = userID, st = state }
                          equals new { uid = u3.addUserID, cruid = u3.userID, st = u3.state }
                          into uf

                          from u3 in uf.DefaultIfEmpty()

                          where u.userID != userID && u.visible == 1
                          select new
                          {
                              u,
                              friend = (int?)u2.friendID,
                              newfriend = (int?)u3.addUserID,
                              state = (int?)u3.state,
                          };


                if (!string.IsNullOrEmpty(qb.text))
                {
                    obj = obj.Where(n => n.u.trueName.Contains(qb.text) || n.u.mobile.Contains(qb.text));
                }


                pageCount = obj.Count();
                obj = obj.OrderBy(n => n.u.trueName.Replace(qb.text, "")).ThenBy(n => n.u.mobile.Replace(qb.text, ""))
                     .Skip((qb.page - 1) * qb.rows)
                     .Take(qb.rows);

 

2、linq groupby

 

 public dynamic GetClientUserList(QueryUserinfo qb, out int totalNum)
        {
            totalNum = 0;
            using (var db = base.newContext())
            {
                try
                {
                    var predicate = PredicateBuilder.True<uDepart>();

                    //add vendor filter
                    if (qb.type == 1)
                    {
                        predicate = predicate.And(m => m.user.state == 1);
                    }
                    else if (qb.type == 0)
                    {
                        predicate = predicate.And(m => m.user.state == 0);
                    }
                    if (!string.IsNullOrEmpty(qb.searchKey) && !string.IsNullOrEmpty(qb.searchKeyword))
                    {
                        switch (qb.searchKey)
                        {
                            case "userName":
                                predicate = predicate.And(m => m.user.userName.Contains(qb.searchKeyword));
                                break;
                            case "trueName":
                                predicate = predicate.And(m => m.user.trueName.Contains(qb.searchKeyword));
                                break;
                            case "mobile":
                                predicate = predicate.And(m => m.user.mobile.Contains(qb.searchKeyword));
                                break;
                            case "email":
                                predicate = predicate.And(m => m.user.email.Contains(qb.searchKeyword));
                                break;
                            case "qq":
                                predicate = predicate.And(m => m.user.qq.Contains(qb.searchKeyword));
                                break;
                            case "job":
                                predicate = predicate.And(m => m.user.job.Contains(qb.searchKeyword));
                                break;
                        }

                    }
                    var obj1 = (from n in db.userinfo
                                join dr in
                                    (from r in db.userdepartmentrelate
                                     join d in db.userdepartment on r.departmentID equals d.itemID
                                     where d.visible == 1
                                     select new { userID = r.userID, itemID = d.itemID, name = d.name }) on n.userID equals dr.userID into temp
                                from tt in temp.DefaultIfEmpty()
                                where n.visible == 1
                                orderby n.userID descending
                                select new uDepart
                                {
                                    user = n,
                                    d_id = tt.itemID,
                                    d_name = tt == null ? "" : tt.name,

                                }).Where(predicate).ToList();

                    var obj = obj1
                                .GroupBy(c => c.user).Select(c =>
                               new
                               {
                                   strUser = security1.EIPEncrypt(c.Key.userID.ToString()),
                                   userName = c.Key.userName,
                                   trueName = c.Key.trueName,
                                   mobile = c.Key.mobile,
                                   email = c.Key.email,
                                   job = c.Key.job,
                                   qq = c.Key.qq,
                                   state = c.Key.state,
                                   imgURL = c.Key.imgURL,
                                   roleID = c.Key.roleID,
                                   departmentNames = string.Join(",", c.Select(y => y.d_name)),
                                   departmentIDs = string.Join(",", c.Select(y => y.d_id))
                               }).Where(tt => (qb.searchKey == "department" && !string.IsNullOrEmpty(qb.searchKeyword)) ? tt.departmentNames.Contains(qb.searchKeyword) : 1 == 1);


                    totalNum = obj.Count();

                    return obj.Skip((qb.page - 1) * qb.rows).Take(qb.rows).ToList();
                }
                catch (Exception ex)
                {

                    return new List<object>();
                }

            }

        }