[EFCore] Like

使用 EFCore 10 來整理 Linq to Entity 產生 TSQL like 語法應用

Linq to Entity 語法

using EFCoreLike.Models;
using Microsoft.EntityFrameworkCore;

namespace EFCoreLike
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using var dbContext = new AdventureWorks2022DbContext();

            // StartWith
            var startWithResult = dbContext.Person
                .Where(p => p.LastName.StartsWith("Hen"))
                .ToList();

            // EndWith
            var endWithResult = dbContext.Person
                .Where(p => p.LastName.EndsWith("Hen"))
                .ToList();

            // Containes
            var containResult = dbContext.Person
                .Where(p => p.LastName.Contains("Hen"))
                .ToList();

            // EF.Functions.Like
            var functionsResult = dbContext.Person
                .Where(p => EF.Functions.Like(p.LastName, "H[a-c]n%"))
                .ToList();
        }
    }
}

產生 TSQL Like 語法觀察

StartWith 轉成 關鍵字%

SELECT [p].[BusinessEntityID], [p].[AdditionalContactInfo], [p].[Demographics], [p].[EmailPromotion], [p].[FirstName], [p].[LastName], [p].[MiddleName], [p].[ModifiedDate], [p].[NameStyle], [p].[PersonType], [p].[Suffix], [p].[Title], [p].[rowguid]
FROM [Person].[Person] AS [p]
WHERE [p].[LastName] LIKE N'Hen%'

EndWith 轉成 %關鍵字

SELECT [p].[BusinessEntityID], [p].[AdditionalContactInfo], [p].[Demographics], [p].[EmailPromotion], [p].[FirstName], [p].[LastName], [p].[MiddleName], [p].[ModifiedDate], [p].[NameStyle], [p].[PersonType], [p].[Suffix], [p].[Title], [p].[rowguid]
FROM [Person].[Person] AS [p]
WHERE [p].[LastName] LIKE N'%Hen'

Containes 轉成 %關鍵字%

SELECT [p].[BusinessEntityID], [p].[AdditionalContactInfo], [p].[Demographics], [p].[EmailPromotion], [p].[FirstName], [p].[LastName], [p].[MiddleName], [p].[ModifiedDate], [p].[NameStyle], [p].[PersonType], [p].[Suffix], [p].[Title], [p].[rowguid]
FROM [Person].[Person] AS [p]
WHERE [p].[LastName] LIKE N'%Hen%'

EFCore 2.0 開始支援 EF.Functions.Like 來自訂 TSQL Like 應用

SELECT [p].[BusinessEntityID], [p].[AdditionalContactInfo], [p].[Demographics], [p].[EmailPromotion], [p].[FirstName], [p].[LastName], [p].[MiddleName], [p].[ModifiedDate], [p].[NameStyle], [p].[PersonType], [p].[Suffix], [p].[Title], [p].[rowguid]
FROM [Person].[Person] AS [p]
WHERE [p].[LastName] LIKE N'H[a-c]n%'

EF 6.5.1 StartWith、EndWith 和 Contains 轉出的 TSQL 跟 EFCore 10 是相同的

• 延伸閱讀
• [EF] Contains 轉 TSQL 語法
• [EFCore] Contain
• 參考資料
• EF Core 2.0 新功能 - EF.Functions.Like()
• DbFunctionsExtensions.Like Method
• Entity Framework Core Like 查询揭秘
• EF Core Funcion Like 用法