昨天提到實務上查詢條件多半由使用者動態決定,往往得靠SqlQuery或Dapper配合WHERE條件字串動態組裝搞定。(提醒:SQL語法可以用串接的,使用者輸入內容務必使用參數嵌入)但如此就得放棄LINQ .Where()的強型別優點。介紹一個好物,讓你可以兩者兼顧-LINQKit

老樣子,用NuGet就可安裝,查詢關鍵字請填"LinqKit":

最新版的LINQKit需要EntityFramework 6.1.3以上,但依其運作原理,EF5應該也適用,等遇到再說。

LINQKit在Github有詳細說明,這裡只簡單介紹如何用它取代自組SQL WHERE指令。LINQKit靠PredicateBuilder建立動態條件,起手式有兩種:

var pred = PredicateBuilder.False<MyEntity>();
pred = pred.Or(o => o.Col1 == 1);
pred = pred.Or(o => o.Col2 == "X");
ctx.MyEntity.AsExpandable().Where(pred);
等同於ctx.MyEntity.Where(o => o.Col1 == 1 || o.Clo2 == "X")

var pred = PredicateBuilder.True<MyEntity>();
pred = pred.And(o => o.Col1 == 1);
pred = pred.And(o => o.Col2 == "X");
ctx.MyEntity.AsExpandable().Where(pred);
等同於ctx.MyEntity.Where(o => o.Col1 == 1 && o.Clo2 == "X")

注意,OR比對一開始用False<T>,AND則是True<T>,而EF的DbSet<T>要先.AsExpandable()才能在Where()傳入Predicate,否則會出現 The LINQ expression node type 'Invoke' is not supported in LINQ to Entities. 錯誤。另一種做法是不加.AsExpandable(),而是.Where(pred.Expand()),二者皆可。

如果WHERE條件又有AND又有OR怎麼辦?昨天的案例剛好拿來當範例:WHERE PeriodId='2021A' AND (CostCenter='100' AND JobType='A' OR CostCenter='101' AND JobType='S')。秘訣在於Predicate的And()或Or()的傳入參數也可接受其他Predicate,故前述需求可寫成:
var pred2 = PredicateBuilder.False<MyEntity>();
pred2 = pred2.Or(o => o.CostCenter == "100" && o.JobType == "A");
pred2 = pred2.Or(o => o.CostCenter == "101" && o.JobType == "S");
var pred = PredicateBuilder.True<MyEntity>();
pred = pred.And(o => o.PeriodId == "2021A");
pred = pred.And(pred2);
ctx.DataGroup.AsExpandable().Where(pred);

很簡單吧?最後來看看昨天的案例如何用LINQKit改寫:

static string[] keys = new string[] { "100-A", "101-S" };
static void Test3()
{
    using (var ctx = new XDBEntities())
    {
        var pOr = PredicateBuilder.False<DataGroup>();
        keys.ForEach(o =>
        {
            var p = o.Split('-');
            var costCenter = p[0];
            var jobType = p[1];
            pOr = pOr.Or(q => q.CostCenter == costCenter && q.JobType == jobType);
        });
 
        var pWhere = PredicateBuilder.True<DataGroup>();
        pWhere = pWhere.And(o => o.PeriodId == "2021A");
        pWhere = pWhere.And(pOr);
 
        ctx.Database.Log = (m) =>
        {
            Console.WriteLine("**** EF Log ****");
            Console.WriteLine(m);
        };
        var dataGrps = ctx.DataGroup.AsExpandable().Where(pWhere).ToList();
        //var dataGrps = ctx.DataGroup.Where(pWhere.Expand()).ToList();
        foreach (var dg in dataGrps)
        {
            Console.WriteLine("DataGroup:{0}-{1}", dg.CostCenter, dg.JobType);
        }
    }
}

順便示範如何觀察EF6產生的SQL指令,ctx.Database有個Action<string> Log屬性,覆寫成自訂函式,就能接收到EF6的Log,其中包含SQL指令,測試結果如下:

**** EF Log ****
Opened connection at 2015/10/23 上午 05:33:11 +08:00

**** EF Log ****
SELECT
    [Extent1].[PeriodId] AS [PeriodId],
    [Extent1].[CostCenter] AS [CostCenter],
    [Extent1].[JobType] AS [JobType],
    [Extent1].[Status] AS [Status],
    [Extent1].[Tag] AS [Tag]
    FROM [dbo].[DataGroup] AS [Extent1]
    WHERE ('2021A' = [Extent1].[PeriodId]) AND ((([Extent1].[CostCenter] = @p__l
inq__0) AND ([Extent1].[JobType] = @p__linq__1)) OR (([Extent1].[CostCenter] = @
p__linq__2) AND ([Extent1].[JobType] = @p__linq__3)))
**** EF Log ****


**** EF Log ****
-- p__linq__0: '100' (Type = AnsiString, Size = 8000)

**** EF Log ****
-- p__linq__1: 'A' (Type = AnsiString, Size = 8000)

**** EF Log ****
-- p__linq__2: '101' (Type = AnsiString, Size = 8000)

**** EF Log ****
-- p__linq__3: 'S' (Type = AnsiString, Size = 8000)

**** EF Log ****
-- Executing at 2015/10/23 上午 05:33:11 +08:00

**** EF Log ****
-- Completed in 11 ms with result: SqlDataReader

**** EF Log ****


**** EF Log ****
Closed connection at 2015/10/23 上午 05:33:11 +08:00

DataGroup:100-A
DataGroup:101-S

Predicate產生的WHERE條件符合我們的期望:('2021A' = [Extent1].[PeriodId]) AND ((([Extent1].[CostCenter] = @p__l inq__0) AND ([Extent1].[JobType] = @p__linq__1)) OR (([Extent1].[CostCenter] = @p__linq__2) AND ([Extent1].[JobType] = @p__linq__3)))

與自組SQL指令相比,使用LINQKit Predicate寫法相對簡潔,省去為嵌入參數命名、管理參數值陣列的瑣碎手工,又可享受LINQ強型別優勢,不用擔心「打錯字」(呃,被刺傷了),決定將LINQKit收入常備工具箱,多加利用。


Comments

# by tomexou

其實這類sql串接寫法都不容易維護,其實使用一個sql文本寫法(使用@多行),把可能的sql條件都事先寫上去,只要判斷成立時在每行前面控制註解comment符號就好了。 這樣的方式不僅簡單,整段sqltext還能直接貼到ssms裏測試,效能也高很多(StringBuilder.Replace/Insert註解符號)。這種template文本方式,code review時非常有價值性。 我這篇文章寫到這樣的概念: http://tomex.dabutek.com/2014/08/sql.html

# by Jeffrey

to tomexou, 非常巧妙的解法,謝謝分享。

# by Rico

sql串接比較推黑大的 http://blog.darkthread.net/post-2015-08-17-where-is-null-or-trick.aspx 還可以省略重新編譯的效能損耗。 都用linq做where,除非猴子寫的code,不然我想維護性應該都不會太差才是。

# by player

動態LINQ條件? 不是早有個DynamicQuery嗎? LINQKit 有比較好用嗎? http://msdn.microsoft.com/zh-TW/vstudio/bb894665.aspx 下載 CSharpSamples.zip 之後解壓縮, \CSharpSamples\LinqSamples\DynamicQuery\DynamicQuery\Dynamic.cs

# by HamielKuo

Hi 如下例,看來是有差不多的結果,那用linqkit是因為要拆字的關係嗎? int[] keys = new int[] {1,3,4}; var address=Address.Where(x => keys.Contains(x.AddressID)); address.Dump(); 產出的SQL語法: SELECT [Extent1].[AddressID] AS [AddressID], [Extent1].[AddressLine1] AS [AddressLine1], [Extent1].[AddressLine2] AS [AddressLine2], [Extent1].[City] AS [City], [Extent1].[StateProvinceID] AS [StateProvinceID], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[SpatialLocation] AS [SpatialLocation], [Extent1].[rowguid] AS [rowguid], [Extent1].[ModifiedDate] AS [ModifiedDate] FROM [Person].[Address] AS [Extent1] WHERE [Extent1].[AddressID] IN (1, 3, 4)

# by Jeffrey

to HamielKuo, 值得用LINQKit的關鍵在於WHERE條件是執行時期動態決定的,例如:使用者A要查C1=1 AND C2=3,使用者B查C1=2 AND (C3=4 OR C4=5) AND C5=6,此時想在寫Where(o => ...)時把所有可能條件組合都列出來就變得很複雜,而LINQKit允許你跑一段 if else 在不同狀況下加上不同的AND及OR,程式會好寫很多。動態條件情境可參考這篇:http://blog.darkthread.net/post-2015-08-17-where-1-1-and-performance.aspx

Post a comment