Eager Loading Self referencing table in Linq to SQL

Today at my work, I had the need to display category and all the nested categories with in it so that its much easier for Google to have a once place where it can follow all the links to our site. I am not by any means SEO expert but our SEO expert said that Google would seem to come back quite often if it can find hard coded links to our categories as compared to crawling query strings on a page.

Anyway, we have roughly around 1200 categories and subcategories. Each category has subcategories which can have subcategories usually up to 5 level deep. It is a self referencing table that has a parentKeycolumn that allows me to travel the tree. What I had to do was simply to load all the categories in single query but only those categories that have products in them. Initially I felt that using Linq to SQL, that would be piece of cake. All I would have to do is use the loadwith operator and tell that while you are loading category load its child categories as well. Unfortunately query does not work. Linq to SQL has limitations where it wont allow you to eagerly load self referencing entity. Here is the schema that I have and the code that I was trying to use to eagerly load my child categories.

image

 

image

image

In the above code, I am selecting all the categories that have products. I am also telling Linq to SQL to load ChildCategories also. When I run this code, I get an exception which says cycles not allowed in LoadOptions Loadwith type graph. What this means is you cannot immediately load self referencing table or a collection of same entity. I am not sure why Microsoft put this limitation in Linq to SQL to not be able to load self referencing table in one single query. 

Anyway, since the above code did not work and I knew ahead of time that the maximum level that I have to travel is 5 levels deep, so it wouldn't hurt to hard code the depth. Although the query shown below is decently long, my original Linq query in production is around 100 lines long and generates around 1300 lines of SQL.  Here is the hard coded query that goes 4 levels deep to build the hierarchy.

image

 image

 

In the above code, I have hard coded 4 level deep query. I am basically grabbing the child categories for each category at every level. This turns out to be a fairly nasty query. Here is the SQL that gets generated.

image

2 Comments

  • I haven't played with this much on L2S, but in the EF there is a similar limitation in that it can't just perform an arbitrarily deep recursive query for you or even let you specify a number of levels deep that you'd like it to go.

    That said, this is a bit easier in EF than the kind of code you wrote above for L2S. With the EF you can just specify however many levels you would like in your Include string.

    So, if you had an example like yours above with a Category entity which has a Categories set containing nested categories, and if you wanted to go up to 5 levels deep, then you could just do something like this:

    foreach (var category in ctx.Categories.Include("Categories.Categories.Categories.Categories"))
    {
    // do something
    }

    This would load the top level categories as well as up to 4 more levels underneath them. Of course you do have to keep in mind that this would produce a single, potentially very large query to return all the results. So I would recommend perf testing it to decide if you want this approach or if you want to manually write multiple smaller queries...

    - Danny

  • I have a similar structure with a Table containing only 339 rows. The rows are self refereced over 4 levels. If I try to retreive the data with the entgity framework as a hole, it takes me about 120 seconds to build up the data structure.

Comments have been disabled for this content.