在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有一大坨，如下：
+ 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給了我們查詢的方便、靈活，也給了我們性能的陷阱。
