Linq基本语法20180126

1、Linq基本语法

 //======
        ZKWebForm1219_20180114Entities db = new ZKWebForm1219_20180114Entities();
        protected void Button1_Click(object sender, EventArgs e)
        {
            var ss = from r in db.LbtProjectInfo
                     where r.ID > 500 && r.ID < 600
                     select r;

            var ss2 = db.LbtProjectInfo.Where(p => p.ID > 500 && p.ID < 600);

            foreach (var item in ss2)
            {
                // Response.Write(item.ID + "=>" + item.ContractNumber + "=>" + item.ProjectName);
            }


            var lab2 = (from r in db.LbtProjectInfo
                        select r).Sum(p => p.ID);

            var lab23 = db.LbtProjectInfo.Sum(p => p.ID);

            // Response.Write(lab2+"=>"+lab23);

            foreach (var item in ss)
            {
                // Response.Write(item.ID + "=>" + item.ContractNumber + "=>" + item.ProjectName);
            }


            var sorder = from rd in db.LbtProjectInfo
                         where rd.ID > 500 && rd.ID < 600
                         orderby rd.ID descending
                         select rd;

            var sorderlab = db.LbtProjectInfo.OrderByDescending(p => p.ID).Where(p => p.ID > 500 && p.ID < 600).ToList();

            foreach (var item in sorderlab)
            {
                // Response.Write(item.ID + "=>" + item.ContractNumber + "=>" + item.ProjectName);
            }


            //top
            var sp = (from r in db.LbtProjectInfo
                      select r).FirstOrDefault();
            var sp1 = db.LbtProjectInfo.FirstOrDefault();

            //Response.Write(sp.ID + "=>" + sp.ContractNumber + "=>" + sp.ProjectName);
            //Response.Write("<br />");
            //Response.Write(sp1.ID + "=>" + sp1.ContractNumber + "=>" + sp1.ProjectName);


            //跳过前面多少条数据取余下的数据
            var sk = (from r in db.LbtProjectInfo
                      orderby r.ID
                      select r).Skip(10);
            var sk2 = db.LbtProjectInfo.OrderBy(p => p.ID).Skip(10).ToList();
            foreach (var item in sk2)
            {
                //Response.Write(item.ID + "=>" + item.ContractNumber + "=>" + item.ProjectName);
            }

            //分页查询
            var skt = (from r in db.LbtProjectInfo
                       where r.ID > 500 && r.ID < 600
                       orderby r.ID descending
                       select r).Skip(10).Take(10);//取511到第520条数据
            var skt2 = db.LbtProjectInfo.OrderByDescending(r => r.ID).Where(r => r.ID > 500 && r.ID < 600).Skip(10).Take(10).ToList();
            foreach (var item in skt2)
            {
                //Response.Write(item.ID + "=>" + item.ContractNumber + "=>" + item.ProjectName);
            }

            //类似like '%%'
            var slike = from r in db.LbtProjectInfo
                        where r.ProjectName.Contains("武汉")
                        select r;
            var slike2 = db.LbtProjectInfo.Where(p => p.ProjectName.Contains("武汉")).ToList();
            foreach (var item in slike2)
            {
                // Response.Write(item.ID + "=>" + item.ContractNumber + "=>" + item.ProjectName);
            }


            //分组group  by
            var sgroup = from r in db.LbtProjectInfo
                         orderby r.ID descending
                         group r by r.TestType into n
                         select new
                         {
                             n.Key,
                             ID = n.Sum(r => r.ID),
                             maxid = n.Max(r => r.ID),
                             minid = n.Min(r => r.ID)
                         };
            foreach (var item in sgroup)
            {
                //Response.Write(item.Key + "=>" + item.ID + "=>" + item.maxid+"=>"+item.minid);
                //Response.Write("<br />");
            }


            var sgroup2 = from r in db.LbtProjectInfo
                          orderby r.ID descending
                          group r by r.TestType into n
                          select n;

            var sgroup3 = db.LbtProjectInfo.GroupBy(p => p.TestType);
            foreach (var item in sgroup3)
            {
                // Response.Write(item.Key + "=>" + item.Min(p => p.ID));                
            }



            //连表查询
            var sjoin = from r in db.LbtProjectInfo
                        join w in db.ContractRview on r.ContractNumber equals w.xmbh
                        orderby r.ID descending
                        select r;
            var sjoin2 = db.LbtProjectInfo.Join(db.ContractRview, p => p.ContractNumber, r => r.xmbh, (p, r) => p).OrderByDescending(p => p.ID).ToList();
            //Response.Write(sjoin.Count());  
            foreach (var item in sjoin)
            {
                //Response.Write(item.ID + "=>" + item.ProjectName+"=>"+item.ContractNumber);                          
            }

            //in   // not in
            var sin = from r in db.LbtProjectInfo
                      where (new int?[] { 500, 501 }).Contains(r.ID)
                      select r;
           
            var sin2 = from r in db.LbtProjectInfo
                       where !(new int?[] { 500, 501 }).Contains(r.ID)
                       select r;

            foreach (var item in sin2)
            {
                Response.Write(item.ID + "=>" + item.ProjectName + "=>" + item.ContractNumber);
                Response.Write("<br />");
            }
        }