共用方式為

SQL 查詢

  • 發行項

Entity Framework Core 可讓您在處理關聯式資料庫時，下拉至 SQL 查詢。 如果想要的查詢無法使用 LINQ 表示，或 LINQ 查詢導致 EF 產生效率不佳的 SQL，則建議改用 SQL 查詢。 SQL 查詢可以傳回屬於模型一部分的一般實體類型或無索引鍵實體類型。

提示

您可以檢視本文中的 GitHut 範例。

基本 SQL 查詢

您可以根據 SQL 查詢，使用 FromSql 來開始 LINQ 查詢：

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToList();

注意

FromSql 是在 EF Core 7.0 中引入。 使用舊版時，請改用 FromSqlInterpolated。

SQL 查詢可用於執行預存程序來傳回實體資料：

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

注意

FromSql 只能直接對 DbSet 使用。 它無法透過任意 LINQ 查詢來撰寫。

傳遞參數

警告

使用 SQL 查詢時，請特別注意參數化情形

將任何使用者提供的值引入 SQL 查詢時，請務必小心避免 SQL 插入式攻擊。 程式將使用者提供的字串值整合到 SQL 查詢，且製作使用者提供的值來終止字串並執行其他惡意 SQL 作業時，就會發生 SQL 插入。 若要深入瞭解 SQL 插入，請參閱此頁面。

FromSql 和 FromSqlInterpolated 方法不會造成 SQL 插入，且一律會將參數資料整合為個別 SQL 參數。 不過，如果使用不當，FromSqlRaw 方法可能很容易受到 SQL 插入式攻擊。 如需詳細資訊，請參閱下方。

下列範例在 SQL 查詢字串中加入參數預留位置，並提供額外引數，藉此將單一參數傳遞至預存程序：

var user = "johndoe";

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

雖然這種語法可能看起來很像一般的 C# 字串插補，但會將提供的值包裝於 DbParameter 中，並會在指定 {0} 預留位置之處插入產生的參數名稱。 這可讓 FromSql 避免 SQL 插入式攻擊，並有效率且正確地將值傳送至資料庫。

執行預存程序時，在 SQL 查詢字串中使用具名參數很實用，預存程序具有選擇性參數時尤其如此：

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToList();

如果您需要加強控制傳送的資料庫參數，也可以建構 DbParameter 並將它當作參數值來提供。 這可讓您設定參數的精確資料庫類型，或參數大小、精確度或長度等 Facet：

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

注意

您傳遞的參數必須完全符合預存程序定義。 請特別注意參數的排序，小心不要遺漏或錯放其中任何一者，或者您也可以考慮使用具名參數標記法。 此外，請確保參數類型的對應，且 Facet (大小、精確度、規模) 皆依需求設定。

動態 SQL 與參數

只要可行，請盡可能使用 FromSql 及其參數化。 不過，在某些情況下，SQL 必須動態拼湊，且資料庫參數無法使用。 舉例來說，假設 C# 變數會保存要篩選的屬性名稱。 您可能會很想要使用 SQL 查詢，如下所示：

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

此程式碼無法運作，因為資料庫不允許參數化欄名稱 (或結構描述的任何其他部分)。

首先，請務必透過 SQL 或其他方式，考量動態建構查詢的意圖。 接受使用者的欄名稱，會使他們可選擇尚未編入索引的欄，讓查詢執行速度非常慢，造成資料庫超載，或者造成他們可選擇的欄可能包含您不想要公開的資料。 除了真正動態的情況，通常兩個欄名稱最好使用兩個查詢，而不是使用參數化將它們折疊成單一查詢。

如果您決定以動態方式建構 SQL，就必須使用 FromSqlRaw，這可讓您直接將變數資料插補到 SQL 字串，而不是使用資料庫參數：

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();

在上述程式碼中，欄名稱會使用 C# 字串插補直接插補到 SQL 中。 您必須自行確認該字串值是否安全，如果來源不安全，請加以清理；也就是偵測特殊字元 (例如分號)、註解和其他 SQL 建構，並正確將它們逸出或拒絕這類輸入。

另一方面，欄值會透過 DbParameter 傳送，因此不必擔心 SQL 插入。

警告

使用 FromSqlRaw 時請提高警覺，一律確認值的來源安全，或已正確清理。 SQL 插入式攻擊可能會對您的應用程式造成災難性的後果。

使用 LINQ 撰寫

您可以使用 LINQ 運算子撰寫在初始 SQL 查詢之上；EF Core 會將 SQL 視為子查詢，並在資料庫中撰寫。 下列範例的 SQL 查詢會從資料表值函式 (TVF) 中選取。 然後使用 LINQ 撰寫於其上，以便執行篩選和排序。

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

上述查詢會產生下列 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 = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

若要使用 LINQ 撰寫，您的 SQL 查詢必須可撰寫，因為 EF Core 會將您提供的 SQL 視為子查詢。 可撰寫的 SQL 查詢通常以 SELECT 關鍵字開頭，且不能包含在子查詢中無效的 SQL 功能，例如：

  • 結尾分號
  • 在 SQL Server 上，結尾的查詢層級提示 (例如，OPTION (HASH JOIN))
  • 在 SQL Server 中，未在 SELECT 子句中搭配 OFFSET 0 OR TOP 100 PERCENT 使用的 ORDER BY 子句

SQL Server 不允許透過預存程序呼叫進行撰寫，因此將其他查詢運算子套用到這類呼叫的任何意圖都會導致 SQL 無效。 請在 FromSql 或 FromSqlRaw 之後使用 AsEnumerable 或 AsAsyncEnumerable，確保 EF Core 不會嘗試透過預存程序撰寫。

變更追蹤

使用 FromSql 或 FromSqlRaw 的查詢就如同 EF Core 中所有其他的 LINQ 查詢一般，遵循完全相同的變更追蹤規則。 舉例來說，若查詢會投影實體類型，系統就會依預設追蹤結果。

以下範例會使用從資料表值函式 (TVF) 選取的 SQL 查詢，然後透過 呼叫 AsNoTracking 來停用變更追蹤：

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();

查詢純量 (非實體) 類型

注意

此功能是在 EF Core 7.0 中引入。

雖然 FromSql 對於查詢模型中定義的實體很有用，SqlQuery 可讓您輕鬆地透過 SQL 查詢非實體類型的純量，不需要下拉至較低層級的資料存取 API。 舉例來說，以下查詢會從 Blogs 資料表擷取所有識別碼：

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

您也可以透過 SQL 查詢來撰寫 LINQ 運算子。 不過，由於 SQL 會變成子查詢，它的輸出欄需要以 SQL EF 新增項目來參照，因此您必須將輸出欄命名為 Value。 舉例來說，下列查詢傳回的識別碼會高於識別碼平均值：

var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

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 運算子來傳回相關資料以在查詢上方進行撰寫 (請參閱包含相關資料)。