C#中Linq常用用法

1、linq中交集、并集、差集的用法

简单的交集、并集、差集用法如下：

List<string> ListA = new List<string>();
List<string> ListB = new List<string>();
List<string> ListResult = new List<string>();

ListResult = ListA.Distinct().ToList();//去重
ListResult = ListA.Except(ListB).ToList();//差集
ListResult = ListA.Union(ListB).ToList();  //并集
ListResult = ListA.Intersect(ListB).ToList();//交集

若上面的例子不是List<string>类型，而是List<XXXModel>，则需要对XXXModel进行处理。
步骤如下：
(1)先定义Model。

public class ItemModel
{
   public string ItemCode { get; set; }
   public string ItemName { get; set; }
}

(2)定义如何Model间如何比较。若不定义，比较的是两个引用。

public class ItemModelComparer : IEqualityComparer<ItemModel>
{
        //比较
        public bool Equals(ItemModel x, ItemModel y)
        {
            bool checkFlag = true;
            if (Object.ReferenceEquals(x, y))
            {
                checkFlag = true;
            }
            else if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
            {
                checkFlag = false;
            }
            else
            {
                if (x.ItemCode == y.ItemCode) //若Model有多个条件则需要添加。例如 if(x.ItemCode==y.ItemCode && x.Other==y.Other)
                {
                    checkFlag = true;
                }
                else
                {
                    checkFlag = false;
                }
            }
            return checkFlag;
        }
    
        //实现获取哈希值
        public int GetHashCode(ItemModel model)
        {
            if (Object.ReferenceEquals(model, null)) return 0;
            int hashNurse = model.ItemCode.GetHashCode(); 

//            若有多个条件，则哈希值也需要进行计算
//            int hashOther=model.Other.GetHashCode();
//           int resultHash=hashNurse^hashOther;
//            return resultHash;

            return hashNurse;
        }    
    }

(3)具体使用如下

List<ItemModel> ListA = new List<ItemModel>();
List<ItemModel> ListB = new List<ItemModel>();
List<ItemModel> ListResult = new List<ItemModel>();

ListResult = ListA.Distinct(new ItemModelComparer()).ToList();//去重
ListResult = ListA.Except(ListB, new ItemModelComparer()).ToList();//差集
ListResult = ListA.Union(ListB, new ItemModelComparer()).ToList();  //并集
ListResult = ListA.Intersect(ListB, new ItemModelComparer()).ToList();//交集

2、 join的用法

若出现两个List，想用Linq进行查询。则可以使用如下的方式

int tempSatisfiedConditionCount = (from r1 in whiteList
                                   join r2 in args.DiagList on r1.DiagCode equals r2.DiagCode
                                   select r1).Count();

多条件的join

//多条件的联合查核
List<DiagControlModel> whiteDiagList = (from r1 in diagControlList
        where r1.ControlRelation == 1
        join r2 in args.DiagList on new { code = r1.DiagCode, name = r1.DiagName }
        equals new{code=r2.DiagCode,name=r2.DiagName}
        select r1).ToList<DiagControlModel>();

另外，join还可以用于左连接/右连接

var LeftJoin = from emp in ListOfEmployees
                join dept in ListOfDepartment
                on emp.DeptID equals dept.ID into JoinedEmpDept
                from dept in JoinedEmpDept.DefaultIfEmpty()
                 select new
                      {
                            EmployeeName = emp.Name,
                            DepartmentName = dept != null ? dept.Name : null
                      };

3、let用法

let是一个在linq中定义局部变量
1、可以有多个let子句
2、let后的变量无需声明类型
参见下例：

var query = from a in list  
                        let b = a.Name.Split('-')  
                        let c=a.Age  
                        where b[0] =="zhangs" & c>21  
                        select a; 

4、分组

常用的分组用法：

var linqtest = 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
               };

略微复杂点：

var dataList= (from r in drugLabelList
               group r by new
               {
                  r.OrderNo,
                  r.PatientID,
                  r.PatientName
               } into q
               let drugAmount = q.Sum(t => Convert.ToInt32(t.ChargeAmount))
               where drugAmount >= 0
               select new PrintDrugLabelModel
               {
                  OrderNo = q.Key.OrderNo,
                  PatientID = q.Key.PatientID,
                  PatientName = q.Key.PatientName,
                  ChargeAmount = ""+drugAmount
               }).ToList<PrintDrugLabelModel>();

5、简单的函数计算

 var linqtest = (from r in db.Am_recProScheme
                 select r).Sum(p => p.rpId);

6、排序order by desc/asc

var linqtest = (from r in db.Am_recProScheme
                where r.rpId > 10
                orderby r.rpId descending  //倒序
                // orderby r.rpId, r.rpname descending   //多条件的倒序（与SQL语句中的相同）
                //  orderby r.rpId ascending   //正序
                select r);

7、top(1)

//如果取最后一个可以按倒叙排列再取值
 var linqtest = (from r in db.Am_recProScheme                     
                 select r).FirstOrDefault();

8、跳过前面多少条数据取余下的数据

var linqtest = (from r in db.Am_recProScheme
                where r.rpId > 10
                orderby r.rpId descending
                select r).Skip(10).Take(10); //取第11条到第20条数据  

9、包含

//可以使用List、Array、string的Contains()方法进行判断
 var linqtest = (from r in db.Am_recProScheme
                 where r.SortsText.Contains("张")
                 select r);

10、连接查询

var linqtest = (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);

11、使用linq查询DataTable

 //遍历DataTable，将其中的数据对应到ClientStruct中：  
 List<ClientStruct> list = (from x in dtTable.AsEnumerable()  
                            orderby x.Field<string>("")  descending  
                            where x.Field<string>("ErrorType") == "漏孔" 
                            select new ClientStruct  
                            {  
                               ID = x.Field<string>(cs.ID),  
                               Name = x.Field<string>(cs.Name),  
                               Company = x.Field<string>(cs.Company),  
                               CreatedDate = x.Field<string>(cs.CreatedDate)  
                            }).ToList<ClientStruct>();  

12、linq中列传行操作（Aggregate的使用）—2022-3-4

解决分组又聚合的问题。
后台数据存储的样式是：

希望实现：

 即：实现分组，并且能将诊断进行聚合（列转行）
linq的写法如下：

var data_list = (from r in temp_result_list
                 group r by new
                      {
                          r.DrugCode,
                          r.VisitType
                      } into q
                  select q.Aggregate(temp_result_list[0],(workingSentence, next) => new DiagControlModel
                  {
                      DrugCode = q.Key.DrugCode,
                      VisitType = q.Key.VisitType,
                                     DiagInfoStr =  workingSentence.DiagInfoStr + ";" + next.DiagName
                  })).ToList<DiagControlModel>();