OData with EFCore - why no WHERE clause?

Question

I haven't messed with OData in a while, but I remember it being really useful. So I've gone for a .NetCore 3.1 EFCore + OData architecture for my API. With a view to make it fully generic etc. etc.

Doing a little test, I can get the expected results from my browser: e.g.

https://localhost:44310/things?someidfield=44

Cool I get back the JSON I was expecting! But it's sooo slow, why? Looking at the SQL (Profiler) I can see it has no WHERE clause, it's getting everything from the database and filtering it in memory, on half a million records?

What am I missing here? I've tried a number of ways to write the GET method. Starting with not passing any queryOptions (which worked! but same result underneath) and then the below where I explicitly apply the options to by EFCore entity.

        [HttpGet]
        [EnableQuery]
        public async Task<IEnumerable<thing>> GetThingsAsync(ODataQueryOptions<thing> queryOptions)
        {
           return await queryOptions.ApplyTo(DB.thing).Cast<thing>().ToListAsync();
        }

Answer 1

The result set is being loaded in memory because you're calling ToListAsync() and returning an IEnumerable.

If your GetThingsAsync method returns an IQueryable<T> (instead of IEnumerable<T>), then the query will be applied to the database and only the filtered data will be fetched.

If DB.thing is an EFCore DbSet (which implements IQueryable<T>), then you can simplify your method as

[HttpGet]
[EnableQuery]
public Task GetThingsAsync()
{
  return DB.thing;
}

Furthermore, like some in the comment already mentioned, the correct syntax for filtering in your case would be ?$filter=someidfield eq 44