siverlight linq语句和lambda表达式

 

LINQ分组查询统计

• 这里介绍Linq使用Group By和Count得到每个CategoryID中产品的数量，Linq使用Group By和Count得到每个CategoryID中断货产品的数量等方面。

• 学经常会遇到Linq使用Group By问题，这里将介绍Linq使用Group By问题的解决方法。

  1.计数

  1. var q =
  2. from p in db.Products
  3. group p by p.CategoryID into g
  4. select new {
  5. g.Key,
  6. NumProducts = g.Count()
  7. };

  语句描述：Linq使用Group By和Count得到每个CategoryID中产品的数量。

  说明：先按CategoryID归类，取出CategoryID值和各个分类产品的数量。

  2.带条件计数

  1. var q =
  2. from p in db.Products
  3. group p by p.CategoryID into g
  4. select new {
  5. g.Key,
  6. NumProducts = g.Count(p => p.Discontinued)
  7. };

  语句描述：Linq使用Group By和Count得到每个CategoryID中断货产品的数量。

  说明：先按CategoryID归类，取出CategoryID值和各个分类产品的断货数量。 Count函数里，使用了Lambda表达式，Lambda表达式中的p，代表这个组里的一个元素或对象，即某一个产品。

  3.Where限制

  1. var q =
  2. from p in db.Products
  3. group p by p.CategoryID into g
  4. where g.Count() >= 10
  5. select new {
  6. g.Key,
  7. ProductCount = g.Count()
  8. };

  语句描述：根据产品的―ID分组，查询产品数量大于10的ID和产品数量。这个示例在Group By子句后使用Where子句查找所有至少有10种产品的类别。

  说明：在翻译成SQL语句时，在最外层嵌套了Where条件。

  4.多列(Multiple Columns)

  1. var categories =
  2. from p in db.Products
  3. group p by new
  4. {
  5. p.CategoryID,
  6. p.SupplierID
  7. }
  8. into g
  9. select new
  10. {
  11. g.Key,
  12. g
  13. };

  语句描述：Linq使用Group By按CategoryID和SupplierID将产品分组。

  说明：既按产品的分类，又按供应商分类。在by后面，new出来一个匿名类。这里，Key其实质是一个类的对象，Key包含两个Property：CategoryID、SupplierID。用g.Key.CategoryID可以遍历CategoryID的值。

  5.表达式(Expression)

  1. var categories =
  2. from p in db.Products
  3. group p by new { Criterion = p.UnitPrice > 10 } into g
  4. select g;

  语句描述：Linq使用Group By返回两个产品序列。第一个序列包含单价大于10的产品。第二个序列包含单价小于或等于10的产品。

  说明：按产品单价是否大于10分类。其结果分为两类，大于的是一类，小于及等于为另一类。

   

  6.举例：

  var lst = from p in DC.FWorkBills group p by p.WorkState into g select new {工程状态=g.Key , 数量=g.Count()}; dataGridGarb2.ItemsSource = lst.ToList(); dataGridGarb2.DataContext = lst.ToList();

  7.对比

  sql语句-linq语言-lambda表达式对照

  1、 查询Student表中的所有记录的Sname、Ssex和Class列。
  select sname,ssex,class from student
  Linq:
      from s in Students
      select new {
          s.SNAME,
          s.SSEX,
          s.CLASS
      }
  Lambda:
      Students.Select( s => new {
          SNAME = s.SNAME,SSEX = s.SSEX,CLASS = s.CLASS
      })

  
  2、 查询教师所有的单位即不重复的Depart列。
  select distinct depart from teacher
  Linq:
      from t in Teachers.Distinct()
      select t.DEPART
  Lambda:
      Teachers.Distinct().Select( t => t.DEPART)

   

  3、 查询Student表的所有记录。
  select * from student
  Linq:
      from s in Students
      select s
  Lambda:
      Students.Select( s => s)

  
  4、 查询Score表中成绩在60到80之间的所有记录。
  select * from score where degree between 60 and 80
  Linq:
      from s in Scores
      where s.DEGREE >= 60 && s.DEGREE < 80
      select s
  Lambda:
      Scores.Where(
          s => (
                  s.DEGREE >= 60 && s.DEGREE < 80
               )
      )

   

  5、 查询Score表中成绩为85，86或88的记录。
  select * from score where degree in (85,86,88)
  Linq:
  In
      from s in Scores
      where (
              new decimal[]{85,86,88}
            ).Contains(s.DEGREE)
      select s
  Lambda:
      Scores.Where( s => new Decimal[] {85,86,88}.Contains(s.DEGREE))
  Not in
      from s in Scores
      where !(
              new decimal[]{85,86,88}
            ).Contains(s.DEGREE)
      select s
  Lambda:
      Scores.Where( s => !(new Decimal[]{85,86,88}.Contains(s.DEGREE)))

      Any()应用：双表进行Any时，必须是主键为(String)
      CustomerDemographics CustomerTypeID（String）
      CustomerCustomerDemos (CustomerID CustomerTypeID) (String)
      一个主键与二个主建进行Any(或者是一对一关键进行Any)
      不可，以二个主键于与一个主键进行Any
     
      from e in CustomerDemographics
      where !e.CustomerCustomerDemos.Any()
      select e
     
      from c in Categories
      where !c.Products.Any()
      select c

   

  6、 查询Student表中"95031"班或性别为"女"的同学记录。
  select * from student where class ='95031' or ssex= N'女'
  Linq:
      from s in Students
      where s.CLASS == "95031"
         || s.CLASS == "女"
      select s
  Lambda:
      Students.Where(s => ( s.CLASS == "95031" || s.CLASS == "女"))

  
  7、 以Class降序查询Student表的所有记录。
  select * from student order by Class DESC
  Linq:
      from s in Students
      orderby s.CLASS descending
      select s
  Lambda:
      Students.OrderByDescending(s => s.CLASS)

   

  8、 以Cno升序、Degree降序查询Score表的所有记录。
  select * from score order by Cno ASC,Degree DESC
  Linq:(这里Cno ASC在linq中要写在最外面)
      from s in Scores
      orderby s.DEGREE descending
      orderby s.CNO ascending
      select s
  Lambda:
      Scores.OrderByDescending( s => s.DEGREE)
            .OrderBy( s => s.CNO)

  
  9、 查询"95031"班的学生人数。
  select count(*) from student where class = '95031'
  Linq:
      (    from s in Students
          where s.CLASS == "95031"
          select s
      ).Count()
  Lambda:
      Students.Where( s => s.CLASS == "95031" )
                  .Select( s => s)
                      .Count()

   

  10、查询Score表中的最高分的学生学号和课程号。
  select distinct s.Sno,c.Cno from student as s,course as c ,score as sc
  where s.sno=(select sno from score where degree = (select max(degree) from score))
  and c.cno = (select cno from score where degree = (select max(degree) from score))
  Linq:
      (
          from s in Students
          from c in Courses
          from sc in Scores
          let maxDegree = (from sss in Scores
                          select sss.DEGREE
                          ).Max()
          let sno = (from ss in Scores
                  where ss.DEGREE == maxDegree
                  select ss.SNO).Single().ToString()
          let cno = (from ssss in Scores
                  where ssss.DEGREE == maxDegree
                  select ssss.CNO).Single().ToString()
          where s.SNO == sno && c.CNO == cno
          select new {
              s.SNO,
              c.CNO
          }
      ).Distinct()
  操作时问题？执行时报错： where s.SNO == sno（这行报出来的） 运算符"=="无法应用于"string"和"System.Linq.IQueryable<string>"类型的操作数
  解决：
  原：let sno = (from ss in Scores
                  where ss.DEGREE == maxDegree
                  select ss.SNO).ToString()
  Queryable().Single()返回序列的唯一元素；如果该序列并非恰好包含一个元素，则会引发异常。
  解：let sno = (from ss in Scores
                  where ss.DEGREE == maxDegree
                  select ss.SNO).Single().ToString()
   

  11、查询'3-105'号课程的平均分。
  select avg(degree) from score where cno = '3-105'
  Linq:
      (
          from s in Scores
          where s.CNO == "3-105"
          select s.DEGREE
      ).Average()
  Lambda:
      Scores.Where( s => s.CNO == "3-105")
              .Select( s => s.DEGREE)
                  .Average()

  
  12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
  select avg(degree) from score where cno like '3%' group by Cno having count(*)>=5
  Linq:
          from s in Scores
          where s.CNO.StartsWith("3")
          group s by s.CNO
          into cc
          where cc.Count() >= 5
          select cc.Average( c => c.DEGREE)
  Lambda:
      Scores.Where( s => s.CNO.StartsWith("3") )
              .GroupBy( s => s.CNO )
                .Where( cc => ( cc.Count() >= 5) )
                  .Select( cc => cc.Average( c => c.DEGREE) )
  Linq: SqlMethod
  like也可以这样写:
      s.CNO.StartsWith("3") or SqlMethods.Like(s.CNO,"%3")

   

  13、查询最低分大于70，最高分小于90的Sno列。
  select sno from score group by sno having min(degree) > 70 and max(degree) < 90
  Linq:
      from s in Scores
      group s by s.SNO
      into ss
      where ss.Min(cc => cc.DEGREE) > 70 && ss.Max( cc => cc.DEGREE) < 90
      select new
      {
          sno = ss.Key
      }
  Lambda:
      Scores.GroupBy (s => s.SNO)
                 .Where (ss => ((ss.Min (cc => cc.DEGREE) > 70) && (ss.Max (cc => cc.DEGREE) < 90)))
                     .Select ( ss => new {
                                          sno = ss.Key
                                       })

   

  14、查询所有学生的Sname、Cno和Degree列。
  select s.sname,sc.cno,sc.degree from student as s,score as sc where s.sno = sc.sno
  Linq:
      from s in Students
      join sc in Scores
      on s.SNO equals sc.SNO
      select new
      {
          s.SNAME,
          sc.CNO,
          sc.DEGREE
      }
  Lambda:
      Students.Join(Scores, s => s.SNO,
                            sc => sc.SNO,
                            (s,sc) => new{
                                              SNAME = s.SNAME,
                                              CNO = sc.CNO,
                                              DEGREE = sc.DEGREE
                                            })

   

  15、查询所有学生的Sno、Cname和Degree列。
  select sc.sno,c.cname,sc.degree from course as c,score as sc where c.cno = sc.cno
  Linq:
      from c in Courses
      join sc in Scores
      on c.CNO equals sc.CNO
      select new
      {
          sc.SNO,c.CNAME,sc.DEGREE
      }
  Lambda:
      Courses.Join ( Scores, c => c.CNO,
                               sc => sc.CNO,
                               (c, sc) => new 
                                          {
                                              SNO = sc.SNO,
                                              CNAME = c.CNAME,
                                              DEGREE = sc.DEGREE
                                          })

   

  16、查询所有学生的Sname、Cname和Degree列。
  select s.sname,c.cname,sc.degree from student as s,course as c,score as sc where s.sno = sc.sno and c.cno = sc.cno
  Linq:
      from s in Students
      from c in Courses
      from sc in Scores
      where s.SNO == sc.SNO && c.CNO == sc.CNO
      select new { s.SNAME,c.CNAME,sc.DEGREE }