SQL 查詢
Entity Framework Core 可讓您在處理關聯式資料庫時,下拉至 SQL 查詢。 如果想要的查詢無法使用 LINQ 表示,或 LINQ 查詢導致 EF 產生效率不佳的 SQL,則建議改用 SQL 查詢。 SQL 查詢可以傳回屬於模型一部分的一般實體類型或無索引鍵實體類型。
基本 SQL 查詢
您可以根據 SQL 查詢,使用 FromSql 來開始 LINQ 查詢:
var blogs = await context.Blogs
.FromSql($"SELECT * FROM dbo.Blogs")
.ToListAsync();
注意
FromSql 是在 EF Core 7.0 中引入。 使用舊版時,請改用 FromSqlInterpolated。
SQL 查詢可用於執行預存程序來傳回實體資料:
var blogs = await context.Blogs
.FromSql($"EXECUTE dbo.GetMostPopularBlogs")
.ToListAsync();
注意
FromSql 只能直接對 DbSet
使用。 它無法透過任意 LINQ 查詢來撰寫。
傳遞參數
警告
使用 SQL 查詢時,請特別注意參數化情形
將任何使用者提供的值引入 SQL 查詢時,請務必小心避免 SQL 插入式攻擊。 程式將使用者提供的字串值整合到 SQL 查詢,且製作使用者提供的值來終止字串並執行其他惡意 SQL 作業時,就會發生 SQL 插入。 若要深入瞭解 SQL 插入,請參閱此頁面。
FromSql 和 FromSqlInterpolated 方法不會造成 SQL 插入,且一律會將參數資料整合為個別 SQL 參數。 不過,如果使用不當,FromSqlRaw 方法可能很容易受到 SQL 插入式攻擊。 如需詳細資訊,請參閱下方。
下列範例在 SQL 查詢字串中加入參數預留位置,並提供額外引數,藉此將單一參數傳遞至預存程序:
var user = "johndoe";
var blogs = await context.Blogs
.FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
.ToListAsync();
雖然這種語法可能看起來很像一般的 C# 字串插補,但會將提供的值包裝於 DbParameter
中,並會在指定 {0}
預留位置之處插入產生的參數名稱。 這可讓 FromSql 避免 SQL 插入式攻擊,並有效率且正確地將值傳送至資料庫。
執行預存程序時,在 SQL 查詢字串中使用具名參數很實用,預存程序具有選擇性參數時尤其如此:
var user = new SqlParameter("user", "johndoe");
var blogs = await context.Blogs
.FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
.ToListAsync();
如果您需要加強控制傳送的資料庫參數,也可以建構 DbParameter
並將它當作參數值來提供。 這可讓您設定參數的精確資料庫類型,或參數大小、精確度或長度等 Facet:
var user = new SqlParameter("user", "johndoe");
var blogs = await context.Blogs
.FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
.ToListAsync();
注意
您傳遞的參數必須完全符合預存程序定義。 請特別注意參數的排序,小心不要遺漏或錯放其中任何一者,或者您也可以考慮使用具名參數標記法。 此外,請確保參數類型的對應,且 Facet (大小、精確度、規模) 皆依需求設定。
動態 SQL 與參數
只要可行,請盡可能使用 FromSql 及其參數化。 不過,在某些情況下,SQL 必須動態拼湊,且資料庫參數無法使用。 舉例來說,假設 C# 變數會保存要篩選的屬性名稱。 您可能會很想要使用 SQL 查詢,如下所示:
var propertyName = "User";
var propertyValue = "johndoe";
var blogs = await context.Blogs
.FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
.ToListAsync();
此程式碼無法運作,因為資料庫不允許參數化欄名稱 (或結構描述的任何其他部分)。
首先,請務必透過 SQL 或其他方式,考量動態建構查詢的意圖。 接受使用者的欄名稱,會使他們可選擇尚未編入索引的欄,讓查詢執行速度非常慢,造成資料庫超載,或者造成他們可選擇的欄可能包含您不想要公開的資料。 除了真正動態的情況,通常兩個欄名稱最好使用兩個查詢,而不是使用參數化將它們折疊成單一查詢。
如果您決定以動態方式建構 SQL,就必須使用 FromSqlRaw,這可讓您直接將變數資料插補到 SQL 字串,而不是使用資料庫參數:
var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");
var blogs = await context.Blogs
.FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
.ToListAsync();
在上述程式碼中,欄名稱會使用 C# 字串插補直接插補到 SQL 中。 您必須自行確認該字串值是否安全,如果來源不安全,請加以清理;也就是偵測特殊字元 (例如分號)、註解和其他 SQL 建構,並正確將它們逸出或拒絕這類輸入。
另一方面,欄值會透過 DbParameter
傳送,因此不必擔心 SQL 插入。
警告
使用 FromSqlRaw 時請提高警覺,一律確認值的來源安全,或已正確清理。 SQL 插入式攻擊可能會對您的應用程式造成災難性的後果。
使用 LINQ 撰寫
您可以使用 LINQ 運算子撰寫在初始 SQL 查詢之上;EF Core 會將 SQL 視為子查詢,並在資料庫中撰寫。 下列範例的 SQL 查詢會從資料表值函式 (TVF) 中選取。 然後使用 LINQ 撰寫於其上,以便執行篩選和排序。
var searchTerm = "Lorem ipsum";
var blogs = await context.Blogs
.FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
.Where(b => b.Rating > 3)
.OrderByDescending(b => b.Rating)
.ToListAsync();
上述查詢會產生下列 SQL:
SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC
包含相關資料
Include
運算子可用來載入相關的資料,就如同所有其他的 LINQ 查詢一般:
var searchTerm = "Lorem ipsum";
var blogs = await context.Blogs
.FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
.Include(b => b.Posts)
.ToListAsync();
若要使用 LINQ 撰寫,您的 SQL 查詢必須可撰寫,因為 EF Core 會將您提供的 SQL 視為子查詢。 可撰寫的 SQL 查詢通常以 SELECT
關鍵字開頭,且不能包含在子查詢中無效的 SQL 功能,例如:
- 結尾分號
- 在 SQL Server 上,結尾的查詢層級提示 (例如,
OPTION (HASH JOIN)
) - 在 SQL Server 中,未在
ORDER BY
子句中搭配OFFSET 0
ORTOP 100 PERCENT
使用的SELECT
子句
SQL Server 不允許透過預存程序呼叫進行撰寫,因此將其他查詢運算子套用到這類呼叫的任何意圖都會導致 SQL 無效。 請在 AsEnumerable 或 AsAsyncEnumerable 之後使用 FromSql 或 FromSqlRaw,確保 EF Core 不會嘗試透過預存程序撰寫。
變更追蹤
使用 FromSql 或 FromSqlRaw 的查詢就如同 EF Core 中所有其他的 LINQ 查詢一般,遵循完全相同的變更追蹤規則。 舉例來說,若查詢會投影實體類型,系統就會依預設追蹤結果。
以下範例會使用從資料表值函式 (TVF) 選取的 SQL 查詢,然後透過 呼叫 AsNoTracking
來停用變更追蹤:
var searchTerm = "Lorem ipsum";
var blogs = await context.Blogs
.FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
.AsNoTracking()
.ToListAsync();
查詢純量 (非實體) 類型
注意
此功能是在 EF Core 7.0 中引入。
雖然 FromSql 對於查詢模型中定義的實體很有用,SqlQuery 可讓您輕鬆地透過 SQL 查詢非實體類型的純量,不需要下拉至較低層級的資料存取 API。 舉例來說,以下查詢會從 Blogs
資料表擷取所有識別碼:
var ids = await context.Database
.SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
.ToListAsync();
您也可以透過 SQL 查詢來撰寫 LINQ 運算子。 不過,由於 SQL 會變成子查詢,它的輸出欄需要以 SQL EF 新增項目來參照,因此您必須將輸出欄命名為 Value
。 舉例來說,下列查詢傳回的識別碼會高於識別碼平均值:
var overAverageIds = await context.Database
.SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
.Where(id => id > context.Blogs.Average(b => b.BlogId))
.ToListAsync();
SqlQuery 可以搭配資料庫提供者所支援的任何純量類型來使用。 如果您想要使用資料庫提供者不支援的類型,您可以使用預先慣例組態來定義它的值轉換。
SqlQueryRaw 允許動態建構 SQL 查詢,就像 FromSqlRaw 對於實體類型的作用一樣。
執行非查詢 SQL
在某些情況下,您可能需要執行不會傳回任何資料的 SQL,通常用於在資料庫中修改資料,或呼叫不會傳回任何結果集的預存程序。 您可以透過 ExecuteSql 執行此操作:
using (var context = new BloggingContext())
{
var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}
這個動作會執行您提供的 SQL,並傳回修改的列數。 ExecuteSql 使用安全參數化來防範 SQL 插入,就像 FromSql 一樣,且 ExecuteSqlRaw 允許動態建構 SQL 查詢,就像 FromSqlRaw 之於查詢。
注意
在 EF Core 7.0 之前,有時您需要使用 ExecuteSql
API 對資料庫執行「大量更新」,如上所述;相比查詢所有相符列,然後再使用 SaveChanges
加以修改,這麼做效率更高。 EF Core 7.0 引進了 ExecuteUpdate 和 ExecuteDelete,可讓您透過 LINQ 來表達有效率的大量更新作業。 建議盡可能使用這些 API 來取代 ExecuteSql
。
限制
傳回 SQL 查詢的實體類型時,請注意幾項限制:
- SQL 查詢必須傳回實體類型所有屬性的資料。
- 結果集中的資料行名稱必須符合屬性所對應的資料行名稱。 請注意,此行為與 EF6 不同;EF6 會忽略 SQL 查詢的屬性與欄的對應,且結果集的欄名稱必須符合屬性名稱。
- SQL 查詢無法包含相關資料。 不過,在許多情況下,您可以使用
Include
運算子來傳回相關資料以在查詢上方進行撰寫 (請參閱包含相關資料)。