ASP.NET MVC LINQ Entity Framework recursive

Question

I'm not sure how to write LINQ query. I have these models:

class Category
{
    ICollection<Thread> Threads {get;set;}
    ICollection<Category> SubCategories {get;set;}
}

class Thread 
{
    Category Category {get;set;}
    //Some Stuff
}

So, there could be categories linked like -

• Category1
• Category2
  • Category3
  • Category4
    • Category5
  • Category6

I want find all threads linked to Category2 and it SubCategories(3, 4, 5).
I thought about just take Category1 form db, and using C# recursive function build List of threads i need, but i feel it's bad idea.

Any ideas or links would be great. Thank you! There code, but there is Topics(in Threads), i didnt mention it couse it's not rly matter(at least i think so)

public ActionResult ShowCategoryTopics(int id)
{
  var category = db.Categories.Where(x => x.Id == id).FirstOrDefault();
  var topics = GetTopics(category);
  return View();
}
public List<Topic> GetTopics(Category category)
{
    List<Topic> topics = new List<Topic>();

    if (!category.IsDeleted && !category.IsHidden)
        return null;

    foreach (Thread thread in category.Threads)
    {
        topics.AddRange(thread.Topics.Where(x => !x.IsDeleted).ToList());
    }

    foreach(Category childCategory in category.SubCategories)
    {
        topics.AddRange(GetTopics(childCategory));
    }

        return topics;
}

Answer 1

While EF can load joined records lazily and transparently, it can't load recursive joined records cause it's too complicate.

So, first of all, remove the Category.Threads navigation property:

public class Category
{
    public int Id { get; set; }

    public int? ParentId { get; set; }

    // you can remove the attribute
    [ForeignKey(nameof(ParentId))]
    public virtual Category Parent { get; set; }

    public string Title { get; set; }

    public virtual ICollection<Category> SubCategories { get; set; } = new HashSet<Category>();
}

public class Thread
{
    public int Id { get; set; }

    public int CategoryId { get; set; }

    // you can remove the attribute
    [ForeignKey(nameof(Category))]
    public Category Category { get; set; }

    public string Title { get; set; }
}

Now you can use Common Table Expressions to recursive query and Database.SqlQuery<TElement> method to load the result of the query.

This is the SQL query to get all Threads corresponded to the specified @CategoryId and all its subcategories:

WITH RecursiveCategories(Id, ParentId, Title)
AS
(
    SELECT Id, ParentId
    FROM dbo.Categories AS c1
    WHERE Id = @CategoryId
    UNION ALL
    SELECT Id, ParentId
    FROM dbo.Categories AS c2
    INNER JOIN c1 ON c2.ParentId = c1.Id
)
SELECT th.*
FROM dbo.Threads AS th
WHERE th.CategoryId IN (SELECT Id FROM RecursiveCategories)

The method to load threads of specified category recursively:

public IEnumerable<Thread> GetAllRecursivelyByCategoryId(int categoryId)
{
    var query = @"WITH RecursiveCategories(Id, ParentId, Title)
                  AS
                  (
                      SELECT Id, ParentId
                      FROM dbo.Categories AS c1
                      WHERE Id = @CategoryId
                      UNION ALL
                      SELECT Id, ParentId
                      FROM dbo.Categories AS c2
                      INNER JOIN c1 ON c2.ParentId = c1.Id
                  )
                  SELECT th.*
                  FROM dbo.Threads AS th
                  WHERE th.CategoryId IN (SELECT Id FROM RecursiveCategories)";

    var parameter = new SqlParameter("CategoryId", categoryId);

    return _dbContext.Database
                     .SqlQuery<Thread>(query, parameter)
                     .AsEnumerable();
}

This method runs the recursive query and maps the result to enumerable of threads. Here is only one request to the SQL server, and the response contains only necessary threads.

Answer 2

The way to do this all in database would be to use a recursive Common Table Expression (CTE) to extract all the category hierarchy. However this is a bit difficult to implement using Linq without resorting to direct SQL.

As you state there will only be about 100 or so categories it may me simpler to do the category extraction in the code rather than database.

I'm assuming you have the foreign key columns as wells as the navigation properties.

First a Helper function, converts a list of categories to an enumerable of nested ids;

static IEnumerable<int> GetCategoryIds(IList<Category> categories, int? targetId) {
  if (!targetId.HasValue) {
    yield break;
  }
  yield return targetId;
  foreach (var id in categories.Where(x => x.ParentId==targetId).SelectMany(x => GetCategoryIds(x.Id))) {
    yield return id;
  } 
}

Now your query

var ids = GetCategoryIds(db.Categories.ToList(), 2).ToList();
var threads = db.Threads.Where(x => ids.Contains(x.CategoryId));