CODEWITHSUNDEEP
vb.netef-code-first
Nick
Nick

Reputation: 745

Recursive iteration through Child entities in entity framework codefirst

I have created a VB.net WinForms program and a database using EF Codefirst. In one of the tables, "Categories", there is a column named "ParentCategory_CategoryId" which is the parent category of that particular category (nb it can be NULL). This allows me an unlimited amount of nesting for sub-categories. so far, so good (Categories definition below):

Public Class Category
    Public Property CategoryId As Integer
    Public Property CategoryName As String

    Public Overridable Property ChildCategories As List(Of Category)
    Public Overridable Property ParentCategory As Category
End Class

The problem arises when I try to populate a TreeView control with this data. My intention is to iterate recursively through the categories and add a TreeNode for each category, and add a sub node for each child category. The VB code is as follows:

Private Sub Categories_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ' Create new data context        
    Dim dc As New MyContext
    ' Call the recursive function once with Parent category as nothing
    LoadNodesOntoTreeview(dc, Nothing, Nothing)

End Sub

Public Sub LoadNodesOntoTreeview(dc As MyContext, Optional ParentCategory As Category = Nothing, Optional ByRef ParentNode As TreeNode = Nothing)
    Try
        For Each c As Category In dc.Categories.Where(Function(x) x.ParentCategory Is ParentCategory).ToList
            Dim n As New TreeNode With {.Text = c.CategoryName}
            LoadNodesOntoTreeview(dc, c, n)
            If ParentNode Is Nothing Then
                TreeView1.Nodes.Add(n)
            Else
                ParentNode.Nodes.Add(n)
            End If
        Next
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

For the first iteration (ParentCategory = Nothing) this works fine, however the problem occurs when calling

LoadNodesOntoTreeview(dc, c, n)

within iteslf. I get the error:

"Unable to create a constant value of
type 'System.Data.Entity.DynamicProxies.Category_D3161C0FA58DECDDCD3237
36A77B49BF9AC13F6AB1D9C56D7946173054210834'. Only primitive types or
enumeration types are supported in this context."

Any ideas would be greatly appreciated.

Upvotes: 1

Views: 724

Answers (3)

Nick
Nick

Reputation: 745

Thanks guys, that has sorted the problem, but I have had to seperate the two cases where the category has 1) no parent and 2) a parent (solution below).

I have also moved

Dim dc as new myContext

inside the recursive function as I was generating an additional error (the data context cannot be re-used until it has been closed - obviously!).

Private Sub Categories_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    LoadNodesOntoTreeview()
End Sub

Public Sub LoadNodesOntoTreeview(Optional ParentCategory As Category = Nothing, Optional ByRef ParentNode As TreeNode = Nothing)
    Try
        Dim dc As New MyContext
        If ParentCategory Is Nothing Then
            For Each c As Category In dc.Categories.Where(Function(x) x.ParentCategory Is Nothing)
                Dim n As New TreeNode With {.Text = c.CategoryName}
                LoadNodesOntoTreeview(c, n)
                TreeView1.Nodes.Add(n)
            Next
        Else
            For Each c As Category In dc.Categories.Where(Function(x) x.ParentCategory.CategoryId = ParentCategory.CategoryId).ToList
                Dim n As New TreeNode With {.Text = c.CategoryName}
                LoadNodesOntoTreeview(c, n)
                ParentNode.Nodes.Add(n)
            Next
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

I know I could pass just the ParentCategoryId into the recursive function rather than the Category object itself.

Upvotes: 0

Fredrik Ljung
Fredrik Ljung

Reputation: 1475

Entity Frameworks doesn't know how to transfer classes to actual SQL. If you instead use the CategoryId in your where clause it should work. I use C# so hopefully the following makes sense:

For Each c As Category In dc.Categories.Where(Function(x) x.ParentCategory.CategoryId = ParentCategory.CategoryId).ToList

Upvotes: 1

Paul Taylor
Paul Taylor

Reputation: 5751

The Where clause in your query contains an expression that cannot be resolved by Linq to Entities:

dc.Categories.Where(Function(x) x.ParentCategory Is ParentCategory)

You can avoid this by comparing the keys of the two objects, rather than their types:

dc.Categories.Where(Function(x) x.ParentCategory.CategoryId = ParentCategory.CategoryId)

Alternatively, you can run this query using Linq to Objects. To do that, you need to retrieve the Categories data as a collection of objects before evaluating the where clause:

dc.Categories.ToList().Where(Function(x) x.ParentCategory Is ParentCategory)

Clearly this may mean passing a lot more data across the wire, so the first approach is preferable.

Upvotes: 1

Related Questions