How GroupBy can be used in LINQ?

Question

I have a following code:

var sql = db.Accounts.AsNoTracking()
                  .Join(db.Customers.AsNoTracking(),
                   d => d.AccountNr, c => c.CustNr,
                   (d, c) => new {Accounts = d, Customers = c })
                  .GroupBy(g => g.Accounts.AccountNr)
                  .Where(w => w.Accounts.Date == null)
                                  
                  .Select(s => new
                       {
                        Company = s.Customers.CompName,
                        TWQ = s.Customers.TWQ,
                        AccountNr = s.Accounts.AccountNr,
                        DocDate = s.Accounts.DocumentDate,
                        Income = s.Customers.Income
                        })
                  .OrderBy(o => o.DocDate);

The issue is that c# underlines the whole WHERE part with an alert saying that: Element IGrouping <string,> has no definition of Accounts and extension method of Accounts can not be found

I don't know where the problem lies. I also tried to use GroupBy in model (instead of using it in the code above) but got the some problem:

var model = (from ss in sql   // here I refer to sql outcome I got from the code above
                   .GroupBy(g => g.AccountNr)
                   .Skip(page * 15 - 15)  
                   .Take(15)
                   .AsEnumerable()

                   select new DocumentsModel
                       {
                        Company = s.Customers.CompName,
                        TWQ = s.Customers.TWQ,
                        AccountNr = s.Accounts.AccountNr,
                        DocDate = s.Accounts.DocumentDate,
                        Income = s.Customers.Income
                        }).ToList();

I don't understand how this is a grouping. You seem to be expecting the same format out as you put in. A group is a key matched to one or more records that fall within that group. Think of it like creating a box for each AccountNr and then putting all records related to that account into the relevant account box. Anyway, please edit your question to include your model objects and what you actually want out of it. — ProgrammingLlama, Nov 20 '20 at 15:51
I want to use 'group by' just like it's used in sql. Simple gruping by AccountNr and that's it. Look here, I followed this simple code: https://csharp.net-tutorials.com/linq/grouping-data-the-groupby-method/ — mustafa00, Nov 20 '20 at 16:01
But you're not using it like it's used in SQL. In SQL you have to use aggregations such as `MAX()`, `MIN()`, `AVG()`, etc. to get your data out. You're not doing that here. Note in your example you have a `foreach` loop and a nested `foreach` loop. The outer one loops through the groups, the inner one loops through the items inside the group. — ProgrammingLlama, Nov 20 '20 at 16:04
Take a look at [this question](https://stackoverflow.com/questions/7325278/group-by-in-linq). — ProgrammingLlama, Nov 20 '20 at 16:06
Looks like a typical beginner question. As I see, the where is wrong, it should be either before the GroupBy (to filter out Accounts with Date==null - notice that DateTime is a struct and can't be null!), or be adapted to the IGrouping output (Key and IEnumerable of values). Also, the data structure in the beginning is not really clear, what is in Accounts. — Erik Hart, Nov 20 '20 at 16:09

score 0 · Accepted Answer · answered Nov 20 '20 at 16:28

At first I would like to say that my English isn't that good.

If you execute an GroupBy, you'll get collection of elements where each element represents a projection over a group and its key.

That's why I execute SelectMany afterwards to work with the model in a normal way.

db.Accounts
    .AsNoTracking()
      .Join
        (
          inner: db.Customers.AsNoTracking(),
          outerKeySelector: x => x.AccountNr, 
          innerKeySelector: x => x.CustNr,
          resultSelector: (Accounts, Customers) => new 
          {
            Accounts, Customers
          }
        )
      .Where
        (
          predicate: x => x.Accounts.Date == null
        )
      .GroupBy
        (
          keySelector: x => x.Accounts.AccountNr
        )
      .SelectMany
        (
          selector: x => x
        )
      .Select
        (
          selector: x => new
            {
              Company = x.Customers.CompName,
              TWQ = x.Customers.TWQ,
              AccountNr = x.Accounts.AccountNr,
              DocDate = x.Accounts.DocumentDate,
              Income = x.Customers.Income
            }
        )

How GroupBy can be used in LINQ?

1 Answers1