# 用 SqlCacheDependency 快取 LINQ to SQL 的查詢結果
在 ADO.NET 2.0 有個 Query notifications (SqlDependency) 機制，讓 SQL Server 2005 能夠主動通知你的應用程式(Application)來源資料是否變更，尤其是在做資料快取(Cache)的時候特別有效率。但自從改用了 LINQ to SQL 好像就很少人提到如何利用 SqlCacheDependency 類別進行 LINQ to SQL 查詢後的結果做快取。
最近在 [MSDN Code Gallery](http://code.msdn.microsoft.com/) 發現有個 [Caching LinqToSql results with SqlCacheDependency](https://web.archive.org/web/20080827175137/http://code.msdn.microsoft.com/linqtosqlcache) 專案，上面提供了一段程式碼，是以 Extension Method 的方式寫成的，可以針對 IQueryable 物件進行資料快取的動作。由於他是用一段原始碼來說明如何快取 LINQ to SQL 的結果，所以我也打算用一段程式碼說明一遍（當然是用中文說明），大部分的程式碼都跟原網站的一樣，只是我修的更完整一點，整段複製回去就可以馬上用了，你可以先在 App\_Code 目錄下先建立一個空的 Class 檔（LinqToSqlCache.cs），然後將以下這段程式碼複製、貼上就可以用了。
```
using System;
using System.Linq;
using System.Data.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Caching;
public static class LinqToSqlCache
{
///
/// 快取 LINQ Query 的結果（僅適用於 LINQ to SQL 環境）
/// 使用的的限制跟使用 SqlCacheDependency 的限制一樣
///
///
///
/// 你的 LINQ to SQL DataContext
/// Cache ID，需為每一個不同的 IQueryable 物件設定一個唯一的 ID
///
public static List LinqCache(this IQueryable q, DataContext dc, string CacheId)
{
List objCache = (List)System.Web.HttpRuntime.Cache.Get(CacheId);
if (objCache == null)
{
///////// 尚未快取，實做 new SqlCacheDependeny //////////
// 1. 透過 DataContext 取得連線字串
string connStr = dc.Connection.ConnectionString;
// 2. 透過 DataContext 與 IQueryable 物件取得 SqlCommand 物件
SqlCommand sqlCmd = dc.GetCommand(q) as SqlCommand;
// 3. 建立要給 SqlCacheDependency 使用的 SqlConnection 物件
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
// 4. 建立要給 SqlCacheDependency 使用的 SqlCommand 物件
using (SqlCommand cmd = new SqlCommand(sqlCmd.CommandText, conn))
{
// 5.0 將 sqlCmd 中的所有參數傳遞給 cmd 物件
foreach (System.Data.Common.DbParameter dbp in sqlCmd.Parameters)
{
cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value));
}
// 5.1 啟用資料庫的 Query notifications 功能
SqlCacheDependencyAdmin.EnableNotifications(connStr);
// 5.2 取得要進行異動通知的表格名稱(ElementType)
string NotificationTable = q.ElementType.Name;
// 5.3 將取得的 NotificationTable 啟用通知功能
if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable))
SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable);
// 6. 建立 SqlCacheDependency物件
SqlCacheDependency sqlDep = new SqlCacheDependency(cmd);
// 7. 刷新 LINQ to SQL 的值（取得資料庫中的最新資料）
dc.Refresh(RefreshMode.OverwriteCurrentValues, q);
// 8. 執行 SqlCacheDepency 查詢
cmd.ExecuteNonQuery();
// 9. 執行 LINQ to SQL 的查詢，並將結果轉成 List 型別，避免延遲查詢(Delayed Query)立即將資料取回
objCache = q.ToList();
//10. 將結果插入到 System.Web.HttpRuntime.Cache 物件中，並且指定 SqlCacheDependency 物件
System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, sqlDep);
}
}
}
// 回傳查詢結果（或快取的結果）
return objCache;
}
}
```
除了新增了 LinqToSqlCache.cs 到 App\_Code 目錄下以外，還必須修改 Global.asax 檔案，在 Application\_Start 與 Application\_End 事件中分別加入 System.Data.SqlClient.SqlDependency.Start(connstr); 與 System.Data.SqlClient.SqlDependency.Stop(connstr); 用以啟用 ASP.NET 的 SqlDependency 支援。
```
void Application_Start(object sender, EventArgs e) {
string connstr = System.Web.Configuration.WebConfigurationManager
.ConnectionStrings["ConnectionString1"].ConnectionString;
System.Data.SqlClient.SqlDependency.Start(connstr);
}
void Application_End(object sender, EventArgs e) {
string connstr = System.Web.Configuration.WebConfigurationManager
.ConnectionStrings["ConnectionString1"].ConnectionString;
System.Data.SqlClient.SqlDependency.Stop(connstr);
}
```
當然除了這點以外，資料庫也需要特別設定過才能讓 SqlDependency 正常運作，如果你沒有特別設定 SQL Server 2005 的話，第一次執行就會出現「尚未啟用目前資料庫的 SQL Server Service Broker，因此不支援查詢通知。如果您想要使用通知，請啟用這個資料庫的 Service Broker。 」的錯誤訊息，如下圖：
如果要啟用 SQL Server Service Broker 的話，可以用 [ALTER DATABASE](http://msdn.microsoft.com/zh-tw/library/ms174269.aspx) 指令進行啟用 Service Broker 的動作：
```
ALTER DATABASE [YourDBName] SET ENABLE_BROKER
```
執行完後可以用以下指令查詢目前 SQL Server 2005 中有哪些資料庫已經啟用 Service Broker：
```
SELECT name, is_broker_enabled FROM sys.databases
```
但你必須注意一點，當你在執行 *ALTER DATABASE \[YourDBName\] SET DISABLE\_BROKER* 指令的時候可能會發現該指令一直執行都不會停止，那是因為在任何資料庫中啟用 SQL Server Service Broker 時都需要資料庫鎖定，而你要啟用 Service Broker 的資料庫可能還在被 Management Studio 或 ASP.NET 使用中，你可以透過 Management Studio 的 **管理 -> 活動監視器** 功能強制將正連接到你資料庫的連線切斷即可（如下圖示）。
[](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/6f755b6183cefbc9cb72-image_5.png) [](https://stwillblogassets.blob.core.windows.net/files/images/external/stwillblogassets.blob.core.windows.net/b9471d8bab8dca73ed58-image_7.png)
依照上述說明去做應該是會設定成功，接下來若要套用 LinqCache 這個 Extension Method 在你現成的專案裡就很容易了，例如你原本的程式如下：
```
var q = from n in db.Members
where n.IsEnabled == true
select n;
ListView1.DataSource = q;
ListView1.DataBind();
```
改成這樣就好了：
```
var q = from n in db.Members
where n.IsEnabled == true
select n;
ListView1.DataSource = q.LinqCache(db, "FrontSite_EnabledMembers");
ListView1.DataBind();
```
這實在是個很不錯的 LINQ to SQL Cache 解決方案呢！
### 2009-07-15 補充說明
由於上線中的資料庫經常會被鎖定以導致無法正確執行上述指令，建議可利用以下語法先將連線佔領，然後再執行 ENABLE\_BROKER 的變更：
```
USE [master]
GO
ALTER DATABASE [YourDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [YourDBName] SET SINGLE_USER
GO
ALTER DATABASE [YourDBName] SET ENABLE_BROKER
GO
ALTER DATABASE [YourDBName] SET MULTI_USER
GO
```
**相關連結**
- [Caching LinqToSql results with SqlCacheDependency](https://web.archive.org/web/20080827175137/http://code.msdn.microsoft.com/linqtosqlcache)
- [SqlDependency.Start 方法](http://msdn.microsoft.com/zh-tw/library/ms224871.aspx)
- [SqlDependency.Stop 方法](http://msdn.microsoft.com/zh-tw/library/ms224869.aspx)
- [DataContext.GetCommand 方法](http://msdn.microsoft.com/zh-tw/library/system.data.linq.datacontext.getcommand.aspx)
- [ALTER DATABASE (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms174269.aspx)
- [以 SqlCacheDependency 類別在 ASP.NET 中快取](http://msdn.microsoft.com/zh-tw/library/ms178604.aspx)
- [ASP.NET 快取概觀](http://msdn.microsoft.com/zh-tw/library/ms178597.aspx)