LINQ to SQL 進階（六）：以物件動態查詢與 Log 記錄

傳統動態 SQL 靠字串拼接，LINQ 提供物件導向查詢。本文實作 Find<TEntity>(TEntity obj)：依物件非 null 屬性自動建構 Where 條件。

核心思路：反射 + 運算式樹

1. 反射列舉物件公用屬性。
2. 屬性值非 null 才加入條件。
3. 建構 Expression<Func<TEntity,bool>>：ParameterExpression → Expression.Property / Expression.Constant → Expression.Equal → Expression.Lambda。
4. 串聯 query.Where(pred)（預設 AND）。

完整實作

public IQueryable<TEntity> Find<TEntity>(TEntity obj) where TEntity : class {
    var properties = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
    var query = this.GetTable<TEntity>().AsQueryable();
    foreach (var p in properties) {
        if (p == null) continue;
        var t = p.PropertyType;
        if (t.IsValueType || t == typeof(string) || t == typeof(byte[]) || t == typeof(object) ||
            t == typeof(XDocument) || t == typeof(Binary)) {
            var val = p.GetValue(obj, null);
            if (val == null) continue;
            var param = Expression.Parameter(typeof(TEntity), "c");
            var right = Expression.Constant(val);
            var left = Expression.Property(param, p.Name);
            var filter = Expression.Equal(left, right);
            var pred = Expression.Lambda<Func<TEntity, bool>>(filter, param);
            query = query.Where(pred);
        }
    }
    return query;
}

測試

var c = new Customer { City="London", Phone="23236133" };
var q = db.Find<Customer>(c).ToList();
// SQL: WHERE Phone=@p0 AND City=@p1

待解決問題

• 僅支援 AND，不支援 OR。
• 不支援模糊查詢。

Log 記錄實作

partial class DataMappingDataContext {
    private static StreamWriter sw = new StreamWriter(Path.Combine(Directory.GetCurrentDirectory(), "log.txt"), true);
    public DataMappingDataContext(bool withLog) : this() {
        OnCreated();
        if (withLog) this.Log = sw;
    }
    protected override void Dispose(bool disposing) {
        base.Dispose(disposing);
        sw.Flush();
    }
}
// 使用：using (var db = new Northwind(true)) { ... }

靜態 StreamWriter 避免多 DataContext 並發寫入衝突，Dispose 時 Flush 確保寫入。