C#におけるLinqの常用用法


-1 joinの使い方
二つのリストが出たら、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
                      };

0、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; 

1、グループ化
よく使われるグループ分けの使い方:
      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();

2、簡単な関数計算
      var linqtest = (from r in db.Am_recProScheme
                      select r).Sum(p => p.rpId);

3、ソート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);

4、top(1)
            //                 
      var linqtest = (from r in db.Am_recProScheme                     
                      select r).FirstOrDefault();

5、前のデータをスキップして残りのデータを取る
      var linqtest = (from r in db.Am_recProScheme
                      where r.rpId > 10
                      orderby r.rpId descending
                      select r).Skip(10).Take(10); //  11   20     

6、含む
	   //    List、Array、string Contains()      
     var linqtest = (from r in db.Am_recProScheme
                     where r.SortsText.Contains(" ")
                     select r);

7、接続クエリー
      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);

8、linqを使ってDataTableを検索する
            //  DataTable,         ClientStruct :  
            List list = (from x in dtTable.AsEnumerable()  
                                       orderby x.Field("")  descending  
                                       where x.Field("ErrorType") == "  " 
                                       select new ClientStruct  
                                       {  
                                           ID = x.Field(cs.ID),  
                                           Name = x.Field(cs.Name),  
                                           Company = x.Field(cs.Company),  
                                           CreatedDate = x.Field(cs.CreatedDate)  
                                       }).ToList();  

参考:ブログ園の張龍豪ブログ