How can I get "levels" of a self referencing table in LINQ?

Question

There's a table Category with a pk idCategory and a self-referencing foreign-key fiCategory. That means categories are "main-categories" when fiCategory is null. If fiCategory links to another category, it is a sub-category of it. But it's also valid that this sub-category also has 1-n sub-categories(with fiCategory linking to it's idCategory).

Q: How can i get a list of main-categories,sub-categories,"sub-sub-categories", ...etc. with LINQ?

Backgound:

I'm using typed DataSets to compare data from Server1/MySQL with data from Server2/MS SQL-Server. After normalizing and cleaning(there are several inconsistencies) i want to import the new data into SQL-Server. First of all i have to import the main-categories, then the sub-categories and so on. Otherwise SQL-Server would throw a constraint exception when i would try to insert a row with a foreign-key to a category that is yet not inserted.

These are the tables(left MySQL-source, right SQL-Server destination table):

Kategorie: MySQL-source Category: SQL-Server destination

Here i'm getting the new rows in MySQL that are not in SQL-Server:

src and dest are typed DataSets

Dim idSrc = From c In src.kategorie Select c.kategorie_id
Dim idDest = From c In dest.Category Select c.idCategory
Dim diff = idSrc.Except(idDest)
Dim needUpdate = diff.Any

Now i want to import the new rows. In this way i get all "main-categories":

Dim mainCat = From kat In src.kategorie
            Join d In diff
            On kat.kategorie_id Equals d
            Where kat.IsparentNull
        Select kat

For Each cat In mainCat
    Dim newCat = Me.dest.Category.NewCategoryRow
    newCat.idCategory = cat.kategorie_id
    newCat.Name = cat.name
    newCat.SetfiCategoryNull()
    dest.Category.AddCategoryRow(newCat)
    rowsUpdated += daCategoryOut.Update(dest.Category)
Next

In this way i get all sub-categories:

Dim subCat = From kat In src.kategorie
               Join d In diff
               On kat.kategorie_id Equals d
               Where Not kat.IsparentNull
          Select kat

Both LINQ-queries are working, but how do i get all "levels" of sub-categories? I need to insert the rows from "top" to "bottom". Is there a way that works even with any depth?

At least this is not working(repeating pk-values):

Dim subCatWithChild = From cat In subCat
                      Join child In 
                     (From kat In src.kategorie Where Not kat.IsparentNull)
                      On child.parent Equals cat.kategorie_id
                   Select cat

I'm still learning LINQ and appreciating any kind of suggestions(also in C#). Thank you in advance.

Note: Maybe you know a way i can temporarily disable the foreign key contraint in SQL-Server and enable it after i inserted all rows from ADO.NET. That would be much simpler.

This is the solution, thanks to @Tridus:

Dim mainCat = From kat In src.kategorie
          Where kat.IsparentNull
      Select kat

For Each kat In mainCat
   rowsUpdated += insertCategory(kat, diff, daCategoryOut)
Next

This is the recursive function:

Private Function insertCategory(ByVal parent As CC_IN.kategorieRow, ByVal diff As IEnumerable(Of Int32), ByVal daCategoryOut As CC_OutTableAdapters.CategoryTableAdapter) As Int32
    Dim rowsInserted As Int32 = 0

    If diff.Contains(parent.kategorie_id) Then
        Dim newCat = Me.dest.Category.NewCategoryRow
        newCat.idCategory = parent.kategorie_id
        newCat.Name = parent.name
        If parent.IsparentNull Then
            newCat.fiCategory = parent.parent
        Else
            newCat.SetfiCategoryNull()
        End If
        dest.Category.AddCategoryRow(newCat)
        rowsInserted += daCategoryOut.Update(dest.Category)
    End If

    'get all childs from this parent
    Dim childs = From cat In Me.src.kategorie
               Where Not cat.IsparentNull AndAlso cat.parent = parent.kategorie_id
               Select cat
    'insert all childs for this parent
    For Each child In childs
        rowsInserted += insertCategory(child, diff, daCategoryOut)
    Next

    Return rowsInserted
End Function

The solution should be put into its own answer and marked as the accepted answer, or you should edit the existing accepted answer and add the 'solution' to it so that the solution exists in one place. — George Stocker, Jul 25 '11 at 16:48
I've accepted Tridus answer and added my actual solution to my question clearly visible, hence i don't understand what i would have done better. — Tim Schmelter, Jul 25 '11 at 17:39

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

Yes, Foreign Key constraints can be temporarily disabled.

The best way to do this (other then disabling foreign keys and just copying the entire table row-by-row) is recursively starting with the main categories. Conceptually, you'd do this:

Get the main categories (which you've done)
For each main category
   Is this one in the other DB? If not, add it.
   Get the sub-categories of this main category.
   For each sub-category
     Is this one in the other DB? If not, add it.
     Get the sub-categories of this sub-category.

etc. It's easy to get the sub-categories of whatever category you currently have, so if you just start at the top you can walk the entire tree and add anything that's missing on the other side.

edited May 23 '17 at 10:30

Community

1
1

answered Jul 19 '11 at 22:46

Tridus

5,021
1
19
19

Thanks. That means i should call a function recursively that takes the parent-category and inserts all childs as long as there are childs? But for interest, isn't there a LINQ solution that gives me all levels like a recursive CTE in T-SQL? – Tim Schmelter Jul 20 '11 at 07:24
I don't think there is, no. You'd setup a stored procedure and call that in order to do recursive stuff on the server. – Tridus Jul 20 '11 at 10:00

How can I get "levels" of a self referencing table in LINQ?

1 Answers1