EF Core Funcion Like 用法

2022-02-07

847
0
EF Core

EF Core Funcion Like 用法，語法上的差異

Entity Framework 物件關聯對應 (O/R Mapping) 一個方便的框架

滿推崇這個框架，搭配著Linq使用可讀性頗高，但效能上與T-SQL還是有差異

來說說為何本次要做此實驗吧

T-SQL 中有一個模糊查詢指令叫做Like，在查詢上為了效能考量多數都會使用Like "Test%"，作為模糊查詢的考慮

而Linq上有一個方便的寫法叫做StartsWith，可以達到相同的指令，所以使用上就會懶得去管EF 轉換出來的T-SQL指令，直到效能爆發

接下來開始使用吧

情境有以下幾種

string test = "test";
TestContext testContext = new TestContext();
var data = testContext.TestTable.Where(x => x.Name.StartsWith("test")).AsQueryable();
data = testContext.TestTable.Where(x => x.Name.StartsWith(test)).AsQueryable();
data = testContext.TestTable.Where(x => EF.Functions.Like(x.Name, "test%")).AsQueryable();
data = testContext.TestTable.Where(x => EF.Functions.Like(x.Name, test + "%")).AsQueryable();
data = testContext.TestTable.Where(x => x.Description.StartsWith("test")).AsQueryable();
data = testContext.TestTable.Where(x => x.Description.StartsWith(test)).AsQueryable();
data = testContext.TestTable.Where(x => EF.Functions.Like(x.Description, "test%")).AsQueryable();
data = testContext.TestTable.Where(x => EF.Functions.Like(x.Description, test + "%")).AsQueryable();
var rersult = data.FirstOrDefault();
Console.ReadLine();

附上資料表的型態

testContext.TestTable.Where(x => x.Name.StartsWith("test")).AsQueryable();轉出的T-SQL為

SELECT [t].[Id], [t].[CreateTime], [t].[Description], [t].[Name]
FROM [Test_Table] AS [t]
WHERE [t].[Name] IS NOT NULL AND ([t].[Name] LIKE N'test%')

testContext.TestTable.Where(x => x.Name.StartsWith(test)).AsQueryable();轉出的T-SQL為

DECLARE @__test_0 nvarchar(4000) = N'test';

SELECT [t].[Id], [t].[CreateTime], [t].[Description], [t].[Name]
FROM [Test_Table] AS [t]
WHERE (@__test_0 = N'') OR ([t].[Name] IS NOT NULL AND (LEFT([t].[Name], LEN(@__test_0)) = @__test_0))

testContext.TestTable.Where(x => EF.Functions.Like(x.Name, "test%")).AsQueryable();轉出的T-SQL為

SELECT [t].[Id], [t].[CreateTime], [t].[Description], [t].[Name]
FROM [Test_Table] AS [t]
WHERE [t].[Name] LIKE N'test%'

testContext.TestTable.Where(x => EF.Functions.Like(x.Name, test + "%")).AsQueryable();轉出的T-SQL為

DECLARE @__p_1 nvarchar(50) = N'test%';

SELECT [t].[Id], [t].[CreateTime], [t].[Description], [t].[Name]
FROM [Test_Table] AS [t]
WHERE [t].[Name] LIKE @__p_1

testContext.TestTable.Where(x => x.Description.StartsWith("test")).AsQueryable();轉出的T-SQL為

SELECT [t].[Id], [t].[CreateTime], [t].[Description], [t].[Name]
FROM [Test_Table] AS [t]
WHERE [t].[Description] IS NOT NULL AND ([t].[Description] LIKE 'test%')

testContext.TestTable.Where(x => x.Description.StartsWith(test)).AsQueryable();轉出的T-SQL為

DECLARE @__test_0 nvarchar(4000) = N'test';

SELECT [t].[Id], [t].[CreateTime], [t].[Description], [t].[Name]
FROM [Test_Table] AS [t]
WHERE (@__test_0 = N'') OR ([t].[Description] IS NOT NULL AND (LEFT([t].[Description], LEN(@__test_0)) = @__test_0))

testContext.TestTable.Where(x => EF.Functions.Like(x.Description, "test%")).AsQueryable();轉出的T-SQL為

SELECT [t].[Id], [t].[CreateTime], [t].[Description], [t].[Name]
FROM [Test_Table] AS [t]
WHERE [t].[Description] LIKE 'test%'

testContext.TestTable.Where(x => EF.Functions.Like(x.Description, test + "%")).AsQueryable();轉出的T-SQL為

DECLARE @__p_1 varchar(50) = 'test%';

SELECT [t].[Id], [t].[CreateTime], [t].[Description], [t].[Name]
FROM [Test_Table] AS [t]
WHERE [t].[Description] LIKE @__p_1

由以上的實驗結果可以知道，由Linq產生出來的StartsWith以及EF.Functions.Like兩者所產出來的語法是有差異的，

最接近慣用T-SQL的Like語法則為EF.Functions.Like效果最佳，

寫Hard Code的效果，最接近我所學習中的EF，但使用上也不是最好的，畢竟他會多出一個Not Null判斷。

提醒有個奇妙的地方，如果查詢的欄位型態為nvarchar則與varchar卻也是有著效能上的差異，

EF上會多帶一個N跟少帶一個N查詢上是有效能落差。

資料參考來源

EF Core

回首頁