Entity Framework Core Like 查詢揭秘

来源:http://www.cnblogs.com/tdfblog/archive/2017/09/12/entity-framework-core-like-query.html
-Advertisement-
Play Games

在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 中預設提供了StartsWithContainsEndsWith方法用於解決模糊查詢,那麼為什麼還要提供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 類型定義了兩個欄位:CategoryIDCategoryName

    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

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

字元串匹配模式

在這裡,我暫且將StartsWithContainsEndsWith方法稱之為字元串匹配模式

您肯定在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的示例,StartsWithEndsWith的功能非常相似,我就不重覆列舉了。

這兩個示例的運行結果是一致的,那麼微軟為什麼要提供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]” 的字元串。

提示: StartsWithEndsWith分別會被解析成為LeftRight函數,測試結果在這裡不再做重覆演示。

結論: 在EF Core中提供EF.Functions.Like()方法的根本原因是在 TSQL 語句中 Like 關鍵字支持通配符,而在.Net中StartsWithContainsEndsWith方法是不支持通配符的;
在EF Core中StartsWithContainsEndsWith模糊查詢實際分別被解析成為LeftCharIndexRight,而不是Like

其它要點

通過上面的示例我們已經說清楚了EF.Functions.Like()方法和StartsWithContainsEndsWith方法之間的區別,但是還有以下兩點需要說明。

EF Core StartsWith 優化

如果使用StartWith方法來實現模糊查詢,解析後的SQL語句會包括一個Like查詢,您可能要說,剛纔不是已經講過嗎,StartsWithContainsEndsWith方法解析後的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函數,這是為什麼呢?

您可能知道在資料庫查詢時,如果在某一個欄位上使用函數是無法利用到索引的;在使用LeftCharIndexRight時是無法利用到索引的;而Like查詢在百分號後置的情況下會利用到索引。關於資料庫的這些知識,在博客園上有很多文章,我就不重覆說明瞭。

結論: StartsWith模糊查詢解析後的SQL用到Like,這是因為Like在百分號後置的是情況下會利用到索引,這樣查詢速度會更快。ContainsEndsWith模糊查詢解析後的SQL不包括Like查詢,因為在分百號前置的情況無法引用到索引。

關於ContainsEndsWith模糊查詢的測試,在這裡不再重覆,您可以自己測試。

EF 6

在EF 6中,模糊查詢解析後的SQL語句與EF Core中略有不同,但是執行的結果沒有區別。

我們在EF 6中分別捕獲StartsWithContainsEndsWith解析後的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'~'

StartsWithContainsEndsWith方法均會被解析為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中StartsWithContainsEndsWith方法均會被解析為Like查詢,但是如果出現了通配符,框架會結合ESCAPE以及自身過濾功能將參數進行轉義。

總結

通過上面的敘述,我們可以得到如下一些結論:

  • 在EF Core中提供EF.Functions.Like()方法的根本原因是在 TSQL 語句中 Like 關鍵字支持通配符,而在.Net中StartsWithContainsEndsWith方法是不支持通配符的;
  • 在EF Core中StartsWithContainsEndsWith模糊查詢分別被解析成為LeftCharIndexRight,而不是Like
  • 在EF Core中StartsWith模糊查詢解析後的SQL用到Like,這是因為Like在百分號後置的是情況下會利用到索引,這樣查詢速度會更快;
  • 在EF 6中,StartsWithContainsEndsWith方法均會被解析為Like查詢,但是如果出現了通配符,框架會結合ESCAPE以及自身過濾功能將參數進行轉義;
  • 在EF 6中,模糊查詢不支持通配符,這一點是因為我沒有找到對應的解決方案,如果您知道,請留言,謝謝!

作者:Sweet Tang
本文地址:http://www.cnblogs.com/tdfblog/p/entity-framework-core-like-query.html
歡迎轉載,請在明顯位置給出出處及鏈接。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 在工作過程中,調用第三方介面出現當返回的數據是中文的時候,中文數據便會變成 這樣??? 迷~ ,一開始我以為是發送成功後接收字元編碼是不是不對,在換過UTF-8,Unicode,。。。都是不行。 最後是問我大佬解決的,希望我大佬帶我飛。 就是解碼用Unicode content-Type 是 app ...
  • Entity Framework在使用時,很多時間操縱的是Model,並沒有寫sql語句,有時候為了調試或優化等,又需要追蹤Entity framework自動生成的sql(最好還能記錄起來,方便出錯時排查) 方式一: 通過System.Data.Entity.DataBase.Log屬性指定一個無 ...
  • RemoteAttribute是asp.net mvc 的一個驗證特性,它位於System.Web.Mvc命名空間 下麵通過例子來說明 很多系統中都有會員這個功能,會員在前臺註冊時,用戶名不能與現有的用戶名重覆,還要求輸入手機號碼去註冊,同時手機號碼也需要驗證是否重覆,下麵是實體類 /// <sum ...
  • 安裝軟體的最佳實踐 雖然我們知道Linux下安裝軟體有三種方式,分別是源代碼安裝,rpm包安裝和yum安裝,但是從可控性和結合自己目前的水平來說,優先選擇以下兩種方式安裝程式。 1,使用rpm包安裝 一般是先在windows下下載好對應的rpm包,然後通過WinSCP工具copy到Linux伺服器上 ...
  • asp.net.core教程(翻譯自微軟官方文檔https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-mvc-app/adding-controller)本教程會教你使用vs2017去搭建基礎的asp.net.core服務程式。本教 ...
  • MaxPageSize -此值控制的最大獨立的每個返回的對象的大小對單個搜索結果中返回的對象數。要執行的搜索的結果可能超過此數目的對象,客戶端必須指定分頁的搜索控制項。這是在不大於MaxPageSize值的組中返回的結果進行分組。總之, MaxPageSize控制在單個搜索結果中返回的對象的數目。預設 ...
  • Windows Service簡介: 一個Windows服務程式是在Windows操作系統下能完成特定功能的可執行的應用程式。Windows服務程式雖然是可執行的,但是它不像一般的可執行文件通過雙擊就能開始運行了,它必須有特定的啟動方式。這些啟動方式包括了自動啟動和手動啟動兩種。對於自動啟動的Win ...
  • 一. Ubuntu 14.04 安裝 1.安裝之前請先刪除之前的.net core 版本 命令如下: 1.1 獲取安裝的.net core 版本 sudo apt --installed list | grep "dotnet-dev" 1.2 刪除安裝的.net core 版本 sudo apt- ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...