Создание выражения LINQ для поиска элементов, связанных со всеми потомками узла дерева

Сценарий

Я построил структуру базы данных, представляющую дерево категорий, чтобы помочь классифицировать некоторые данные, которые мы сохранили. Реализация заключается в том, что каждая запись в таблице Category имеет обнуляемый внешний ключ обратно в таблицу Category для представления родительской Category этой категории (один ко многим), что по существу позволяет использовать подкатегории в пределах более широкого родительского уровня. Существует таблица CategoryMembership, которая связывает запись в таблице Item с соответствующей таблицей Category (многие ко многим). Я создал DBML для этой базы данных, и у нее есть структура доступа к членам, которая включает следующее:

Dim aCategory As New Category()
Dim aParentCategory As Category = aCategory.Parent
Dim aChildCategoryCollection As EntitySet(Of Category) = aCategory.Subcategories
Dim aMembershipCollection As EntitySet(Of CategoryMembership)  = aCategory.CategoryMemberships

Каждый элемент в aMembershipCollection имеет следующую структуру доступа к элементам:

Dim aMembership As CategoryMembership = aMembershipCollection.First()
Dim aLinkedCategory As Category = aMembership.Category
Dim aLinkedItem As Item = aMembership.Item

Требование

Я пытаюсь создать выражение LINQ, которое позволило бы мне определить, какие Items имеют CategoryMemberships для запрошенного Category (т.е. aCategory.id = myID) или членства для потомков запрошенного Category, идея заключается в том, что я хочу, чтобы все Items были в родительском категория или ее несколько уровней подкатегорий.

По сути, запрос будет построен примерно так:

Dim results As IQueryable(Of Item) = _
    From cm In db.CategoryMemberships.Where(myInCategoryPredicate(myID)) _
    Select cm.Item

...где myInCategoryPredicate возвращает объект выражения LINQ, который поможет мне сделать это определение. Это, конечно, работает из предположения, что таблица CategoryMembership — это место, с которого нужно начать извлекать IQueryable(Of Item). Возможно, я сделал ошибочное предположение, и именно поэтому я пришел за советом.

Проблема

Мне трудно видеть лес за деревьями. Я не могу определить, следует ли мне начать создание предиката с Category или с CategoryMembership, а также я не могу понять необходимый код, который мог бы выполнить то, что я хотел бы. Я надеюсь, что кто-нибудь еще, уже построивший аналогичную древовидную структуру для базы данных, сможет помочь мне ориентироваться в классах DBML.

Доступные ресурсы

Ранее я использовал PredicateBuilder и относительно хорошо знаком с его работой, но я не смогли разработать способ перемещения вверх по дереву и рекурсивного построения предиката, который указывал бы, находится ли Статья в категории, которая является либо запрошенной категорией, либо ее дочерней. До сих пор я создал следующее с очень заметным пробелом, помеченным SomeRecursiveCall():

Private Function InCategory(ByVal myID As Integer) As Expression(Of Func(Of CategoryMembership, Boolean))
    Dim predicate = PredicateBuilder.False(Of CategoryMembership)()

    predicate = predicate.Or(Function(cm) cm.fkCategoryID = myID OrElse SomeRecursiveCall())

    Return predicate
End Function

Однако я понимаю, что построитель предикатов здесь может быть бесполезен и может потребоваться другое направление.

Я полагал, что всегда есть возможность выбрать запись Category для запрошенного идентификатора и рекурсивно построить список идентификаторов из нее и всех членов Subcategories, а затем использовать этот список для оценки сравнения .Contains() в этом списке, но я задавался вопросом, не было ли других вариантов, которые не казались такими уродливыми.


tsql vb.net user-defined-functions common-table-expression linq-to-sql
person lsuarez    schedule 07.03.2011    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вы не можете выполнять рекурсию с ограничением данных в запросе linq to sql (где вы хотите выполнять рекурсию до тех пор, пока не будет больше данных для извлечения). Это связано с тем, что транслятор запросов должен знать, когда следует прекратить создание запроса, и он не может просмотреть данные, чтобы узнать это.

Вы можете использовать Common Table Expression в TSql для выполнения рекурсии с ограничением данных. ... Если вы просто добавите этот CTE в представление, вы можете запросить представление из linq в sql.

person Amy B    schedule 08.03.2011
comment
Ненавижу слышать, что ты не можешь этого сделать, но я понимаю, что ты имеешь в виду. Это разочарование, я не могу просто отключить отложенное выполнение, чтобы разрешить ограничение данных. К сожалению, программное решение выдает неподдерживаемое исключение для перегрузки .Any(), которую я, наконец, определил, будет вести себя так, как я хотел (как вы указали). Спасибо, что указали мне на CTE. Я опубликую результаты хранимой процедуры, как только закончу. - person lsuarez; 08.03.2011

arrow_upward
1
arrow_downward

Решение требовало создания возвращающей табличное значение функции из рекурсивного общего табличного выражения, описанного Дэвидом Б., и выполнения запроса к результату функции в LINQ-to-SQL с помощью .Contains() в первичном ключе моей тестовой категории. Подробности того, как это делается, приведены ниже.

Функция GetAllCategories с табличным значением была объявлена ​​с помощью следующего сценария. Когда задан параметр @ParentCategoryID, он возвращает этот родитель вместе со всеми подкатегориями и соответствующей глубиной каждой записи относительно родителя в виде нового поля с именем CategoryLevel.

USE MyDatabase
GO

IF OBJECT_ID (N'dbo.GetAllCategories') IS NOT NULL

DROP FUNCTION dbo.GetAllCategories

GO

CREATE FUNCTION dbo.GetAllCategories(@ParentCategoryID int)

RETURNS TABLE

AS RETURN

(

WITH AllCategories (pkCategoryID, fkParentID, Name, Description, CategoryLevel)
AS
(
-- Anchor member definition
    SELECT c.pkCategoryID, c.fkParentID, c.Name, c.Description, 
        0 AS CategoryLevel
    FROM dbo.Category AS c
    WHERE c.pkCategoryID = @ParentCategoryID
    UNION ALL
-- Recursive member definition
    SELECT c.pkCategoryID, c.fkParentID, c.Name, c.Description,
        CategoryLevel + 1
    FROM dbo.Category AS c
    INNER JOIN AllCategories AS ac
        ON c.fkParentID = ac.pkCategoryID
)

SELECT *
FROM AllCategories

)

Эту возвращающую табличное значение функцию теперь можно включить в DBML из обозревателя серверов, развернув вложенную папку «Функции» подключения к базе данных. К сведению: его также можно увидеть в SQL Server Management Studio 2008 в MyDatabase > Programmability > Functions > Table-value Functions. Теперь эта функция становится членом любого объекта контекста данных, который вы создаете.

Чтобы использовать эту функцию для решения приведенных выше требований, я построил выражение LINQ-to-SQL следующим образом:

Using db As New MyDatabaseDataContext()
    Dim results As IQueryable(Of Item) =
        From cm In db.CategoryMemberships _
        Where (From i In db.GetAllCategories(searchValue) _
               Select i.pkCategoryID).Contains(cm.Category.pkCategoryID) _
        Select cm.Item
End Using

Выражение проецирует список всех первичных ключей из результата функции и использует расширение .Contains() для проверки наличия первичного ключа для каждой CategoryMembership записи Category внутри. В случае успеха выбирается соответствующий Item для членства.

Это вернуло все Items, которые были членами Category с первичным ключом, равным searchValue, или членами любых Category, которые были дочерними элементами этого родителя.

person lsuarez    schedule 08.03.2011
comment
В конечном итоге я перевел табличную функцию в хранимую процедуру (на самом деле не требуется много переписывать сценарий) для возможного использования в модели сущностей ADO.NET в более позднее время. Вариант использования для кода VB.NET в этом случае вообще не меняется. - person lsuarez; 10.03.2011