SQL 查詢

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

提示

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

基本 SQL 查詢

您可以使用 FromSql 來根據 SQL 查詢開始 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） 中選取的 SQL 查詢。 然後使用 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 上， ORDER BY 子句中未搭配 OFFSET 0 OR TOP 100 PERCENTSELECT 使用的 子句

SQL Server 不允許透過預存過程調用撰寫，因此任何將其他查詢運算符套用至這類呼叫的嘗試都會導致 SQL 無效。 在或 之後FromSqlFromSqlRaw使用 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();

FromSql 可以搭配資料庫提供者所支援的任何純量類型使用。 如果您想要使用資料庫提供者不支援的類型，您可以使用 預先慣例組態 來為其定義值轉換。

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