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

2023-01-04 09:54 PM

上回研究過 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.");
    }
}

那麼，有沒有可能傳入 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);
}

輕鬆秒殺!