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.

@IvanStoev oh interesting. Didn't see that one but I'll try. — user2509192
– user2509192, Commented Apr 22, 2020 at 13:39
@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
– Ivan Stoev, Commented Apr 22, 2020 at 14:04
@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
– user2509192, Commented Apr 22, 2020 at 14:49

Sergey Kovalev · Accepted Answer · 2020-04-22 13:23:05Z

1

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.

answered Apr 22, 2020 at 13:23

Sergey Kovalev

9,4712 gold badges30 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2509192 Over a year ago

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).

Sergey Kovalev Over a year ago

@user2509192 Doesn't matter. A standard serializer like JsonConvert.SerializeObject() from Newtonsoft.Json will iterate through IEnumerable objects and include them in JSON.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related