Entity Framework Core Like 查詢揭秘

在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，這樣可以直接運行對應的示例，並且也可以直接輸出對應的結果。

我們來看一下運行的結果：

EF.Functions.Like

查詢的結果包含兩條數據，這與我們預期結果一致。

字元串匹配模式

在這裡，我暫且將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” ，運行的結果如下：

EF Contains

運行結果與 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 查詢示例：

EF.Functions.Like

Contains 查詢示例：

EF 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
歡迎轉載，請在明顯位置給出出處及鏈接。