原始的 SQL 查詢Raw SQL Queries

Entity Framework Core 可讓您使用關聯式資料庫時,下拉式清單來原始的 SQL 查詢。Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. 這可以是很有用,如果不使用 LINQ,表示您想要執行的查詢,或使用 LINQ 查詢會導致效率不佳 SQL 傳送給資料庫。This can be useful if the query you want to perform can't be expressed using LINQ, or if using a LINQ query is resulting in inefficient SQL being sent to the database.

提示

您可以在 GitHub 上檢視此文章的範例 (英文)。You can view this article's sample on GitHub.

限制Limitations

有幾項限制時要注意的使用原始的 SQL 查詢:There are a couple of limitations to be aware of when using raw SQL queries:

  • SQL 查詢可以只用來傳回屬於您的模型的實體類型。SQL queries can only be used to return entity types that are part of your model. 沒有在我們的待辦項目,來增強啟用從原始的 SQL 查詢傳回特定型別There is an enhancement on our backlog to enable returning ad-hoc types from raw SQL queries.

  • SQL 查詢必須傳回所有屬性的實體類型的資料。The SQL query must return data for all properties of the entity type.

  • 在結果集中的資料行名稱必須符合屬性對應到資料行名稱。The column names in the result set must match the column names that properties are mapped to. 請注意這點不同於 EF6 屬性/資料行對應已忽略原始的 SQL 查詢和結果集資料行名稱必須符合的屬性名稱。Note this is different from EF6 where property/column mapping was ignored for raw SQL queries and result set column names had to match the property names.

  • SQL 查詢不能包含相關的資料。The SQL query cannot contain related data. 不過,在許多情況下您可以撰寫查詢使用之上Include運算子傳回相關的資料 (請參閱包括相關的資料)。However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

  • SELECT陳述式傳遞給這個方法通常應該是可組合: 如果 EF 核心必須評估伺服器上的其他查詢運算子 (例如轉譯 LINQ 運算子之後套用FromSql),提供的 SQL 將會被視為子查詢。SELECT statements passed to this method should generally be composable: If EF Core needs to evaluate additional query operators on the server (e.g. to translate LINQ operators applied after FromSql), the supplied SQL will be treated as a subquery. 這表示傳遞 SQL 應該不會包含任何字元或不是有效的子查詢,這類的選項:This means that the SQL passed should not contain any characters or options that are not valid on a subquery, such as:

    • 尾端的分號a trailing semicolon
    • SQL Server 上尾端查詢層級提示,例如:OPTION (HASH JOIN)On SQL Server, a trailing query-level hint, e.g. OPTION (HASH JOIN)
    • SQL Server 上ORDER BY子句未隨附的TOP 100 PERCENTSELECT子句On SQL Server, an ORDER BY clause that is not accompanied of TOP 100 PERCENT in the SELECT clause
  • SQL 陳述式以外SELECT會自動被辨識為非可組合。SQL statements other than SELECT are recognized automatically as non-composable. 因此,預存程序的完整結果一律會傳回用戶端和之後套用任何 LINQ 運算子FromSql是在記憶體中評估。As a consequence, the full results of stored procedures are always returned to the client and any LINQ operators applied after FromSql are evaluated in-memory.

基本的原始 SQL 查詢Basic raw SQL queries

您可以使用FromSql開始 LINQ 查詢是根據原始的 SQL 查詢的擴充方法。You can use the FromSql extension method to begin a LINQ query based on a raw SQL query.

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

原始的 SQL 查詢可以用來執行預存程序。Raw SQL queries can be used to execute a stored procedure.

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

傳遞參數Passing parameters

如同任何應用程式開發介面可接受 SQL,請務必將參數化的任何使用者輸入,以防止 SQL 資料隱碼攻擊。As with any API that accepts SQL, it is important to parameterize any user input to protect against a SQL injection attack. 您可以在 SQL 查詢字串中包含參數位置,然後提供 做為其他引數的參數值。You can include parameter placeholders in the SQL query string and then supply parameter values as additional arguments. 您提供任何參數值將自動轉換成DbParameterAny parameter values you supply will automatically be converted to a DbParameter.

下列範例會將單一參數傳遞至預存程序。The following example passes a single parameter to a stored procedure. 雖然這看起來像String.Format所提供的值會包裝在語法中,插入的位置參數,以及與產生的參數名稱{0}指定預留位置。While this may look like String.Format syntax, the supplied value is wrapped in a parameter and the generated parameter name inserted where the {0} placeholder was specified.

var user = "johndoe";

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

這是相同的查詢,但使用字串插值語法 EF 核心 2.0 和更新版本都支援:This is the same query but using string interpolation syntax, which is supported in EF Core 2.0 and above:

var user = "johndoe";

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

您也可以建構 DbParameter,並提供它做為參數值。You can also construct a DbParameter and supply it as a parameter value. 這可讓您在 SQL 查詢字串中使用具名的參數This allows you to use named parameters in the SQL query string

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

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

使用 LINQ 撰寫Composing with LINQ

如果資料庫中,可以在撰寫 SQL 查詢,您可以撰寫在初始原始 SQL 查詢使用 LINQ 運算子之上。If the SQL query can be composed on in the database, then you can compose on top of the initial raw SQL query using LINQ operators. 必須與可撰寫 SQL 查詢SELECT關鍵字。SQL queries that can be composed on being with the SELECT keyword.

下列範例會使用原始的 SQL 查詢選取資料表值函式 (TVF),然後撰寫使用 LINQ 來進行篩選和排序。The following example uses a raw SQL query that selects from a Table-Valued Function (TVF) and then composes on it using LINQ to perform filtering and sorting.

var searchTerm = ".NET";

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

撰寫 LINQ 運算子可以用來在查詢中包含相關的資料。Composing with LINQ operators can be used to include related data in the query.

var searchTerm = ".NET";

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

警告

一律使用參數化的未經處理的 SQL 查詢:接受原始 SQL Api 的這類字串FromSqlExecuteSqlCommand允許輕鬆地傳遞做為參數的值。Always use parameterization for raw SQL queries: APIs that accept a raw SQL string such as FromSql and ExecuteSqlCommand allow values to be easily passed as parameters. 除了驗證使用者輸入,請一律使用參數化的未經處理的 SQL 查詢/命令中使用的任何值。In addition to validating user input, always use parameterization for any values used in a raw SQL query/command. 如果您使用字串串連以動態方式建立查詢字串的任何部分,則您必須負責驗證任何輸入,以防止 SQL 資料隱碼攻擊。If you are using string concatenation to dynamically build any part of the query string then you are responsible for validating any input to protect against SQL injection attacks.