LINQ to SQL 進階（七）：動態查詢進階與 CLR/SQL 細節差異

承上篇 Find 動態查詢，改為擴充方法並支援 OR 條件，修正 Nullable 型別處理。

改良版 Find 擴充方法

public static IQueryable<TEntity> Find<TEntity>(this IQueryable<TEntity> source, TEntity obj, bool isAnd)
    where TEntity : class {
    if (source == null) throw new ArgumentNullException("Source can't be null!!");
    var properties = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
    Expression condition = null;
    var param = Expression.Parameter(typeof(TEntity), "c"); // 提出迴圈外
    foreach (var p in properties) {
        if (p == null) continue;
        var t = p.PropertyType;
        if (!(t.IsValueType || t == typeof(string))) continue; // 僅支援值型別與字串
        var val = p.GetValue(obj, null);
        if (val == null) continue;
        // 排除不支援比較的 SQL 型別
        var attr = Attribute.GetCustomAttribute(p, typeof(ColumnAttribute));
        if (attr != null) {
            var dbType = ((ColumnAttribute)attr).DbType;
            if (dbType.Contains("Text") || dbType.Contains("NText") || dbType.Contains("Xml") ||
                dbType.Contains("Image") || dbType.Contains("Binary") || dbType.Contains("DateTime") ||
                dbType.Contains("sql_variant") || dbType.Contains("rowversion") ||
                dbType.Contains("UniqueIdentifier") || dbType.Contains("VarBinary(MAX)"))
                continue;
        }
        var right = Expression.Constant(val, p.PropertyType); // 修正 Nullable 型別
        var left = Expression.Property(param, p.Name);
        var filter = Expression.Equal(left, right);
        condition = condition == null ? filter :
            isAnd ? Expression.And(condition, filter) : Expression.Or(condition, filter);
    }
    if (condition != null) {
        var pred = Expression.Lambda<Func<TEntity, bool>>(condition, param);
        return source.Where(pred);
    }
    return source;
}

// 重載預設 AND
public static IQueryable<TEntity> Find<TEntity>(this IQueryable<TEntity> source, TEntity obj)
    where TEntity : class => Find(source, obj, true);

測試與生成 SQL

var q0 = db.Customers.Find(new Customer { City="London", Country="UK" }).ToList();
// WHERE City=@p0 AND Country=@p1

var q1 = db.Customers.OrderBy(c=>c.Country).Find(new Customer { City="London", Country="UK" }, false).ToList();
// WHERE City=@p0 OR Country=@p1 ORDER BY Country

WhereOr：欄位值在集合中

public static IQueryable<TEntity> WhereOr<TEntity, OrType>(this IQueryable<TEntity> source,
    string propertyName, IEnumerable<OrType> values) {
    if (source == null) throw new ArgumentNullException("Source can't be null!!");
    var param = Expression.Parameter(typeof(TEntity), "p");
    var left = Expression.Property(param, propertyName);
    Expression condition = null;
    foreach (var v in values) {
        var filter = Expression.Equal(left, Expression.Constant(v));
        condition = condition == null ? filter : Expression.Or(condition, filter);
    }
    return condition != null ? source.Where(Expression.Lambda<Func<TEntity,bool>>(condition, param)) : source;
}
// 使用：db.Customers.WhereOr("City", new List<string> { "London", "BeiJing" })

CLR 與 SQL 在 Null 比較上的差異

• CLR：null == null 為 true。
• SQL：NULL = NULL 為 UNKNOWN，需用 IS NULL。

LINQ to SQL 將 == 轉為 =，Equals() 轉為 IS NULL 檢查，導致生成 SQL 差異極大。範例：

// ==
WHERE [t0].[Region] = [t1].[ShipRegion]
// Equals
WHERE ([t0].[Region] IS NULL AND [t1].[ShipRegion] IS NULL) OR ([t0].[Region] = [t1].[ShipRegion])

數值精度差異亦可能導致 OverflowException，屬 ADO.NET 層面問題。