Understanding EF core subquery

Question

I have the following linq statements which I am hoping to cut down from multiple round trips to my database to one using subquery.

This is my code before refactoring:

IQueryable<Customers> customers= this.BaseQuery.Where(o => o.EmailAddress == emailAddress);
            int totalCustomersCount = await customers.CountAsync();
            int totalDisabledCustomersCount = await customers.Where(o => CustomerState.Disabled == o.CurrentState).CountAsync();
            int totalCancelledCustomersCount = await customers.Where(o => CustomerState.Cancelled == o.CurrentState).CountAsync();
            int totalExpiredCustomersCount = await customers.Where(o => CustomerState.Archived == o.CurrentState).CountAsync();
            int totalPremiumCustomerCount = await customers.Where(o => CustomerState.Premium== o.CurrentState).CountAsync();

            CustomerStatistics customerStatistics = new CustomerStatistics()
            {
                TotalCustomersCount = totalCustomersCount,
                TotalDisabledCustomersCount = totalDisabledCustomersCount,
                TotalCancelledCustomersCount = totalCancelledCustomersCount,
                TotalExpiredCustomersCount= totalExpiredCustomersCount,
                TotalPremiumCustomerCount= totalPremiumCustomerCount,
            };

and I have reworked this code to look like this

IQueryable<Customers>? query = this.Context.Customers.Where(o => o.EmailAddress == emailAddress);
            IQueryable<CustomerStatistics >? myQuery= query.Select(o => new CustomerStatistics()
            {
                TotalCustomersCount = query.Count(),
                TotalDisabledCustomersCount = query.Where(o => o.CurrentState == CustomerState.Disabled).Count(),
                TotalCancelledCustomersCount = query.Where(o => o.CurrentState == CustomerState.Cancelled).Count(),
                TotalExpiredCustomersCount= query.Where(o => o.CurrentState == CustomerState.Archived).Count(),
                TotalPremiumCustomerCount= query.Where(o => o.CurrentState == CustomerState.Premium).Count(),
            });

Is this along the right lines? To me, it still looks like it will be makign round trips to the database to get each count.

Update to the example query:

IQueryable<Order> orders = this.BaseQuery.Where(o => o.CustomerContactId == customerContactId);
            int totalOrderCount = await orders.CountAsync();
            int totalInProgressOrderCount = await orders.Where(o => OrderState.Fulfilment == o.CurrentState).CountAsync();
            int totalCancelledOrderCount = await orders.Where(o => OrderState.Cancelled == o.CurrentState).CountAsync();
            int totalExpiredOrderCount = await orders.Where(o => OrderState.Archived == o.CurrentState).CountAsync();
            int totalQuoteCount = await orders.Where(o => OrderState.Initial == o.CurrentState || OrderState.QuoteIssued == o.CurrentState || OrderState.ReadyToPlace == o.CurrentState || OrderState.CheckGeometry == o.CurrentState).CountAsync();
            int totalIssuedOrderCount = await orders.Where(o => OrderState.QuoteIssued == o.CurrentState).CountAsync();

            CustomerContactOrderStatistics customerContactOrderStatistics = new CustomerContactOrderStatistics()
            {
                TotalOrderCount = totalOrderCount,
                TotalInProgressOrderCount = totalInProgressOrderCount,
                TotalCancelledOrderCount = totalCancelledOrderCount,
                TotalExpiredOrderCount = totalExpiredOrderCount,
                TotalQuoteCount = totalQuoteCount,
                TotalIssuedOrderCount = totalIssuedOrderCount,
            };

This seems an odd query pattern.. Is emailaddress unique? Or is it e.g. the "email address of the account manager, and you want to know how many of the account manager's customers are in Disabled status" ? — Caius Jard, Feb 02 '22 at 11:26
So really you just want to know what status a customer is in as a spread out/pivoted list — Caius Jard, Feb 02 '22 at 11:31
@CaiusJard yes, and I'm using this more as a learning/working example rather than the actual query itself. Sorry, maybe I should have ben more specific in the example I used. — f_olivere, Feb 02 '22 at 11:32
I don't think I'd bother with anything so complex; just download the 1 customer and cross their status with all the possible statuses to find the 1 amidst the sea of 0s that you're after — Caius Jard, Feb 02 '22 at 11:35
@f_olivere in SQL you'd use a simple query with `GROUP BY` to calculate statistics. There's no need for subqueries. You can do the same thing in LINQ. Unless you want something different from what was asked in the question — Panagiotis Kanavos, Feb 02 '22 at 11:35
@PanagiotisKanavos but here is a group of size 1.. doesn't even need a group by.. If there were more than 1 customer then I'd say group by the status but... — Caius Jard, Feb 02 '22 at 11:36
@CaiusJard the way this code is written it's easy to miss parts. That's definitely not a job for EF. There's no query to construct here, just an object to fill from the query's results — Panagiotis Kanavos, Feb 02 '22 at 11:37
Indeed; this is a most bizarre approach. @IvanStoev I don't think this is a duplicate of that, because at its core this problem doesn't even need a grouping.. It's like an XY, the X is maybe a duplicate, but the Y isn't — Caius Jard, Feb 02 '22 at 11:38
@CaiusJard I have added an updated query to the end of my question. Would this make a difference to the answer? I have reworded in terms of customer orders rather than customers. — f_olivere, Feb 02 '22 at 11:43
It all really depends on what's a CustomerContactId? Imagine you ran `this.BaseQuery.Where(o => o.CustomerContactId == customerContactId).ToList()` - how many records are in the list? Do they have different statuses? e.g. are there 100 records, 10 are InProgress, 20 are Fulfilled, 30 are Canceleld and 40 are Expired? (and you want an object result like `{Total=100,InProg=10,...}` — Caius Jard, Feb 02 '22 at 11:44
@CaiusJard yes, I would expect that a list of 100 would not be unreasonable for our case. Does the number of records returned change the solution? Is there not one solution which will suit all cases in terms of efficency etc? — f_olivere, Feb 02 '22 at 11:49
Number of records doesn't affect what I'd recommend but I wouldn't recommend the approach that Syvatoslav or Ivan gave - syvatoslav has keyed off your approach and Ivan off Syvatoslavs and neither has really checked the the overall problem being investigated, in my opinion. There isn't a need for the multi aggregate trick here because there aren't multiple things being aggregated. You could just `GroupBy(c => c.OrderState)` and get from that a Dictionary of all the orderstates and their counts. The total is the sum of the values of each orderstate, which C# can quickly do — Caius Jard, Feb 02 '22 at 11:53
I'm now somewhat on the fence as to whether to reopen the question so an alternative approach can be proposed (ultimately doubtless another duplicate, actually - "pivot on x in linq" will have been asked a hundred times before) or leave it alone because the proposed conditional agg will work, but is overcomplicated for the use case — Caius Jard, Feb 02 '22 at 12:00
@CaiusJard Actually it is if you want single server side SQL query (even though it will contain multiple sub queries). And group by constant is definitely needed, even in EF Core. As in already posted answer. — Ivan Stoev, Feb 02 '22 at 12:08
@Ivan actually I think here `GroupBy(c => c.CurrentState)` and then inspect the key and count will be fine.. If we were doing some more involved conditional agg I would agree with you, but this particular case is answerable with a basic `select x, count(*) from t group by x` with C# doing the pivoting, not the DB. — Caius Jard, Feb 02 '22 at 12:31
@CaiusJard I see. What's the close reason for XY problem then? :) — Ivan Stoev, Feb 02 '22 at 12:45
Oh.. Erm.. In this case I'd perhaps add some more duplicates to the close, with a comment pointer as to why.. For example one that does a `GroupBy(..).ToLookup(key)` then builds the `Statistics{ Total = lookup.Count(), Expired = lookup[Expired].Count(), Cancelled = lookup[Cancelled].Count()` or perhaps the statistics class would have a dictionary driving the properties, and they're just `public int Expired{ get => _dict.GetValueOrDefault(Expired) }` and we can ToDictionary() the group and set it as the backing data store for the props.. — Caius Jard, Feb 02 '22 at 13:20

Svyatoslav Danyliv · Answer 1 · 2022-02-02T11:22:41.120

You can do grouping by constant. It is special case which is handled by EF to make aggregation query:

vra query = this.Context.Customers
    .Where(o => o.EmailAddress == emailAddress);

var statistics = = query.GroupBy(x => 1)
    .Select(g => new CustomerStatistics()
    {
        TotalCustomersCount = g.Count(),
        TotalDisabledCustomersCount = g.Count(o => o.CurrentState == CustomerState.Disabled),
        TotalCancelledCustomersCount = g.Count(o => o.CurrentState == CustomerState.Cancelled),
        TotalExpiredCustomersCount = g.Count(o => o.CurrentState == CustomerState.Archived),
        TotalPremiumCustomerCount = g.Count(o => o.CurrentState == CustomerState.Premium),
    });

For EF Core versions which do not support conditional Count, it can be emulated by Sum

var statistics = = query.GroupBy(x => 1)
    .Select(g => new CustomerStatistics()
    {
        TotalCustomersCount = g.Count(),
        TotalDisabledCustomersCount = g.Sum(o => o.CurrentState == CustomerState.Disabled ? 1 : 0),
        TotalCancelledCustomersCount = g.Sum(o => o.CurrentState == CustomerState.Cancelled ? 1 : 0),
        TotalExpiredCustomersCount= g.Sum(o => o.CurrentState == CustomerState.Archived ? 1 : 0),
        TotalPremiumCustomerCount= g.Sum(o => o.CurrentState == CustomerState.Premium ? 1 : 0),
    });

Understanding EF core subquery

1 Answers1