iT邦幫忙

第 12 屆 iThome 鐵人賽

DAY 7
0
Modern Web

我與 ASP.NET Core 的 30天系列 第 7

[Day07] Entity Framework Core 的 原始 SQL 查詢(Raw SQL Query) - 我與 ASP.NET Core 3 的 30天

前面介紹如何使用LINQ的方式進行資料的存取,不過相信也會有需要直接下SQL指令的時候,

EF Core 有提供原始 SQL 查詢(Raw SQL Query)

EF Core 3 提供了兩個方法來實現Raw SQL Query,分別是 FromSqlRawFromSqlInterpolated (EF Core 3.0 之前是使用 FromSql 的兩個多載方法)

本篇將會用T-SQL語法作為範例,來介紹如何在EF Core 使用Raw SQL Query。
範例中DbContext的實體會以變數context作為命名。

基礎用法

FromSqlRaw擴充方法基於Raw SQL Query開始 LINQ 查詢。FromSqlRaw只能用在DbSet<>上。

var posts = context.Posts
    .FromSqlRaw("SELECT * FROM dbo.Posts")
    .ToList();

Raw SQL Query 也可以用來執行預存程序(Stored Procedure)。

var posts = context.Posts
    .FromSqlRaw("EXECUTE dbo.GetNewsPosts")
    .ToList();

在撰寫Raw SQL Query的時候不免俗會需要使用變數來加入到查詢的參數當中,但如果用一般加號去做字串連接的方式,就會有SQL injecction的問題,這時候就建議使用FromSqlInterpolated

FromSqlInterpolated類似於FromSqlRaw但允許以防止SQL injection的方式使用字串插值語法。

以下兩個範例來說明差異

var user = "atai";
var posts = context.Posts
    .FromSqlRaw("EXECUTE dbo.GetNewsPosts {0}", user) 
    .ToList();

上述範例看起來雖然像是 String.Format語法,但是在轉換的過程中DbParameter會將 user的值直接填入{0}的位置,如此就容易造成SQL injection的問題

var user = "atai";
var posts = context.posts
    .FromSqlInterpolated($"EXECUTE dbo.GetNewsPosts {user}")
    .ToList();

上述範例使用了FromSqlInterpolated來取代 FromSqlRaw,不同的是FromSqlInterpolated會將字串插符的值轉換成 DbParameter,使查詢語句比較不容易遭到SQL injection的攻擊。

使用LINQ

使用Raw SQL Query做查詢也可以搭配LINQ做使用,如果搭配了LINQ,EF Core就會將Raw SQL Query做為子查詢。

var user = "atai";
var posts = context.Posts
    .FromSqlInterpolated($"Select * From dbo.Posts")  
    .Where(p ⇒ p.Read > 100)
    .ToList();

產出的T-SQL就會為

SELECT *
FROM (
    Select * From dbo.Posts
) AS [p]
WHERE [p].[Read] > 100

因為EF Core 會將 Raw SQL Query 用子查詢的方式與LINQ的查詢組合在一起,所以在Raw SQL Query這邊就必須得是Select開頭的查詢語句。

此外SQL Server 不允許由預存程序組成查詢,因此如果用Raw SQL Query呼叫預存程序搭配LINQ下去作查詢都會導致回傳錯誤。

所以在搭配預存程序使用時,要在FromSqlRaw 或是 FromSqlInterpolated的方法後面加上AsEnumerable() 或是 AsAsyncEnumerable(),先將預存程序的結果取回再進行查詢,以確保EF Core不會用預存程序下去作查詢。

Raw SQL Query 使用限制

1.執行查詢必須回傳對應型別的所有屬性的資料。
2.查詢結果的欄位名稱必須符合相對應類別的屬性名稱
3.查詢不能包含相關資料。不過,在許多情況下,可以透過LINQ的Include進行相關資料的回傳,詳請請參考範例

EF Core 查詢效能問題

EF Core會將查詢結果進行cache,如果需求只是單純的讀取資料,建議使用AsNoTracking()取消對變更的追蹤,因為不需要設定變更追蹤資訊,執行速度會比較快
例如:

var posts = context.Posts
    .FromSqlRaw("SELECT * FROM dbo.Posts")
    .AsNoTracking()
    .ToList();

或是直接在DbContext執行個體的層級直接變更預設的追蹤行為

context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
var posts = context.Posts.ToList();

結語

在EF Core的使用上,不只能使用LINQ,透過搭配原生SQL語法的呼叫,可以讓我們在使用EF Core的時候更加彈性方便,更能因應不同情境組合出不同用法。只是使用上有許多小細節需要注意,但相信只要了解過後便能將EF Core使用的得心應手!

相關資料
https://docs.microsoft.com/en-US/ef/core/querying/raw-sql


上一篇
[Day06] Entity Framework Core 的 CodeFirst與資料庫版控 - 我與 ASP.NET Core 3 的 30天
下一篇
[Day08] Restful API - 我與 ASP.NET Core 3 的 30天
系列文
我與 ASP.NET Core 的 30天31

尚未有邦友留言

立即登入留言