How to only get the ID from EF Linq query-CodePudding

I am attempting to get a list of Order Ids from a EF linq query. The sql query is returning back quickly but I think the EF framework is trying to create the full entity. I only want the ID of the order. It seems that it creates the whole entity and then it parses it out to only the id. Which seems to be a complete waste of resources.

Orders are a complex object that includes lots of child entitys. I dont need anything but the Ids of the orders in the list. Orders are organized into OrderCollection which is a many to many relationship.

The basic query in English is get the order ids in the specified order collection and have a cart date newer then the date specified and only send the specified page (skip and take).

example:

_repo.Orders.Where(o => o.OrderCollection.Any(r => r.Id == RoutingRuleId)).ToList()
                    .Where(o => o.OrderDate >= StartDateTime)
                            .OrderBy(x => x.OrderDate )
                            .Skip(RecordsToSkipCount)
                            .Take(BatchSize).Select(x => new { x.Id }).ToArray();

The sql runs in just 102ms for this in debug mode. But afterwards I see the memory go to up 4GB before failing. The batchsize is only 100. Its like it grabbing everything.

I tried moving the select around but that failed also or gave syntax errors or poor performance in running the sql (SQL taking 16 seconds).

Example

    _repo.Orders.Select(x => new { x.Id, x.OrderCollection, x.OrderDate})
                   .Where(o => o.OrderCollection.Any(r => r.Id == RoutingRuleId)).ToList()
                    .Where(o => o.OrderDate >= StartDateTime)
                            .OrderBy(x => x.OrderDate )
                            .Skip(RecordsToSkipCount)
                            .Take(BatchSize).Select(x => new { x.Id }).ToArray();

The database has millions of records.

CodePudding user response：

What you have is roughly;

List<Order> list = _repo.Orders
    .Where(o => o.OrderCollection.Any(r => r.Id == RoutingRuleId))
    .ToList();

list.Where(o => o.OrderDate >= StartDateTime)
    .OrderBy(x => x.OrderDate )
    .Skip(RecordsToSkipCount)
    .Take(BatchSize)
    .Select(x => new { x.Id })
    .ToArray();

That first .ToList is forcing EF Core to load every order with a matching routing rule into memory. The rest of the expression is then using IEnumerable extension methods to process those results.

I think you want to rearrange that to;

IQueryable<Order> query = _repo.Orders
    .Where(o => o.OrderCollection.Any(r => r.Id == RoutingRuleId)
        && o.OrderDate >= StartDateTime)
    .OrderBy(x => x.OrderDate )
    .Skip(RecordsToSkipCount)
    .Take(BatchSize)
    .Select(x => new { x.Id });

query.ToArray();

Creating an IQueryable doesn't trigger EF Core to execute any SQL. An IQueryable is just a description of the query you would like to run. Then it's the .ToArray method that will finally cause EF Core to compile and execute an sql statement.