'Client side GroupBy is not supported.'

Question

I am trying to run GroupBy() command in northwind db this is my code

using(var ctx = new TempContext())
{
    var customer = (from s in ctx.Customers
                    group s by s.LastName into custByLN
                    select custByLN);
    foreach(var val in customer)
    {
        Console.WriteLine(val.Key);
        {
            foreach(var element in val)
            {
                Console.WriteLine(element.LastName);
            }
        }
    }
}

it gives System.InvalidOperationException: 'Client side GroupBy is not supported'

I guess this is turning into a group join which is a LINQ thing. — Tanveer Badar, Aug 26 '20 at 05:43

score 0 · Answer 1 · answered Aug 26 '20 at 06:44

Apparently you are trying to make groups of Customers with the same value for LastName. Some database management systems don't support GroupBy, although this is very rare, as Grouping is a very common database action.

To see if your database management system supports grouping, try the GroupBy using method syntax. End with ToList, to execute the GroupBy:

var customerGroupsWithSameLastName = dbContext.Customers.GroupBy(

    // Parameter KeySelector: make groups of Customers with same value for LastName:
    customer => customer.LastName)
.ToList();

If this works, the DBMS that your DbContext communicates with accepts GroupBy.

The result is a List of groups. Every Group object implements IGrouping<string, Customer>, which means that every Group has a Key: the common LastName of all Customers in this group. The group IS (not HAS) a sequence of all Customers that have this LastName.

By the way: a more useful overload of GroupBy has an extra parameter: resultSelector. With the resultSelector you can influence the output: it is not a sequence of IGrouping objects, but a sequence of objects that you specify with a function.

This function has two input parameters: the common LastName, and all Customers with this LastName value. The return value of this function is one of the elements of your output sequence:

var result = dbContext.Customers.GroupBy(
    customer => customer.LastName,

    // parameter resultSelector: take the lastName and all Customers with this LastName
    // to make one new:
    (lastName, customersWithThisLastName) => new
    {
        LastName = lastName,
        Count = customersWithThisLastName.Count(),

        FirstNames = customersWithThisLastName.Select(customer => customer.FirstName)
                                              .ToList(),
        ... // etc
    })
    .ToList();

Back to your question

If the above code showed you that the function is not supported by your DBMS, you can let your local process do the grouping:

var result = dbContext.Customer
    // if possible: limit the number of customers that you fetch
    .Where(customer => ...)

    // if possible: limit the customer properties that you fetch
    .Select(customer => new {...})

    // Transfer the remaining data to your local process:
    .AsEnumerable()

    // Now your local process can do the GroupBy:
    .GroupBy(customer => customer.LastName)
    .ToList();

Since you selected the complete Customer, all Customer data would have been transferred anyway, so it is not a big loss if you let your local process do the GroupBy, apart maybe that the DBMS is probably more optimized to do grouping faster than your local process.

Warning: Database management systems are extremely optimized in selecting data. One of the slower parts of a database query is the transfer of the selected data from the DBMS to your local process. So if you have to use AsEnumerable(), you should realize that you will transfer all data that is selected until now. Make sure that you don't transfer anything that you won't use anyhow after the AsEnumerable(); so if you are only interested in the FirstName and LastName, don't transfer primary keys, foreign keys, addresses, etc. Let your DBMS do the Where and Select`

FirstNames = customersWithThisLastName.Select(customer => customer.FirstName) .ToList(), this statement doesent seem to be working when i see the value of customers through debugging it has lastname=count = 3 these types of value it doesnt generate list of names — karnik, Aug 29 '20 at 05:33
Nice explanation, but surely you have the problem backwards! It says "Client-side" grouping isn't supported. Not that the DBMS can't do it. I know my DBMSs, SQL Server and Postgres, both can, but I'm still getting this error, which would appear to be because I've done something in my query to force it to do the grouping on the client. — Auspex, Oct 04 '21 at 12:44

'Client side GroupBy is not supported.'

1 Answers1

Back to your question