Linq to SQL(联接 聚合 分组)

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        NorthwindDBDataContext dc = new NorthwindDBDataContext();
        int count = dc.Products.Count();


     

        //1、获取根据分类ID得到最贵的、最便宜的、产品价格


        var result = from p in dc.Products
                     group p by p.CategoryID into g
                     select new
                     {
                         Max=g.Max(x=>x.UnitPrice),
                         Min=g.Min(x=>x.UnitPrice),
                         Group=g.Key
                     };

                   
                   
 

        //2、获得每个分类中产品的总个数，并显示分类名、总数、总价

        var result1 = from p in dc.Products
                      join c in dc.Categories
                      on p.CategoryID equals c.CategoryID
                      group new { p=p,c=c } by c.CategoryName into g
                      select new
                      {

                          Name = g.Key,
                          Sum=g.Sum(x=>x.p.UnitPrice),
                          Count=g.Count()

                      };
 


        //3、获得每个分类中最贵产品的价格和产品名字

        var result2 = from p in dc.Products
                      join c in dc.Categories
                      on p.CategoryID equals c.CategoryID
                      group new { p = p, c = c } by c.CategoryName into g
                      select new
                      {

                        Max=g.Max(x=>x.p.UnitPrice),
                        CategoryName=g.Key,
                        ProductName = (from p in dc.Products
                                       where p.UnitPrice == g.Max(x => x.p.UnitPrice)
                                       select p.ProductName).First()

                      };


       GridView1.DataSource = result2;
       GridView1.DataBind();
    }
}