1

Goal: Group by 2 columns

Error: This causes the error IGrouping does not contain a definition for 'Sub_ID' and no extension method 'SubID' accepting a first argument of type IGrouping

Code:

return db.View.Where(m => m.Mem_ID == Some_MemID && m.Last_Name.ToLower() == Search_MemLastName.ToLower()).GroupBy(t => new { t.Sub_ID, t.Mem_Seq }).OrderBy(t => t.Subs_ID);

I've also tried adding .key but that did not help. Did not find a solution in How to order IGrouping without changing its type? or IGrouping does not contain a definition for

Been looking at this for hours

seesharp
  • 101
  • 1
  • 14

1 Answers1

2

You are sorting the groups, and you have to access the grouped property(ies) through the Key:

.OrderBy(t => t.Key.Subs_ID)

Edit: If you only want to display distinct elements by Sub_ID, add .Select(x => x.First()) to select only the top result per group:

return db.View
    .Where(m => m.Mem_ID == Some_MemID && m.Last_Name.ToLower() == Search_MemLastName.ToLower())
    .GroupBy(t => new { t.Sub_ID, t.Mem_Seq })
    .OrderBy(g => g.Key.Subs_ID)
    .Select(g => g.First());

With GroupBy you transformed an IQueryable<T> into an IGrouping<TKey, T>. The last Select is to reduce the groupings back to single elements.

Xiaoy312
  • 14,292
  • 1
  • 32
  • 44
  • I had tried that as well. It gives the error: Cannot implicitly convert type System.Linq.IOrderedQueryable<<>> to System.Linq.IQueryable<> – seesharp Oct 15 '18 at 20:09
  • The full error is : Cannot implicitly convert type System.Linq.IOrderedQueryable, Program.Data.View>> to System.Linq.IQueryable . An explicit conversion exists – seesharp Oct 15 '18 at 20:16
  • Either (a.) change your method return type to match that if you need to keep the grouped collections – Xiaoy312 Oct 15 '18 at 20:22
  • Or, (b.) add `.Select(x => x.First())` to select only the top result per group, if that is what you need. – Xiaoy312 Oct 15 '18 at 20:23
  • I only want it to display one row on the page if the Sub_ID is the same in the rows from SQL. Otherwise, it should display multiple rows on the page. – seesharp Oct 15 '18 at 20:43
  • In other words, you only want to display distinct elements by `Sub_ID`. So pick option (b.) then. – Xiaoy312 Oct 15 '18 at 20:51
  • Thanks. Not sure if this is what you meant by the select, but this is what ended up working: .GroupBy(t => new { t.Sub_ID, t.Mem_Seq }).Select(g => g.OrderByDescending(p => p.Sub_ID).FirstOrDefault()).AsQueryable().OrderBy(m => m.Sub_ID); – seesharp Oct 15 '18 at 21:13