How to Delay EF Query Execution Until Actually Needed

Question

I've got a function that determines a user's permissions to interact with certain objects based on a bunch of rules that require querying multiple parts of the database in different ways. One of these queries is particularly expensive (it's querying a massive view made up of unions and CTEs) and this query returns maybe 50 results or so for the current user. This particular query currently runs toward the start of this function and populates a list named userRoles. This is then followed by a series of 10 if...else if...else if... blocks, meaning that once one of these blocks matches the condition, it jumps to the end and returns the result without processing the rest of them. The userRoles list is used in 4 of these blocks, but there are 3 blocks that come before the first block that uses it. So if the conditions in any of those first 3 blocks match, then there's really no reason to query the expensive view in the first place.

In pseudo code, here's what it currently looks like:

var userRoles = await DAL.GetUserRolesAsync(userId); // Expensive query using EF

if (unrelatedConditions == 1) { doSomething1(); }
else if (unrelatedConditions == 2) { doSomething2(); }
else if (unrelatedConditions == 3) { doSomething3(); }
else if (userRoles.Any(x => x.role == "red")) { logicForRedRole(); }
else if (userRoles.Any(x => x.role == "blue")) { logicForBlueRole(); }
// more else if blocks...

What I want to have happen is that the query that populates the list would only get executed if the first three blocks have failed to match and it is now needing the list for the 4th block to test. I need to have the query defined at the top because it's actually more performant to just get all the results and then filter them down to the cases that are needed in each of the blocks than it is to run the query for each block. I've tried constructing a Task<T> object, passing in the asynchronous query (var userRolesTask = new Task<List<UserRole>>(async () => await DAL.GetUserRolesAsync(userId));), but that doesn't work because it actually creates a Task<Task<List<UserRole>>> object and has to be unwrapped to get the actual result, and I'm not sure that await userRolesTask would actually Start the query execution. I could change it to var userRolesTask = new Task<List<UserRole>>(() => DAL.GetUserRolesAsync(userId).Result);, but then it's just blocking the calling thread and I'm losing the benefits of async.

So what I need is something that functions as a Task, but that really just wraps an async method and doesn't execute until awaited. I can definitely make something clunky that would take a Func<Task<T>> and have a T Result property and a async Task<T> GetResult() method that could be awaited on that would only execute the query on the first call. I was just hoping to find something that's either built in or is used seamlessly without having to use some extra construct like await userRolesTask.GetResult().

I've seen some results in my searching that suggest using Rx Observables for doing something like this, but that is not an option on this project.

Answer 1

How about extracting a method:

if (unrelatedConditions == 1) { doSomething1(); }
else if (unrelatedConditions == 2) { doSomething2(); }
else if (unrelatedConditions == 3) { doSomething3(); }
else if ( await IsUserRole() )
else if ...

//...

private async Task<bool> IsUserRole()
{
    var userRoles = await DAL.GetUserRolesAsync(userId); // Expensive query using EF

    if(userRoles.Any(x => x.role == "red")) 
    {
        logicForRedRole(); 
        return true;
    }
    if (userRoles.Any(x => x.role == "blue")) 
    {
        logicForBlueRole(); 
        return true;
    }
    return false;
}

And yet another possibility would be to maybe use Lazy<T>:

using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading.Tasks;
                    
public class Program
{
    public static async Task Main()
    {
        // Just for example ...
        bool condition(int conditionNr) {
            Console.WriteLine("Testing condition #{0}", conditionNr);
            return false;
        }
        
        // The expensive Query
        var userId = 5;
        var expensiveDbAccess = new Lazy<Task<IEnumerable<int>>>(async () => {
            Console.WriteLine("Executing expensive query for User '{0}'", userId);
            await Task.Delay(1000); // "Doing some work"
            return new List<int>{1,2,3,4,5};
// This body would actually just be your
// return await DAL.GetUserRolesAsync(userId);
        });
        
        Console.WriteLine("Going down the rabbit hole ...");
        
        if(condition(1)){ Console.WriteLine("1"); }
        else if(condition(2)){ Console.WriteLine("2"); }
        else if(condition(3)){ Console.WriteLine("3"); }
        else if( (await expensiveDbAccess.Value).Contains(6) ){ Console.WriteLine("Contained 6"); }
        else if( (await expensiveDbAccess.Value).Contains(3) ){ Console.WriteLine("Contained 3"); }
        else if(condition(4)){ Console.WriteLine("4"); }
        else if(condition(5)){ Console.WriteLine("5"); }
    }
}

I tested this in a Fiddle.

Output:

Going down the rabbit hole ...
Testing condition #1
Testing condition #2
Testing condition #3
Executing expensive query for User '5'
Contained 3

If you need the example to be more complex, feel free to comment or add to your question.

Answer 2

If you want to get some kind of "lazy" loading, you can do the following:

    public async Task YourMethod(Guid userId)
    {
        if (unrelatedConditions == 1) { doSomething1(); }
        else if (unrelatedConditions == 2) { doSomething2(); }
        else if (unrelatedConditions == 3) { doSomething3(); }
        else if ((await GetRolesAsync(userId)).Any(x => x.role == "red")) { logicForRedRole(); }
        else if ((await GetRolesAsync(userId)).Any(x => x.role == "blue")) { logicForBlueRole(); }
    }

    private List<Role> _roles;
    private async ValueTask<List<Role>> GetRolesAsync(Guild userId)
    {
        return _roles ??= await DAL.GetUserRolesAsync(userId)
    }

Note, if your framework version doesn't support ValueTask, you can replace it with Task

Answer 3

var userRoles;

if (unrelatedConditions == 1) { doSomething1(); }
else if (unrelatedConditions == 2) { doSomething2(); }
else if (unrelatedConditions == 3) { doSomething3(); }
else {
    userRoles = await DAL.GetUserRolesAsync(userId); // Expensive query using EF
    if (userRoles.Any(x => x.role == "red")) { logicForRedRole(); }
    else if (userRoles.Any(x => x.role == "blue")) { logicForBlueRole(); }
    else if { ... }
}

Answer 4

You can implement and declare it as IQuerable. When you will need it then, you can call the ToList() method in that function. So it will be executed after the ToList() method is called.

Answer 5

As stated in my comment on Fildor's answer, I ended up taking a completely different route and was able to completely refactor out the expensive query into smaller parts that each only get executed when they are actually needed.

But I was still interested in solving the problem for the future and, taking inspiration from Yaroslav's answer, I was able to come up with something over the weekend that almost perfectly satisfies what I was looking for. It's a really simple generic function that you can just throw into a utility class for easy use. Here's the code:

public static Func<Task<T>> CreateLazyTask<T>(Func<Task<T>> asyncCall)
{
    Task<T> executedTask = null;
    return () => executedTask ??= asyncCall();
}

Using my initial sample code, this can be used like so:

var userRolesTask = CreateLazyTask(() => DAL.GetUserRolesAsync(userId)); // Expensive query using EF

if (unrelatedConditions == 1) { doSomething1(); }
else if (unrelatedConditions == 2) { doSomething2(); }
else if (unrelatedConditions == 3) { doSomething3(); }
else if ((await userRolesTask()).Any(x => x.role == "red")) { logicForRedRole(); }
else if ((await userRolesTask()).Any(x => x.role == "blue")) { logicForBlueRole(); }
// more else if blocks...

There are a couple things to point out with this implementation. First off is that there's no async/await used and it's just returning the Task<T> from the asyncCall, so it's not trying to create a state machine or anything for this, nor is it going to have to rewrap the results into a completed Task<T> in subsequent calls (which is what Yaroslav's code does), there's only one Task<T> created by asyncCall and that is returned for every call to the function returned by CreateLazyTask (which is similar to Fildor's Lazy<T> code).

Also, I'm pretty sure this should be thread safe (in the traditional sense). So it should be possible to create a function with this and pass it to a collection of worker threads and it would get executed the first time it was needed and if other threads are awaiting it before completion, it will just handle it correctly and resume all the threads when the task completes and subsequent calls would just get the result directly and continue on. I don't think Yaroslav's method would be thread safe (I think concurrent calls would trigger multiple executions of the query until the result is returned) and I'm not entirely sure how the Lazy<T> example would behave in that scenario.

--Thinking about it a little more, there might actually be a very small time slice where multiple calls could trigger execution multiple times (between when asyncCall is executed and the first await is hit so that it can assign the Task<T> back to executedTask), but that is typically going to be a much smaller window than waiting for it to actually complete. There are also a bunch of other complexities regarding what type of data is being returned and how it would be used that would need to be considered before actually using something like this in a scenario that would allow for parallel execution.--

Next is that there's no need to specify the type of value that is returned, since the compiler is able to determine that directly from the passed in asyncCall. Also, there's no need to use await on something other than what is created (like the Value property on the Lazy<T> example), it's just awaiting the results of the created function. So this is much more easily readable.

So this is basically the best parts of Fildor's and Yaroslav's answers all wrapped into a very neatly contained generic function that can be used anywhere for any type of async call. I hope this helps out anyone who was having similar difficulties to what I was having!

--- Edit in response to comment about thread safety ---

So Stephen Cleary apparently wrote an article about making an async version of Lazy<T> called AsyncLazy<T>. In there, he stated that "It will not be executed more than once, even when multiple threads attempt to start it simultaneously (this is guaranteed by the Lazy type)." So since he said that, I believe that the Lazy<T> method would be thread safe. (Though looking at the reference source for Lazy<T>, I'm seeing comments that make me think that it's still not 100%...) There are some other oddities in that article that I don't understand (like his use of Task.Run, which he specifically discourages in numerous other articles), but overall it's pretty good and it looks like he's changed it a bit since then in his AsyncEx project (including the removal of Task.Run).

But for anyone who needs to use what I wrote where it needs to be thread safe, here's a version that relies on Lazy<T> to prevent race conditions:

public static Func<Task<T>> CreateLazyTask<T>(Func<Task<T>> asyncCall)
{
    var lazy = new Lazy<Task<T>>(asyncCall);
    return () => lazy.Value;
}