Batched queries for explicit loading

[samardzicneo]

Includes and Lazy Loading are in my eyes the two major ways to query for related data. Both of them have their disadvantages: Includes make it hard to optimize queries when reusing them, and lazy loading can cause a massive amount of DB round trips. I'm proposing a middle ground to this.

Tracking which query an entity originated from (possibly by creating a new proxy on each query, even if the object is already cached locally - maybe EF already does this?) and, when a navigation is lazy-loaded, load that same navigation for all entities of that type from the query it originated from, effectively like a lazily-loaded split-query include.

I see some possible problems with this, like I mentioned above, I haven't looked at the current code and don't know if EF Core already creates a new proxy for each query even if the entity is already in memory. Tracking the original query would probably have to be done through the proxy object too. Also, making sure that you only run the query once even if accessed multiple times simultaneously could be difficult?

[roji]

@samardzicneo where do you see the usefulness of something like this? If you know in advance that you're going to need the dependents, it's best to just use eager loading (Include). Otherwise, it doesn't seem at all generally true that if you need one dependent, you're going to need the dependents from all loaded principals. We certainly haven't received any requests for something like this before.

Note that lazy loading techniques are generally discouraged, as they hide potentially expensive I/O operations and do not support async (as they're triggered by synchronous property access); because of that there's not a high chance we'd choose to invest in this. Note also that in addition to eager and lazy loading, EF supports explicit loading, though AFAIK it's not possible to batch multiple explicit loads in a single roundtrip.

[samardzicneo]

@samardzicneo where do you see the usefulness of something like this? If you know in advance that you're going to need the dependents, it's best to just use eager loading (Include). Otherwise, it doesn't seem at all generally true that if you need one dependent, you're going to need the dependents from all loaded principals. We certainly haven't received any requests for something like this before.

Note that lazy loading techniques are generally discouraged, as they hide potentially expensive I/O operations and do not support async (as they're triggered by synchronous property access); because of that there's not a high chance we'd choose to invest in this. Note also that in addition to eager and lazy loading, EF supports explicit loading, though AFAIK it's not possible to batch multiple explicit loads in a single roundtrip.

I'm am aware of explicit loading, but like you said, I haven't found a way of batching them.

The use case I came across just today might very well be quite specific, but I'm going to try and explain it: We're building a hospitality solution. It's quite possible that one user might have permissions for different things in multiple hotels - Our current approach to load these permissions is an extension method for our DatabaseContext. Then, multiple extension methods exist on the User entity to check for permissions for specific things.

I can write up a little code example tomorrow but doing it on my phone is quite hard.

Alternatively to what I'm suggesting, and because I agree that lazy loading should be avoided, a batched explicit load that skips already loaded navigations would be a great option for this.

[ajcvickers]

@samardzicneo Can you provide an example of what code you would write to trigger this loading?

[samardzicneo]

@ajcvickers This is just a very simplified example of a snippet we have in one of our codebases at work:

    public User[] GetUsersOfCompany(string domain = "example.ch")
    {
        var users = _db.Users
            .Where(x => x.Email.EndsWith($"@{domain}"))
            .ToArray();

        // Imagine this property doesn't always get called.
        if (ImagineSomeConditionHere())
        {
            return FilterUsersByPermission(users, "admin");
        }

        return users;
    }

    private User[] FilterUsersByPermission(User[] users, string permission)
    {
        // Important section:
        // (This would load the includes, but only if they're not already loaded.)
        _db.Entries(users)
            .Include(x => x.Permissions)
            .ThenInclude(x => x.SomethingElse)
            .Include(x => x.SomeOtherUserProperty.Where(x => x.Something))
            .Load();

        return users
            .Where(x => x.Permissions.Any(x => x.Type == permissions))
            .ToArray();
    }

I realise that in this specific example the whole code could be refactored and simplified to avoid this but again, it's just an example. The only important part is the _db.Entries() section.

[ajcvickers]

Note from triage: this would better be handled using three calls to Load which will generate three queries, each one only loading if the navigation is not already loaded. It should then be possible to batch these queries when #10879 is implemented.