How can I fully load a recursive model on multiple levels with entity framework?

Question

EDIT: This is the answer Is recursive query possible in LINQ It was already answered there must have missed that one.

I'm on .NET Core 2.2.

I've looked through multiple questions asked on this site and none of the answers worked for me.

My Model looks like this (simplified):

public class Product
{
    /// <summary>
    ///     Gets or sets the Id.
    /// </summary>
    public string Id { get; set; }

    /// <summary>
    ///     Gets or sets the code.
    /// </summary>
    public string Code { get; set; }

    /// <summary>
    ///     Gets or sets the properties.
    /// </summary>
    public List<Property> Properties { get; set; }

    /// <summary>
    ///     Gets or sets the products
    /// </summary>
    public List<Product> Products { get; set; }
}

public class Property
{
    /// <summary>
    ///     Gets or sets the Id.
    /// </summary>
    public string Id { get; set; }

    /// <summary>
    ///     Gets or sets the code
    /// </summary>
    public string Code { get; set; }
}

I am trying to load a product with all it's sub products also including the properties of all the products.

First, I've been able to load all products recursively but I honestly don't know why it works.

I've tried this:

var products1 = this._context.Products.Where(x => x.Code == "TEST").Include(x => x.Products);

And it would only load the products of the first level products.

Then by pure luck/mistake I've left this line before the aforementioned one:

this._context.Products.ToList()

Then, the first line actually loaded all products recursively... nice I guess?

Anyway, now what I'm trying to do is load all products recursively while also loading all of their properties.

I've tried a couple of things including the shady-looking:

this._context.Products.Where(x => x.Code == "TEST").Include(x => x.Properties)
            .Include(x => x.Products).ThenInclude(x => x.Properties);

But whatever I do I will only load the properties of the products of the first level of recursion. I still fully load all products recursively but only the first 2 levels have their properties loaded.

So ultimately my question is how do I do that? As a bonus question that might be answered anyway if the real question is answered: why do I need to put this line:

this._context.Products.ToList()

before this line:

var products1 = this._context.Products.Where(x => x.Code == "TEST").Include(x => x.Products);

To load the products recursively?

EDIT: Note that I know I can recursively load all Product/Property with multiple queries (one for each Product) in a loop but I definitely want to avoid that if possible.

See https://stackoverflow.com/questions/41894751/is-recursive-query-possible-in-linq/41909322#41909322. — Ivan Stoev, Apr 22 '20 at 13:18
@IvanStoev oh interesting. Didn't see that one but I'll try. — user2509192, Apr 22 '20 at 13:39
@DeepakMishra I don't understand your question. They're defined within the model posted above. Nothing special I guess... — user2509192, Apr 22 '20 at 13:58
@user2509192 In this case you might need to define the relationship using fluent api (or foreign key attribute if it is supported in EF Core) — Deepak Mishra, Apr 22 '20 at 14:01
@IvanStoev just to be sure, will I be able to also load the properties of all products this way? (The property called "Properties" within my model) — user2509192, Apr 22 '20 at 14:01
@DeepakMishra Yeah, that is what Ivan seems to propose. I'll try that. — user2509192, Apr 22 '20 at 14:02
@user2509192 Yes, you will be able to load other related data (with `Include` / `ThenInclude`). The "only" problem is that this is not so efficient for returning single / few items, because it needs to retrieve / process the whole table in memory (thus a lot of unnecessary related data as well). — Ivan Stoev, Apr 22 '20 at 14:04
@IvanStoev as long as it's faster than a query for each Product I'm good. — user2509192, Apr 22 '20 at 14:07
@IvanStoev thanks a lot it works! Btw WithOptional doesn't exist anymore. We now use WithOne and when the FK is nullable it is treated as optionnal otherwise yup, your answer is THE answer. — user2509192, Apr 22 '20 at 14:49

score 1 · Answer 1 · answered Apr 22 '20 at 13:23

Eager loading won't work recursively, but you can use lazy loading. It will hurt your performance, but you will be able to iterate all layers.

Refer to https://learn.microsoft.com/en-us/ef/core/querying/related-data

The simplest way to use lazy-loading is by installing the Microsoft.EntityFrameworkCore.Proxies package and enabling it with a call to UseLazyLoadingProxies. For example:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
    .UseLazyLoadingProxies()
    .UseSqlServer(myConnectionString);

EF Core will then enable lazy loading for any navigation property that can be overridden--that is, it must be virtual and on a class that can be inherited from.

So convert your Lists to a virtual collections and you're good to go.

Thing is my properties aren't directly accessed within the app. The result of my query is sent through a post to another app. (as json). — user2509192, Apr 22 '20 at 13:34
@user2509192 Doesn't matter. A standard serializer like `JsonConvert.SerializeObject()` from `Newtonsoft.Json` will iterate through `IEnumerable` objects and include them in JSON. — Sergey Kovalev, Apr 23 '20 at 00:32

How can I fully load a recursive model on multiple levels with entity framework?

1 Answers1