1

LINQ 進階練習 - 動態組裝 Expression 產生 WHERE 條件

 1 year ago
source link: https://blog.darkthread.net/blog/expression-to-where-condition/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

動態組裝 Expression 產生 WHERE 條件-黑暗執行緒

上回研究過 ExpressionTree，這回再用它玩些有趣應用。

在 EF Core Model 要宣告複合索引，Fluent API 做法是在 OnModelCreating() 宣告 modelBuilder.Entity<Person>().HasIndex(p => new { p.FirstName, p.LastName }); (註：EF Core 5.0 起支援用 Attribute 宣告)，EF Core 便能依據匿名物件屬性 p => new { p.FirstName, p.LastName } 得知索引的欄位組成，跟上回從 o => o.PropName 拿到屬性相關資訊一樣讓我覺得很酷，想用它對映成 WHERE 比對條件。

先佈置測試環境，宣告 Product 模型類別及 DbContext 物件，CreateDbContextForTest() 方法建立一個以 SQLite 記憶體暫存資料庫儲存資料，包含五筆測試資料的 DbContext 提供測試：

class Product 
{
    [Key]
    public int ProductId { get; set; }
    [Required]
    public string Brand {get; set;}
    [Required]
    public string Category  {get; set;}
    [Required]
    public string ModelId {get; set;}
    [Required]
    public string Name {get; set;}
}

class MyDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    
    public MyDbContext(DbContextOptions options) : base(options) { }

    public static bool LogEnabled = false;

    public static MyDbContext CreateDbContextForTest()
    {
        var cn = new SqliteConnection("Data Source=:memory:");
        cn.Open();
        var opt = new DbContextOptionsBuilder<MyDbContext>()
            .UseSqlite(cn)
            .LogTo(s =>
            {
                if (LogEnabled && s.Contains("Microsoft.EntityFrameworkCore.Database.Command"))
                    Console.WriteLine(s);
            }, Microsoft.Extensions.Logging.LogLevel.Information)
            // 連同寫入資料庫的參數一起顯示，正式環境需留意個資或敏感資料寫入Log
            .EnableSensitiveDataLogging()
            .Options;
        var dbctx = new MyDbContext(opt);
        dbctx.Database.EnsureCreated();
        dbctx.Products.Add(new Product
        {
            Brand = "ThinkPad",
            Category = "Laptop",
            ModelId = "T470p",
            Name = "小黑 T470p"
        });
        dbctx.Products.Add(new Product
        {
            Brand = "ThinkPad",
            Category = "Laptop",
            ModelId = "T43",
            Name = "小黑 T43"
        });
        dbctx.Products.Add(new Product
        {
            Brand = "ThinkPad",
            Category = "Laptop",
            ModelId = "X21",
            Name = "小黑 X21"
        });
        dbctx.Products.Add(new Product
        {
            Brand = "Sony",
            Category = "Laptop",
            ModelId = "VAIO T13",
            Name = "小銀 VAIO T13"
        });
        dbctx.Products.Add(new Product
        {
            Brand = "Sony",
            Category = "Camera",
            ModelId = "RX-100",
            Name = "RX100"
        });
        dbctx.SaveChanges();
        return dbctx;
    }
}

假想情境一，有三組預先設好篩選條件組合 Brand、Category、Brand+Category，用 o => new { o.Brand, o.Category } 定義篩選欄位，寫一個函式 Expression<Func<TEntity, bool>> GetFindExpression<TEntity>( Expression<Func<TEntity, object>> compositeKeys, params object[] args) 使 dbctx.Products.Where(GetFindExpression(filterBrandAndCategory, "ThinkPad", "Laptop")) 具有 dbctx.Products.Where(o => o.Brand == "ThinkPad" && o.Category == "Laptop") 相同結果：

using System.Linq.Expressions;
using FlexDataAccess;
using Microsoft.Extensions.Options;

var dbctx = MyDbContext.CreateDbContextForTest();

MyDbContext.LogEnabled = true;

// 預先定義好篩選條件
Dictionary<string, Expression<Func<Product, object>>> predefinedFilters = new();
predefinedFilters.Add("Category", o => new { o.Category });
predefinedFilters.Add("Brand", o => new { o.Brand });
predefinedFilters.Add("Brand-Category", o => new { o.Brand, o.Category });

Test("Brand", 2, "Sony");
Test("Category", 4, "Laptop");
Test("Brand-Category", 0, "ThinkPad", "Camera");
Console.ReadLine();

void Test(string filterName, int expectedRowCount, params object[] args)
{
    var res = dbctx.Products.Where(
        GetFindExpression(predefinedFilters[filterName], args)).ToList();
    Console.ForegroundColor = ConsoleColor.Yellow;
    Console.WriteLine($"{filterName}: {string.Join(", ", args.Select(o => o.ToString()))}");
    Console.WriteLine($"Result Count = {res.Count()} (Expected: {expectedRowCount})");
    Console.ResetColor();
}

Expression<Func<TEntity, bool>> GetFindExpression<TEntity>(
    Expression<Func<TEntity, object>> compositeKeys, 
    params object[] args) where TEntity : class
{
    var exp = compositeKeys;
    // 偵測表示式是否為 o => new { ... } 
    if (exp.NodeType == ExpressionType.Lambda && exp.Body.NodeType == ExpressionType.New)
    {
        var newExp = exp.Body as NewExpression;
        // 檢查 new { ... } 屬性個數與參數個數必須相等
        var argList = ((NewExpression)exp.Body).Arguments.Cast<MemberExpression>().ToList();
        if (argList.Count != args.Length)
        {
            throw new ArgumentException("Invalid argument number.");
        }
        var param = Expression.Parameter(typeof(TEntity), "o");
        Expression body = null;
        for (int i = 0; i < argList.Count; i++)
        {
            // 建立等於判斷式
            var eqExp = Expression.Equal(
                Expression.Property(param, argList[i].Member.Name), // 屬性
                Expression.Constant(args[i])); // 常數
            // 第二筆開始用 && 串接
            body = i == 0 ? eqExp : Expression.AndAlso(body, eqExp);
        }
        return Expression.Lambda<Func<TEntity, bool>>(body, param);
    }
    else
    {
        throw new NotSupportedException("Invalid PrimaryKeyProperties.");
    }
}

Fig1_638084377543947943.png

那麼，有沒有可能傳入 new Dictionary<string, object>() { ["Brand"] = "ThinkPad", ["Category"] = "Laptop" } 展開成 dbctx.Products.Where(o => o.Brand == "ThinkPad" && o.Category == "Laptop") 呢？ 這樣好像更有彈性更好用! 來試試：

using System.Linq.Expressions;
using System.Xml;
using FlexDataAccess;
using Microsoft.Extensions.Options;

var dbctx = MyDbContext.CreateDbContextForTest();

MyDbContext.LogEnabled = true;

// 預先定義好篩選條件
Dictionary<string, Expression<Func<Product, object>>> predefinedFilters = new();
predefinedFilters.Add("Category", o => new { o.Category });
predefinedFilters.Add("Brand", o => new { o.Brand });
predefinedFilters.Add("Brand-Category", o => new { o.Brand, o.Category });

Test(new Dictionary<string, object> { ["Brand"] = "Sony" }, 2);
Test(new Dictionary<string, object> { ["Category"] = "Laptop" }, 4);
Test(new Dictionary<string, object> { ["Brand"] = "ThinkPad", ["Category"] = "Camera" }, 0);
Console.ReadLine();

void Test(Dictionary<string, object> parameters, int expectedRowCount)
{
    var res = dbctx.Products.Where(
        GetFindExpression<Product>(parameters)).ToList();
    Console.ForegroundColor = ConsoleColor.Yellow;
    var condText = string.Join(" && ", 
        parameters.Select(o => $"{o.Key}={o.Value}").ToArray());
    Console.WriteLine($"Filter: {condText}");
    Console.WriteLine($"Result Count = {res.Count()} (Expected: {expectedRowCount})");
    Console.ResetColor();
}

Expression<Func<TEntity, bool>> GetFindExpression<TEntity>(
    Dictionary<string, object> parameters) where TEntity : class
{
    var param = Expression.Parameter(typeof(TEntity), "o");
    Expression body = null;
    foreach (var kv in parameters)
    {
        var eqExp = Expression.Equal(
            Expression.Property(param, kv.Key),
            Expression.Constant(kv.Value));
        body = body == null ? eqExp : Expression.AndAlso(body, eqExp);
    }
    return Expression.Lambda<Func<TEntity, bool>>(body, param);
}

輕鬆秒殺!

Fig2_638084377547424414.png

學會以上技巧，我們就有能力動態組裝 WHERE 條件丟給 EF Core 執行，雖然比用 Dapper / FromSql / ExecuteSql 來得複雜，但可以跨資料庫甚至用在物件集合查詢是無可取代的好處，解決問題時再多一種選擇。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK