.NetCore EF - Main query used by sync/async query

Question

So I have a main sync query like:

private List<MyCustomClass> Foo()
{
    return _ctx.Table1.Where(...).Select(s => new MyCustomClass { ... }).ToList();
}

and I need to use the results of it in a sync query:

1. return Foo().Where(...).GroupBy(...).Select(s => new MyCustomClass2 { ... }).ToList();

AND ALSO in an async query:

2. return await Foo().Where(...).GroupBy(...).Select(s => new MyCustomClass3 { ... }).ToListAsync();

cause I need to filter the main query results for other fields and then group by and get other informations.

The (1) query does work but the (2) query does not (it doesn't even show .ToListAsync()). I would like to leave both doors open.

I already try to add AsQueryable() to the main query but it doesn't work.

Someone could help me? Thanks a lot.

Answer 1

In a nutshell, using IQueryable enables both asynchronous and synchronous consumption:

private IQueryable<Table1> Foo()
{
    IQueryable<Table1> query =_ctx.Table1.Where(...);
    return query;
}

Then when you go to call "Foo()":

// synchronous code:
var results = Foo()
    .Select(s => new MyCustomClass { ... });
    .ToList();

// asynchronous code:
var results = await Foo()
    .GroupBy(...)
    .Select(s => new MyCustomClass3 { ... })
    .ToListAsync();

If you want Foo() to have basic global conditions, but then a call consuming those results wants to expand on the Where condition, that is fine:

var results = await Foo()
    .Where(... more conditions, will be ANDed to existing conditions ...)
    .GroupBy(...)
    .Select(s => new MyCustomClass3 { ... })
    .ToListAsync();

This provides maximum flexibility while enabling deferred execution for the IQueryable. You can use whatever projection you want, eager load related entities if that is what you want, get a Count or exists check with .Any() etc. Very powerful and results in efficient queries when used properly.