Rafy 中的 Linq 查詢支援（根據聚合子條件查詢聚合父）

BloodyAngel發表於2015-02-03

為了提高開發者的易用性，Rafy 領域實體框架在很早開始就已經支援使用 Linq 語法來查詢實體了。但是隻支援了一些簡單的、常用的條件查詢，支援的力度很有限。特別是遇到對聚合物件的查詢時，就不能再使用 Linq，而只能通過構造底層查詢樹的介面來完成了。由於開發者的聚合查詢的需求越來越多，所以本週我們將這部分進行了增強。

接下來，本文將說明 Rafy 框架原來支援的 Linq 語法，以及最新加入的聚合查詢支援及用法。

 

使用 Linq 查詢的程式碼示例 

public WarehouseList GetByCode(string warehouseCode, string nameKeywords, PagingInfo pagingInfo)
{
    return this.FetchList(r => r.DA_GetByCode(warehouseCode, nameKeywords, pagingInfo));
}
private EntityList DA_GetByCode(string warehouseCode, string nameKeywords, PagingInfo pagingInfo)
{
    var q = this.CreateLinqQuery();
 
    //條件對比
    q = q.Where(e => e.Code == warehouseCode);
    if (!string.IsNullOrEmpty(nameKeywords))
    {
        q = q.Where(e => e.Name.Contains(nameKeywords));
    }
 
    //排序
    q = q.OrderByDescending(w => w.Name);
 
    return this.QueryList(q, pagingInfo);//以指定的分頁資訊 pagingInfo 分頁
}
 

支援的一般查詢

使用 CreateLinqQuery 方法建立出一個 IQueryable<Warehouse> 物件，針對該物件，我們可以以下的標準 Linq 方法：Where、OrderBy、OrderByDescending、ThenBy、ThenByDescending、Count。

對於其中最重要的 Where 方法，Rafy 也支援許多操作，包括：

  • 屬性的各種對比操作（=,!=,>,>=,<,<=,!,Contains,StartsWith,EndsWith等）。
  • 支援兩個屬性條件間的連線條件：&&、||。
  • 支援引用查詢。即間接使用引用實體的屬性來進行查詢，在生成 Sql 語句時，將會生成 INNER JOIN 語句，連線上這些被使用的引用實體對應的表。例如： 
    q = q.Where(warehouse => warehouse.Administrator.Name == "admin");

 

這部分的內容，之前的版本已經支援了，各位可參見 Rafy 框架的使用者手冊。

 

聚合查詢

聚合查詢的功能是，開發者可以通過定義聚合子的屬性的條件，來查詢聚合父。這是本次升級的重點。

例如，書籍管理系統中，Book （書）為聚合根，它擁有 Chapter （章）作為它的聚合子實體，而 Chapter 下則還有 Section（節）。那麼，我們可以通過這個功能，來查詢類似以下需求的資料：

  • 查詢擁有某個章的名字的所有書籍。

要實現這種場景的查詢，我們可以在倉庫的資料層，使用下面的 Linq 語法：

public BookList LinqGetIfChildrenExists(string chapterName)
{
    return this.FetchList(r => r.DA_LinqGetIfChildrenExists(chapterName));
}
private EntityList DA_LinqGetIfChildrenExists(string chapterName)
{
    var q = this.CreateLinqQuery();
    q = q.Where(book => book.ChapterList.Concrete().Any(c => c.Name == chapterName));
    q = q.OrderBy(b => b.Name);
    return this.QueryList(q);
}

其生成的 Sql 如下：

SELECT [T0].[Id], [T0].[Author], [T0].[BookCategoryId], [T0].[BookLocId], [T0].[Code], [T0].[Name], [T0].[Price], [T0].[Publisher]
FROM [Book] AS [T0]
WHERE EXISTS (
    SELECT 1
    FROM [Chapter] AS [T1]
    WHERE [T1].[BookId] = [T0].[Id] AND [T1].[Name] = @p0
)
ORDER BY [T0].[Name] ASC

 

  • 查詢每個章的名字必須滿足某條件的所有書籍。

我們可以在倉庫的資料層，使用下面的 Linq 語法：

public BookList LinqGetIfChildrenAll(string chapterName)
{
    return this.FetchList(r => r.DA_LinqGetIfChildrenAll(chapterName));
}
private EntityList DA_LinqGetIfChildrenAll(string chapterName)
{
    var q = this.CreateLinqQuery();
    q = q.Where(e => e.ChapterList.Cast<Chapter>().All(c => c.Name == chapterName));
    q = q.OrderBy(e => e.Name);
    return this.QueryList(q);
}

生成的 SQL 是：

SELECT [T0].[Id], [T0].[Author], [T0].[BookCategoryId], [T0].[BookLocId], [T0].[Code], [T0].[Name], [T0].[Price], [T0].[Publisher]
FROM [Book] AS [T0]
WHERE NOT (EXISTS (
    SELECT 1
    FROM [Chapter] AS [T1]
    WHERE [T1].[BookId] = [T0].[Id] AND [T1].[Name] != @p0
))
ORDER BY [T0].[Name] ASC

 

  • 查詢某個章中所有節必須滿足某條件的所有書籍。

我們可以在倉庫的資料層，使用下面的 Linq 語法：

public BookList LinqGetIfChildrenExistsSectionName(string sectionName)
{
    return this.FetchList(r => r.DA_LinqGetIfChildrenExistsSectionName(sectionName));
}
private EntityList DA_LinqGetIfChildrenExistsSectionName(string sectionName)
{
    var q = this.CreateLinqQuery();
    q = q.Where(book => book.ChapterList.Concrete().Any(c => c.SectionList.Cast<Section>().Any(s => s.Name.Contains(sectionName))));
    q = q.OrderBy(b => b.Name);
    return this.QueryList(q);
}

將會生成如下 SQL：

SELECT [T0].[Id], [T0].[Author], [T0].[BookCategoryId], [T0].[BookLocId], [T0].[Code], [T0].[Name], [T0].[Price], [T0].[Publisher]
FROM [Book] AS [T0]
WHERE EXISTS (
    SELECT 1
    FROM [Chapter] AS [T1]
    WHERE [T1].[BookId] = [T0].[Id] AND EXISTS (
        SELECT 1
        FROM [Section] AS [T2]
        WHERE [T2].[ChapterId] = [T1].[Id] AND [T2].[Name] LIKE @p0
    )
)
ORDER BY [T0].[Name] ASC

 

  • 同時，這些查詢也可以支援分頁。例如，我們在上面的查詢新增一個分頁條件，程式碼如下： 
public BookList LinqGetIfChildrenExistsSectionName(string sectionName)
{
    return this.FetchList(r => r.DA_LinqGetIfChildrenExistsSectionName(sectionName));
}
private EntityList DA_LinqGetIfChildrenExistsSectionName(string sectionName)
{
    var q = this.CreateLinqQuery();
    q = q.Where(book => book.ChapterList.Concrete().Any(c => c.SectionList.Cast<Section>().Any(s => s.Name.Contains(sectionName))));
    q = q.OrderBy(b => b.Name);
    return this.QueryList(q, new PagingInfo(2, 1));//分頁
}

分成的 SQL 如下：

SELECT TOP 1 [T0].[Id], [T0].[Author], [T0].[BookCategoryId], [T0].[BookLocId], [T0].[Code], [T0].[Name], [T0].[Price], [T0].[Publisher]
FROM [Book] AS [T0]
WHERE EXISTS (
    SELECT 1
    FROM [Chapter] AS [T1]
    WHERE [T1].[BookId] = [T0].[Id] AND EXISTS (
        SELECT 1
        FROM [Section] AS [T2]
        WHERE [T2].[ChapterId] = [T1].[Id] AND [T2].[Name] LIKE @p0
    )
) AND [T0].[Id] NOT IN (
    SELECT TOP 1 [T0].[Id]
    FROM [Book] AS [T0]
    WHERE EXISTS (
        SELECT 1
        FROM [Chapter] AS [T1]
        WHERE [T1].[BookId] = [T0].[Id] AND EXISTS (
            SELECT 1
            FROM [Section] AS [T2]
            WHERE [T2].[ChapterId] = [T1].[Id] AND [T2].[Name] LIKE @p1
        )
    )
    ORDER BY [T0].[Name] ASC
)
ORDER BY [T0].[Name] ASC

 

頭暈，越來越複雜……不過經過測試，上面都沒有什麼問題。

下面是一個單元測試生成的分頁、複雜聚合查詢的 SQL，貼上來觀賞下：

SELECT TOP 2 [T0].[Id], [T0].[Author], [T0].[BookCategoryId], [T0].[BookLocId], [T0].[Code], [T0].[Name], [T0].[Price], [T0].[Publisher]
FROM [Book] AS [T0]
    LEFT OUTER JOIN [BookCategory] AS [T1] ON [T0].[BookCategoryId] = [T1].[Id]
WHERE [T0].[Name] != @p0 AND [T1].[Name] = @p1 AND EXISTS (
    SELECT 1
    FROM [Chapter] AS [T2]
    WHERE [T2].[BookId] = [T0].[Id] AND [T2].[Name] = @p2
) AND EXISTS (
    SELECT 1
    FROM [Chapter] AS [T3]
    WHERE [T3].[BookId] = [T0].[Id] AND [T3].[Name] = @p3 AND NOT (EXISTS (
        SELECT 1
        FROM [Section] AS [T4]
            LEFT OUTER JOIN [SectionOwner] AS [T5] ON [T4].[SectionOwnerId] = [T5].[Id]
        WHERE [T4].[ChapterId] = [T3].[Id] AND ([T4].[Name] NOT LIKE @p4 OR [T4].[SectionOwnerId] IS NULL OR [T5].[Name] != @p5)
    ))
) AND [T0].[Id] NOT IN (
    SELECT TOP 4 [T0].[Id]
    FROM [Book] AS [T0]
        LEFT OUTER JOIN [BookCategory] AS [T1] ON [T0].[BookCategoryId] = [T1].[Id]
    WHERE [T0].[Name] != @p6 AND [T1].[Name] = @p7 AND EXISTS (
        SELECT 1
        FROM [Chapter] AS [T2]
        WHERE [T2].[BookId] = [T0].[Id] AND [T2].[Name] = @p8
    ) AND EXISTS (
        SELECT 1
        FROM [Chapter] AS [T3]
        WHERE [T3].[BookId] = [T0].[Id] AND [T3].[Name] = @p9 AND NOT (EXISTS (
            SELECT 1
            FROM [Section] AS [T4]
                LEFT OUTER JOIN [SectionOwner] AS [T5] ON [T4].[SectionOwnerId] = [T5].[Id]
            WHERE [T4].[ChapterId] = [T3].[Id] AND ([T4].[Name] NOT LIKE @p10 OR [T4].[SectionOwnerId] IS NULL OR [T5].[Name] != @p11)
        ))
    )
    ORDER BY [T0].[Name] ASC
)
ORDER BY [T0].[Name] ASC

 

剛開始支援 Linq 查詢的時候，就已經把聚合查詢的單元測試給寫了。鑑於比較複雜，所以一直沒有實現。這周總算完成了這部分程式碼，心中一塊石頭落了地。Smile

相關文章