LINQ to SQL 系列五 grouping having

最新推荐文章于 2025-06-02 12:27:10 发布

转载最新推荐文章于 2025-06-02 12:27:10 发布 · 113 阅读

本文探讨了Linq to SQL中的分组查询技巧及注意事项，包括简单的分组、带有筛选条件的分组、多字段分组以及排序输出等场景，并对比了不同写法生成的SQL语句差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在linq to sql中grouping很简单、很灵活，但是如果不注意则会中了Microsoft的糖衣炮弹。

本文使用的数据模型如下图：

1．用linq to sql写一个最简单的group语句：
查询Students表并按ClassID分组，输出每个班级的学生数。下面的代码是糖衣版。

static void Main(string[] args)
{
    using (var writer = new StreamWriter(WatchSqlPath, false, Encoding.UTF8))
    {
        using (DbAppDataContext db = new DbAppDataContext())
        {
            //打印sql
            db.Log = writer;

            //最简单的group by ,按ClassID对Students进行分组
            var query = from s in db.Students
                        group s by s.ClassID;

            foreach (var item in query)
            {
                //输出班级编号和班级人数
                Console.WriteLine("class id = {0} student count = {1}",item.Key,item.Count());
            }
        }
    }
    Console.ReadLine();
}

Linq to sql的写法很灵活，我们随时可以调用Count等一大堆方法，上例中我在输出时调用Count方法，导致了linq to sql真正执行的sql有一大坨，如下：

SELECT [t0].[ClassID] AS [Key]
FROM [dbo].[Student] AS [t0]
GROUP BY [t0].[ClassID]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

SELECT [t0].[StudentID], [t0].[Name], [t0].[Hometown], [t0].[Gender], [t0].[Birthday], [t0].[ClassID], [t0].[WeightInKg], [t0].[HeightInCm], [t0].[Desc] AS [Desc]
FROM [dbo].[Student] AS [t0]
WHERE @x1 = [t0].[ClassID]
-- @x1: Input Int (Size = -1; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

SELECT [t0].[StudentID], [t0].[Name], [t0].[Hometown], [t0].[Gender], [t0].[Birthday], [t0].[ClassID], [t0].[WeightInKg], [t0].[HeightInCm], [t0].[Desc] AS [Desc]
FROM [dbo].[Student] AS [t0]
WHERE @x1 = [t0].[ClassID]
-- @x1: Input Int (Size = -1; Prec = 0; Scale = 0) [2]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

SELECT [t0].[StudentID], [t0].[Name], [t0].[Hometown], [t0].[Gender], [t0].[Birthday], [t0].[ClassID], [t0].[WeightInKg], [t0].[HeightInCm], [t0].[Desc] AS [Desc]
FROM [dbo].[Student] AS [t0]
WHERE @x1 = [t0].[ClassID]
-- @x1: Input Int (Size = -1; Prec = 0; Scale = 0) [3]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

这个不能怪linq to sql，我们这样写它就必须得那样执行；只能我们自己使用时处处小心。上例中正确的写法应该是：

static void Main(string[] args)
{
    using (var writer = new StreamWriter(WatchSqlPath, false, Encoding.UTF8))
    {
        using (DbAppDataContext db = new DbAppDataContext())
        {
            //打印sql
            db.Log = writer;

            //最简单的group by ,按ClassID对Students进行分组
            //var query = from s in db.Students
            //            group s by s.ClassID;

            var query = from s in db.Students
                        group s by s.ClassID into sg
                        select new {
                            ClassID = sg.Key,
                            Count = sg.Count()
                        };

            foreach (var item in query)
            {
                //输出班级编号和班级人数
                Console.WriteLine("class id = {0} student count = {1}", item.ClassID, item.Count);
            }
        }
    }
    Console.ReadLine();
}

这样执行时才是那个我们以前写t-sql时经常看到的那个group by语句。

2．对分组聚合进行排序输出，我忘记了用let
请看下面代码，输出每个班级体重最大的同学，并要求最大体重得大于39，并按照体重大下，对分组结果进行排序。

static void Main(string[] args)
{
    using (var writer = new StreamWriter(WatchSqlPath, false, Encoding.UTF8))
    {
        using (DbAppDataContext db = new DbAppDataContext())
        {
            //打印sql
            db.Log = writer;

            var query = from s in db.Students
                        group s by s.ClassID into gS
                        where gS.Max<Student>(s => s.WeightInKg) > 39
                        orderby gS.Max<Student>(s => s.WeightInKg) descending
                        select new
                        {
                            ClassID = gS.Key,
                            MaxWeight = gS.Max<Student>(s => s.WeightInKg)
                        };

            foreach (var item in query)
            {
                Console.WriteLine("class id = {0} student max weight = {1}", item.ClassID, item.MaxWeight);
            }
        }
    }
    Console.ReadLine();
}

上例中，在query变量声明时我用了三次gS.Max<Student>(s => s.WeightInKg)，我们知道在t-sql中如果使用聚合值，必须得用表达式，在linq to sql中用会不会有问题呢，看下执行上述代码生成的sql吧：

SELECT [t1].[ClassID], [t1].[value3] AS [MaxWeight]
FROM (
    SELECT MAX([t0].[WeightInKg]) AS [value], MAX([t0].[WeightInKg]) AS [value2], MAX([t0].[WeightInKg]) AS [value3], [t0].[ClassID]
    FROM [dbo].[Student] AS [t0]
    GROUP BY [t0].[ClassID]
    ) AS [t1]
WHERE [t1].[value] > @p0
ORDER BY [t1].[value2] DESC
-- @p0: Input Float (Size = -1; Prec = 0; Scale = 0) [39]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

Linq to sql忠实的执行了我写的代码，我的query变量声明中用了三次Max，在t-sql的嵌套表中也有三个MAX对应，这个不是我想要的，我又错了，呼叫let，看看使用let之后linq to sql会怎么执行吧：

static void Main(string[] args)
{
    using (var writer = new StreamWriter(WatchSqlPath, false, Encoding.UTF8))
    {
        using (DbAppDataContext db = new DbAppDataContext())
        {
            //打印sql
            db.Log = writer;

            // group by with having
            var query = from s in db.Students
                        group s by s.ClassID into gS
                        let mw = gS.Max<Student>(s => s.WeightInKg)
                        where mw > 39
                        select new
                        {
                            ClassID = gS.Key,
                            MaxWeight = mw
                        };
            foreach (var item in query)
            {
                Console.WriteLine("class id = {0} student max weight = {1}", item.ClassID, item.MaxWeight);
            }
        }
    }
    Console.ReadLine();
}

这次的sql语句比上一个好多了，但是还没有足够好：

SELECT [t1].[ClassID], [t1].[value] AS [MaxWeight]
FROM (
    SELECT MAX([t0].[WeightInKg]) AS [value], [t0].[ClassID]
    FROM [dbo].[Student] AS [t0]
    GROUP BY [t0].[ClassID]
    ) AS [t1]
WHERE [t1].[value] > @p0
-- @p0: Input Float (Size = -1; Prec = 0; Scale = 0) [39]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

还是多嵌套了一层没有必要的select。如何才能不嵌套这一层呢？如果你知道，请告诉我。

3．使用linq to sql Group by多个字段：
这次我们先写一个标准的t-sql

SELECT ClassID,Hometown,count(*) Cn 
FROM Student
GROUP BY ClassID,Hometown
ORDER BY ClassID,Hometown;

这个t-sql对Student表按照ClassID和Hometown两个字段进行分组，并输出每个班级中某个地方的学生数

static void Main(string[] args)
{
    using (var writer = new StreamWriter(WatchSqlPath, false, Encoding.UTF8))
    {
        using (DbAppDataContext db = new DbAppDataContext())
        {
            //打印sql
            db.Log = writer;

            // group by with having
            var query = from s in db.Students
                        group s by new { s.ClassID, s.Hometown } into gS
                        let cn = gS.Count<Student>()
                        select new
                        {
                            ClassID = gS.Key.ClassID,
                            Hometown = gS.Key.Hometown,
                            Count = cn
                        };
            foreach (var item in query)
            {
                Console.WriteLine("class id = {0} hometown {1} student count = {2}", item.ClassID, item.Hometown,item.Count);
            }
        }
    }
    Console.ReadLine();
}

这一次linq to sql给了我一个惊喜，它用的t-sql和我开始写的一样。

4．在3的基础上加一点点需求，要求分组后的结果按照count排序，query的声明代码如下：

// group by with having
var query = from s in db.Students
            group s by new { s.ClassID, s.Hometown } into gS
            let cn = gS.Count<Student>()
            orderby cn descending
            select new
            {
                ClassID = gS.Key.ClassID,
                Hometown = gS.Key.Hometown,
                Count = cn
            };

这次link to sql使用的sql语句多了一层没有必要的select嵌套

SELECT [t1].[ClassID], [t1].[Hometown], [t1].[value] AS [Count]
FROM (
    SELECT COUNT(*) AS [value], [t0].[ClassID], [t0].[Hometown]
    FROM [dbo].[Student] AS [t0]
    GROUP BY [t0].[ClassID], [t0].[Hometown]
    ) AS [t1]
ORDER BY [t1].[value] DESC
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1

看来使用linq to sql少多总要付出点代价的，linq to sql给了我们查询的方便、灵活，也给了我们性能的陷阱。

linq to sql相关随笔：

1．从CUD开始，如何使用LINQ to SQL插入、修改、删除数据

2．查询使用LINQ to SQL做简单查询

3．查询延迟加载与立即加载，使用LoadWith和AssociateWith

4．查询 inner join，left outer join

5． Linq to SQL中的聚合grouping having

6． LINQ to SQL查询优化，需要忧虑性能吗？