原始 SQL 查詢

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

提示

您可以在 GitHub 上檢視此文章的範例 \(英文\)。

基本的原始 SQL 查詢

您可以使用 FromSqlRaw 擴充方法,根據原始的 SQL 查詢來開始 LINQ 查詢。 FromSqlRaw 只能在上直接位於的查詢根目錄上使用 DbSet<>

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

原始 SQL 查詢可以用來執行預存程序。

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

傳遞參數

警告

一律針對原始 SQL 查詢使用參數化

將任何使用者提供的值引進原始 SQL 查詢時,必須小心避免 SQL 的插入式攻擊。 除了驗證這類值不包含不正確字元,請一律使用參數化,以將值與 SQL 文字分開。

尤其是,絕對不會將串連或插入的字串 ($"") ,並將未經驗證的使用者提供值傳遞至 FromSqlRawExecuteSqlRawFromSqlInterpolatedExecuteSqlInterpolated 方法允許以防止 SQL 插入式攻擊的方式使用字串插補語法。

下列範例會在 SQL 查詢字串中包含參數預留位置,並提供額外的引數,以將單一參數傳遞至預存程式。 雖然此語法看起來可能像 String.Format 語法,但是提供的值會包裝在中, DbParameter 並在指定預留位置的位置插入所產生的參數名稱 {0}

var user = "johndoe";

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
    .ToList();

FromSqlInterpolated 類似于, FromSqlRaw 但可讓您使用字串插補語法。 就像 FromSqlRawFromSqlInterpolated 只能在查詢根目錄上使用。 如同上述範例,此值會轉換為 DbParameter ,而且不容易 SQL 插入。

var user = "johndoe";

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

您也可以建構 DbParameter,並將它提供為參數值。 由於會使用一般 SQL 參數預留位置,而不是字串預留位置,因此 FromSqlRaw 可以安全地使用:

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

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
    .ToList();

FromSqlRaw可讓您在 SQL 查詢字串中使用具名引數,這在預存程式具有選擇性參數時很有用:

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

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser=@user", user)
    .ToList();

注意

參數排序 Entity Framework Core 會根據陣列的順序來傳遞參數 。 傳遞多個時 SqlParameter ,SQL 字串中的順序必須符合預存程式定義中參數的順序。 若未這麼做,可能會導致在執行程式時產生類型轉換例外狀況及/或非預期的行為。

使用 LINQ 撰寫

您可以使用 LINQ 運算子來撰寫初始原始 SQL 查詢的最上層。 EF Core 會將它視為子查詢,並在資料庫中撰寫它。 下列範例會使用從 Table-Valued 函數中選取的原始 SQL 查詢 (TVF) 。 然後使用 LINQ 來撰寫篩選和排序。

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSqlInterpolated($"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
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

撰寫 LINQ 需要可組合的原始 SQL 查詢,因為 EF Core 會將提供的 SQL 視為子查詢。 以 SELECT 關鍵字為開頭的 SQL 查詢才可撰寫。 此外,SQL 傳遞時,不應包含任何在子查詢中不正確字元或選項,例如:

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

SQL Server 不允許撰寫預存程序呼叫,因此任何嘗試將額外的查詢運算子套用到這類呼叫會導致不正確 SQL。 AsEnumerableAsAsyncEnumerable 請在或方法之後使用或方法 FromSqlRawFromSqlInterpolated ,以確定 EF Core 不會嘗試在預存程式上撰寫。

變更追蹤

使用或方法的查詢,會 FromSqlRawFromSqlInterpolated 遵循與 EF Core 中的任何其他 LINQ 查詢完全相同的變更追蹤規則。 例如,若查詢投影實體類型,就會依預設追蹤結果。

下列範例會使用從 Table-Valued 函數中選取的原始 SQL 查詢 (TVF) ,然後透過呼叫來停用變更追蹤 AsNoTracking

var searchTerm = "Lorem ipsum";

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

限制

使用原始 SQL 查詢時有一些要注意的限制:

  • SQL 的查詢必須傳回實體型別之所有屬性的資料。
  • 結果集中的資料行名稱必須符合屬性所對應的資料行名稱。 請注意,這種行為不同于 EF6。 針對原始 SQL 查詢,EF6 已忽略資料行對應的屬性,結果集資料行名稱必須符合屬性名稱。
  • SQL 的查詢不能包含相關資料。 不過,在許多情況下,您可以使用 Include 運算子來傳回相關資料以在查詢上方進行撰寫 (請參閱Include)。