在linq to sql中grouping很简单、很灵活,但是如果不注意则会中了Microsoft的糖衣炮弹。
本文使用的数据模型如下图:
1. 用linq to sql写一个最简单的group语句:
查询Students表并按ClassID分组,输出每个班级的学生数。下面的代码是糖衣版。
class="csharp keyword">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有一大坨,如下:
+ View Code这个不能怪linq to sql,我们这样写它就必须得那样执行;只能我们自己使用时处处小心。上例中正确的写法应该是:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29static
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吧:
1 2 3 4 5 6 7 8 9 10SELECT
[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会怎么执行吧:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27static
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语句比上一个好多了,但是还没有足够好:
1 2 3 4 5 6 7 8 9SELECT
[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两个字段进行分组,并输出每个班级中某个地方的学生数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27static
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的声明代码如下:
1 2 3 4 5 6 7 8 9 10 11// 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嵌套
1 2 3 4 5 6 7 8SELECT
[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给了我们查询的方便、灵活,也给了我们性能的陷阱。