• C# LINQ和Lambda表达式详解


    (1) 简单的查询语句

    Linq语法:
      var data=from a in db.Areas select a ;
    Lamda语法:
      var data=db.Areas;
    sql语法:
      string sqlStr=" SELECT * FROM Areas  ";
    

    (2) 简单的WHERE语句

    Linq语法:
      var data=from a in db.orderInfo where a.orderId > 20 select a ;
    Lamda语法:
      var data=db.orderInfo.Where( t=>t.orderId > 20 ) ;
    sql语法:
      string sqlStr=" SELECT * FROM orderInfo WHERE orderId > 20 ";
    

    (3) 关于“COUNT、SUM、MIN、MAX”函数的语句

    Linq语法:
      var data=( from a in db.orderInfo select a ).Max( p=>p.orderId ) ;//查询该表中最大编号Id
      var data=( from a in db.orderInfo select a ).Min( p=>p.orderId ) ;//查询该表中最小编号Id
      var data=( from a in db.orderInfo select a ).Count() ;//查询该表数据总条数
      var data=( from a in db.orderInfo select a ).Sum( p=>p.orderMoney ) ;//查询该表中所有消费额的总数(求和)
    Lamda语法:
      var data=db.orderInfo.Max( t=>t.orderId );//查询该表中最大编号Id
      var data=db.orderInfo.Min( t=>t.orderId );//查询该表中最小编号Id
      var data=db.orderInfo.Count();//查询该表数据总条数
      var data=db.orderInfo.Sum( t=>t.orderMoney );//查询该表中所有消费额的总数(求和)
    sql语法:
      string sqlStr=" SELECT MAX(orderId) FROM orderInfo ";
      string sqlStr=" SELECT MIN(orderId) FROM orderInfo ";
      string sqlStr=" SELECT COUNT(*) FROM orderInfo ";
      string sqlStr=" SELECT SUM(orderMoney ) FROM orderInfo ";
    
    

    (4) 关于数据排序的语句

    Linq语法:
      var data=from a in db.orderInfo where a.orderId > 20 orderby a.orderId descending select a ;//倒序排序,升序可用ascending关键字
    Lamda语法:
      //情况一,根据单字段排序:
      var data=db.orderInfo.OrderByDescending( t=>t.orderId ).Where( t=>t.orderId > 20 ) .ToList();//倒序排序,升序可用OrderBy关键字
      //情况二,根据多字段主次排序:
      var priceMonthEntities = priceMonthApp.GetList().OrderBy(t => t.F_Year).ThenBy(t => t.F_Month).ToList();//先按年升序,再按月升序
    sql语法:
      string sqlStr=" SELECT * FROM orderInfo WHERE orderId > 20 ORDER BY orderId DESC  ";//倒序排序,升序可用ASC关键字
    
    

    (5) 关于分页查询的语句

    Linq语法:
      var data=( from a in db.orderInfo select a ) .Skip((pageIndex-1) * pageSize).Take(pageSize).ToList();
    Lamda语法:
      var data=db.orderInfo.Skip((pageIndex-1)* pageSize).Take(pageSize).ToList();;//pageIndex:当前页码,pageSize:分页数据显示条数
    sql语法:
      string sqlStr="SELECT TOP pageSize * FROM orderInfo WHERE orderId NOT IN(SELECT TOP( ( pageIndex - 1) * pageSize) orderId FROM orderInfo)";
    
    

    (6) 关于模糊查询(like)的语句

    Linq语法:
      var data= from a in db.orderInfo where a.orderId.Contains(1) select a;//使用Contains关键字进行模糊匹配
    Lamda语法:
      var data=db.orderInfo.Where(t=>t.F_UserId.Contains("1")).ToList();//使用Contains关键字进行模糊匹配
    sql语法:
      string sqlStr="SELECT * FROM orderInfo WHERE orderId LIKE '%12%'";//使用like关键字进行模糊匹配
    

    (7) 关于分组查询的语句

    Linq语法:
      var data= from a in db.orderInfo orderby a.orderId descending 
                group a by a.orderType into s select new{
                s.key,//分组字段
                s.sMoney=s.Sum(a=>a.orderMoney),//分组后算出总的消费额
                s.maMoney=s.Max(a=>a.orderMoney),//分组后算出最大的消费额
                s.miMoney=s.Min(a=>a.orderMoney)//分组后算出最小的消费额
                };
    Lamda语法:
      //使用GroupBy关键字进行分组查询(单个字段)
      var data=db.orderInfo.GroupBy(p => p.recType).Select(t=>t.Key).ToList();
      //使用GroupBy关键字进行分组查询(多个字段)
      var data=db.orderInfo.GroupBy(p =>new{ p.recType,p.orderId}).Select(t=>new{ recType=t.Key.recType,orderId=t.Key.orderId}).ToList();
    sql语法:
      string sqlStr="SELECT orderType , SUM(orderMoney), MAX(orderMoney), MIN(orderMoney) FROM orderInfo GROUP BY orderType";
    
    

    (8) 关于多表关联查询的语句

    Linq语法:
      //使用join关键字进行表连接
      var data= from a in db.orderInfo join e in  db.orderType on a.orderTypeId equals e.id select r ; 
      var query=from t in db.orderInfo join s in db.orderType on t.orderTypeId equals s.id select 
      new {
            orderId=t.id,
            orderTypeName=s.name,
            ...
      }
    Lamda语法:
      var data=db.orderInfo.Join(db.orderType,t=>t.orderTypeId,s=>s.id,(t,s)=>t).OrderByDescending(t=>t.orderId).
      Select(
                t=> new{
                 orderId=t.t.id,
                 orderTypeName=t.s.name,
                 ...
              }).ToList(); //使用Join关键字进行表连接
    EF Core中的写法:
      var data=db.orderInfo.Join(db.orderType,t=>t.orderTypeId,s=>s.id,(t,s)=>new{
      orderId=s.Id,
      .....
      }).toList();
    sql语法:(sql语句表关联有多种方法,在此只举一例)
      string sqlStr="SELECT * FROM orderInfo o ,orderType t WHERE o.orderTypeId=t.id ORDER BY t.createDate";
    
    

    (9) 关于in查询的语句

    Linq语法:
      var data= from a in db.orderInfo where (new int?[2213,43311,32422]).Contains(a.orderId) select a ; 
    Lamda语法:
      var data=db.orderInfo.Where(t=>(new int?[2213,43311,32422]).Contains(t.orderId)).ToList();
    sql语法:
      string sqlStr="SELECT * FROM orderInfo WHERE orderId IN (2213,43311,32422)";
    

    (10) 关于去重查询的语句

    Linq语法:
      var data= from a in db.orderInfo group p by new {a.orderTypeId} into _group select _group.FirstOrDefault(); //使用group关键字进行表数据去重
      var data= from a in db.orderInfo group p by new {a.orderTypeId,...} into _group select _group.FirstOrDefault(); //使用group关键字对多个字段进行表数据去重
    Lamda语法:
      //单个去重:
      var data=db.orderInfo.GroupBy(t=>t.orderTypeId).Select(r => r.First()).ToList(); //使用GroupBy关键字进行表数据去重
      var data=db.orderInfo.DistinctBy(t=>t.orderTypeId).ToList(); //使用DistinctBy关键字进行表数据去重
      //多个字段去重:
      var data=db.orderInfo.GroupBy(t=>new{t.orderTypeId,...}).Select(r => r.First()).ToList(); //使用GroupBy关键字对多个字段进行表数据去重
      var data=db.orderInfo.DistinctBy(t=>new{t.orderTypeId,...}).ToList(); //使用DistinctBy关键字对多个字段进行表数据去重
    sql语法:
      string sqlStr="SELECT DISTINCT orderTypeId  FROM orderInfo";//使用DISTINCT关键字进行表数据去重
      string sqlStr="SELECT orderTypeId FROM orderInfo GROUP BY orderTypeId";//使用GROUP BY关键字进行表数据去重
    
    

    (11) 内连接 INNER JOIN

    Linq语法:
    var ss = 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;
    Lamda语法:
    var ss1 = db.Am_recProScheme.Join(db.Am_Test_Result, p => p.rpId, r => r.rsId, (p, r) => p).OrderByDescending(p => p.rpId).ToList();
    sql语法:
    string sssql = "SELECT r.* FROM Am_recProScheme AS r INNER JOIN Am_Test_Result AS t ON r.[rpId] = t.[rsId] ORDER BY r.[rpId] DESC";
    
    

    (12) 左连接 LEFT JOIN

    //两个DataTable关联,查找只在第一个表中的数据
    static void Main(string[] args)
    {
        DataTable dt1 = new DataTable("Ta");
        DataTable dt2 = new DataTable("Tb");
        dt1.Columns.Add("ID", typeof(int));
        dt1.Columns.Add("Name", typeof(string));
        dt1.Rows.Add(1, "小明");
        dt1.Rows.Add(2, "小红");
        dt1.Rows.Add(3, "小黑");
        dt2.Columns.Add("ID", typeof(int));
        dt2.Columns.Add("Name", typeof(string));
        dt2.Rows.Add(1, "小黄");
        dt2.Rows.Add(2, "小红");
        dt2.Rows.Add(3, "小强");
        方法一:Linq语法
        var query = from q1 in dt1.AsEnumerable()
                    join q2 in dt2.AsEnumerable()
                    on q1.Field<string>("Name") equals q2.Field<string>("Name") into tmp
                    from q3 in tmp.DefaultIfEmpty()
                    where q3 == null
                    select new
                    {
                        ID = q1.Field<int>("ID"),
                        Name = q1.Field<string>("Name")
                    };
        方法二:Lamda语法
        var query = dt1.AsEnumerable().GroupJoin(
            dt2.AsEnumerable(),
            x => x.Field<string>("Name"),
            y => y.Field<string>("Name"),
            (x, y) => y.DefaultIfEmpty(). Where(w => w == null).
            Select(z => new { ID = x.Field<int>("ID"), Name = x.Field<string>("Name") })
            ).SelectMany(x => x);
        foreach (var item in query)
        {
            Console.WriteLine($"ID={item.ID}    Name={item.Name}");
        }
        Console.Read();
    }
    
    
    
    

    (13) 三表连接

    SELECT id, name, jname, cname   
            FROM userinfo u   
            LEFT JOIN job j on u.job = j.jid   
            LEFT JOIN city c on u.city = c.cid  
    
    var list = (  
        from u in dc.userinfos  
            join j in dc.jobs on u.job equals j.jid into j_join  
        from x in j_join.DefaultIfEmpty()  
            join c in dc.cities on u.city equals c.cid into c_join  
        from v in c_join.DefaultIfEmpty()  
        select new  
        {  
            id = u.id,  
            name = u.name,  
            jname = x.jname,  
            cname = v.cname,  
            /*u1=u,x1=x,v1=v*/  
            //不要用对象的方式 因为对象可能为null那么对象.属性就会抛异常  
        }  
        ).ToList();  
          
        for (var i = 0; i < list.Count(); i++)  
        {  
            Console.WriteLine(list[i].name + '	' + list[i].jname + '	' + list[i].cname); //字段为null不报异常  
            //Console.WriteLine(list[i].u1.name+'	'+list[i].x1.jname+'	'+list[i].v1.cname+"
    "); //对象x1 v1 有可能为null 抛异常  
        }  
        Console.ReadLine();


    转自:https://www.cnblogs.com/zhaoshujie/p/10434657.html
  • 相关阅读:
    glide引出恶心的git submodule
    恶心的hadoop集群
    恶心的sbt 超级慢--解决
    gradle 如何操作命令行
    TextView使用SpannableString设置复合文本(转)
    在gradle 中使用ant 执行 “命令行”(CMD)不出日志解决方案
    SQLServer常用运维SQL整理(转)
    github 拉取代码慢 的处理
    Docker部署Sentry
    下载网站的视频
  • 原文地址:https://www.cnblogs.com/yonggang/p/15007601.html
Copyright © 2020-2023  润新知