Geek asks and answers

Creating a LINQ expression to search for items associated with all descendants of a node tree

Scenario

I created a database structure that represents a category tree to help classify some of the data that we saved. The implementation is that each record in the table Categoryhas a zero foreign key back to the table Categoryto represent the parent of Categorythis category (one-to-many), basically allowing subcategories at a wider parent level. There is a table CategoryMembershipthat associates a record in a table Itemwith its corresponding Category(many-to-many). I created DBML for this database and has a membership access structure that includes the following:

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

Each item in aMembershipCollectionhas the following membership access structure:

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

Demand

I'm trying to build a LINQ expression that allows me to determine which one Itemshas CategoryMembershipswhich for the requested Category(i.e. aCategory.id = myID) or membership for the descendants of the requested Category, the idea is that I want everyone to Itemsbe in the parent category or at several levels of subcategories .

Essentially, the request will be constructed in the same way as:

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

... where it myInCategoryPredicatereturns a LINQ expression object that will help me make this definition. This, of course, works on the assumption that the table CategoryMembershipis the place to start the search IQueryable(Of Item). Perhaps I made an erroneous assumption, and that is why I came for advice.

Problem

. , CategoryMembership, , , . , - , , DBML.

PredicateBuilder , , , Item , , . , , 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() , , , .

+3
2

linq to sql ( , ). , , , , .

Common Table Expression TSql ... CTE , linq sql.

+1

, , , , . , LINQ-to-SQL .Contains() . , , .

​​ GetAllCategories script. @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 , "" . FYI: SQL Server Management Studio 2008 " " > "" > "" > " ". , .

, 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, .

+1

More articles:


All Articles