在Entity Framework Core 2.0中增加一個很酷的功能:EF.Functions.Like(),最終解析為SQL中的 Like 語句,以便於在 LINQ 查詢中直接調用。不過Entity Framework 中預設提供了 StartsWith、Contains 和 EndsWith... ...
在Entity Framework Core 2.0中增加一個很酷的功能:EF.Functions.Like()
,最終解析為SQL中的Like
語句,以便於在 LINQ 查詢中直接調用。
不過Entity Framework 中預設提供了StartsWith
、Contains
和EndsWith
方法用於解決模糊查詢,那麼為什麼還要提供EF.Functions.Like
,今天我們來重點說說它們之間的區別。
表結構定義
在具體內容開始之前,我們先簡單說明一下要使用的表結構。
public class Category
{
public int CategoryID { get; set; }
public string CategoryName { get; set; }
public override string ToString()
{
return $"{nameof(CategoryID)}: {CategoryID}, {nameof(CategoryName)}: {CategoryName}";
}
}
在 Category 類型定義了兩個欄位:CategoryID、CategoryName。
public class SampleDbContext : DbContext
{
public virtual DbSet<Category> Categories { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("資料庫連接字元串");
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
EntityTypeBuilder<Category> entityTypeBuilder = modelBuilder.Entity<Category>();
entityTypeBuilder.ToTable("Category");
entityTypeBuilder.HasKey(e => e.CategoryID);
entityTypeBuilder.Property(e => e.CategoryID).UseSqlServerIdentityColumn();
}
}
我們使用 SampleDbContext 來訪問資料庫。
CategoryID | CategoryName |
---|---|
1 | Clothing |
2 | Footwear |
3 | Accessories |
在資料庫的 Category 表中插入上面三行記錄。
EF.Functions.Like 使用示例
我們來看一個EF.Functions.Like()
查詢示例,查詢 CategoryName 欄位中包括字元串 “t” 的數據,傳遞的參數是 “%t%”:
[Fact]
public void Like()
{
using (var dataContext = new SampleDbContext()) {
var result= dataContext.Categories.Where(item => EF.Functions.Like(item.CategoryName, "%t%")).ToList();
foreach (var item in result) {
_testOutputHelper.WriteLine(item.CategoryName);
}
}
}
提示:在做一些示例演示時,個人喜歡會用 Xunit + Resharper,這樣可以直接運行對應的示例,並且也可以直接輸出對應的結果。
我們來看一下運行的結果:
查詢的結果包含兩條數據,這與我們預期結果一致。
字元串匹配模式
在這裡,我暫且將StartsWith
、Contains
和EndsWith
方法稱之為字元串匹配模式。
您肯定在Entity Framework中使用過這些方式,我們還是簡單說明一下這三個方法的作用:
StartsWith
:表示字元串的開頭是否與指定的字元串匹配;Contains
:表示指定的子串是否出現在此字元串中;EndsWith
:表示字元串的結尾是否與指定的字元串匹配;
我們可以通過Contains
方法實現與前一個示例一致的功能:
[Fact]
public void Contains()
{
using (var dataContext = new SampleDbContext())
{
var result = dataContext.Categories.Where(item => item.CategoryName.Contains("t")).ToList();
foreach (var item in result)
{
_testOutputHelper.WriteLine(item.CategoryName);
}
}
}
我們在Contains
方法轉入參數“t” ,運行的結果如下:
運行結果與 Like
函數示例的結果是一致的。
在這裡我只列舉了Contains
的示例,StartsWith
和EndsWith
的功能非常相似,我就不重覆列舉了。
這兩個示例的運行結果是一致的,那麼微軟為什麼要提供EF.Functions.Like()
方法呢?
通配符模糊查詢
我們知道在 T-SQL 語句中 Like 關鍵字支持 通配符 ,下麵簡單介紹支持的通配符:
通配符 | 說明 | 示例 |
---|---|---|
% | 包含零個或多個字元的任意字元串。 | WHERE title LIKE '%computer%' 將查找在書名中任意位置包含單詞 "computer" 的所有書名。 |
_(下劃線) | 任何單個字元。 | WHERE au_fname LIKE '_ean' 將查找以 ean 結尾的所有 4 個字母的名字(Dean、Sean 等)。 |
[ ] | 指定範圍 ([a-f]) 或集合 ([abcdef]) 中的任何單個字元。 | WHERE au_lname LIKE '[C-P]arsen' 將查找以 arsen 結尾並且以介於 C 與 P 之間的任何單個字元開始的作者姓氏, 例如 Carsen、Larsen、Karsen 等。 |
[^] | 不屬於指定範圍 ([a-f]) 或集合 ([abcdef]) 的任何單個字元。 | WHERE au_lname LIKE 'de[^l]%' 將查找以 de 開始並且其後的字母不為 l 的所有作者的姓氏。 |
關於 Like 和通配符更多的知識請直接到MSDN中瞭解,鏈接地址:https://msdn.microsoft.com/zh-cn/library/ms179859(v=sql.110).aspx。
我們的將查詢關鍵字由 “t” 改為 “[a-c]”,再來看上面兩個示例分別運行的結果:
EF.Functions.Like 查詢示例:
Contains 查詢示例:
上面運行的結果,Like 查詢的結果返回三條記錄,而 Contains 查詢的結果無任何數據返回。
我們藉助 SQL Server Profiler 分別捕獲這兩個示例實際生成的SQL查詢。
EF.Functions.Like 查詢生成的SQL語句:
SELECT [item].[CategoryID], [item].[CategoryName]
FROM [Category] AS [item]
WHERE [item].[CategoryName] LIKE N'%[a-c]%'
Contains 查詢生成的SQL語句:
SELECT [item].[CategoryID], [item].[CategoryName]
FROM [Category] AS [item]
WHERE CHARINDEX(N'[a-c]', [item].[CategoryName]) > 0
通過上面示例以及捕獲的SQL,我們可以得知,EF.Functions.Like()
查詢會被解釋成為 Like
,實際上是查詢字元串中包括 “a”、“b”、“c” 這三個字元中任何一個字元的數據,而使用 Contains
查詢會被解析成為 CharIndex
函數,實際是指查詢字元串中包括 “[a-c]” 的字元串。
提示:
StartsWith
和EndsWith
分別會被解析成為Left
和Right
函數,測試結果在這裡不再做重覆演示。
結論: 在EF Core中提供
EF.Functions.Like()
方法的根本原因是在 TSQL 語句中Like
關鍵字支持通配符,而在.Net中StartsWith
、Contains
和EndsWith
方法是不支持通配符的;
在EF Core中StartsWith
、Contains
和EndsWith
模糊查詢實際分別被解析成為Left
、CharIndex
和Right
,而不是Like
。
其它要點
通過上面的示例我們已經說清楚了EF.Functions.Like()
方法和StartsWith
、Contains
和EndsWith
方法之間的區別,但是還有以下兩點需要說明。
EF Core StartsWith 優化
如果使用StartWith
方法來實現模糊查詢,解析後的SQL語句會包括一個Like
查詢,您可能要說,剛纔不是已經講過嗎,StartsWith
、Contains
和EndsWith
方法解析後的SQL不是通過 Like
來查詢!先不要著急,我下麵來說清楚這個問題。
StartsWith 查詢示例:
[Fact]
public void StartsWith()
{
using (var dataContext = new SampleDbContext())
{
var result = dataContext.Categories.Where(item => item.CategoryName.StartsWith("Clo")).ToList();
foreach (var item in result)
{
_testOutputHelper.WriteLine(item.CategoryName);
}
}
}
藉助 SQL Server Profiler 捕獲實際生成的SQL查詢:
SELECT [item].[CategoryID], [item].[CategoryName]
FROM [Category] AS [item]
WHERE [item].[CategoryName] LIKE N'Clo' + N'%' AND (LEFT([item].[CategoryName], LEN(N'Clo')) = N'Clo')
在SQL語句中,即用到了Like
,也用到Left
函數,這是為什麼呢?
您可能知道在資料庫查詢時,如果在某一個欄位上使用函數是無法利用到索引的;在使用Left
,CharIndex
和Right
時是無法利用到索引的;而Like
查詢在百分號後置的情況下會利用到索引。關於資料庫的這些知識,在博客園上有很多文章,我就不重覆說明瞭。
結論:
StartsWith
模糊查詢解析後的SQL用到Like
,這是因為Like
在百分號後置的是情況下會利用到索引,這樣查詢速度會更快。Contains
和EndsWith
模糊查詢解析後的SQL不包括Like
查詢,因為在分百號前置的情況無法引用到索引。
關於Contains
和EndsWith
模糊查詢的測試,在這裡不再重覆,您可以自己測試。
EF 6
在EF 6中,模糊查詢解析後的SQL語句與EF Core中略有不同,但是執行的結果沒有區別。
我們在EF 6中分別捕獲StartsWith
、Contains
和EndsWith
解析後的SQL語句,不過我們搜索的關鍵字是:“[a-c]”,包含通配符。
StartsWith 查詢生成的SQL語句:
SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryName] LIKE N'~[a-c]%' ESCAPE N'~'
Contains 查詢生成的SQL語句:
SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryName] LIKE N'%~[a-c]%' ESCAPE N'~'
EndsWith 查詢生成的SQL語句:
SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName]
FROM [dbo].[Category] AS [Extent1]
WHERE [Extent1].[CategoryName] LIKE N'%~[a-c]' ESCAPE N'~'
StartsWith
、Contains
和EndsWith
方法均會被解析為Like
查詢,但是是傳遞的參數由:“[a-c]”變為了“~[a-b]”,前面多了一個特殊符號“~”,並且查詢子句的後面還多了一部分 ESCAPE N'~'。
在MSDN上面有關ESCAPE
關鍵字的解釋,我們摘取其中一部分來說明:
使用 ESCAPE 子句的模式匹配
搜索包含一個或多個特殊通配符的字元串。 例如,customers 資料庫中的 discounts 表可能存儲含百分號 (%) 的折扣值。 若要搜索作為字元而不是通配符的百分號,必須提供 ESCAPE 關鍵字和轉義符。 例如,一個樣本資料庫包含名為 comment 的列,該列含文本 30%。 若要搜索在 comment 列中的任何位置包含字元串 30% 的任何行,請指定 WHERE comment LIKE '%30!%%' ESCAPE '!' 之類的 WHERE 子句。 如果未指定 ESCAPE 和轉義符,則資料庫引擎將返回包含字元串 30 的所有行。
如果您想瞭解EF 6是如果過濾這些通配符的,可以在Github上面瞭解,鏈接地址:https://github.com/aspnet/EntityFramework6/blob/6.1.3/src/EntityFramework.SqlServer/SqlProviderManifest.cs#L164-L189。
結論:在EF 6中
StartsWith
、Contains
和EndsWith
方法均會被解析為Like
查詢,但是如果出現了通配符,框架會結合ESCAPE
以及自身過濾功能將參數進行轉義。
總結
通過上面的敘述,我們可以得到如下一些結論:
- 在EF Core中提供
EF.Functions.Like()
方法的根本原因是在 TSQL 語句中Like
關鍵字支持通配符,而在.Net中StartsWith
、Contains
和EndsWith
方法是不支持通配符的; - 在EF Core中
StartsWith
、Contains
和EndsWith
模糊查詢分別被解析成為Left
、CharIndex
和Right
,而不是Like
。 - 在EF Core中
StartsWith
模糊查詢解析後的SQL用到Like
,這是因為Like
在百分號後置的是情況下會利用到索引,這樣查詢速度會更快; - 在EF 6中,
StartsWith
、Contains
和EndsWith
方法均會被解析為Like
查詢,但是如果出現了通配符,框架會結合ESCAPE
以及自身過濾功能將參數進行轉義; - 在EF 6中,模糊查詢不支持通配符,這一點是因為我沒有找到對應的解決方案,如果您知道,請留言,謝謝!
作者:Sweet Tang
本文地址:http://www.cnblogs.com/tdfblog/p/entity-framework-core-like-query.html
歡迎轉載,請在明顯位置給出出處及鏈接。