Distinct Linq search query

Question

I have a table say TABLEA

ID	Description	regNumber
001	iphone	9890
002	iphone pro max	9890
003	iphone se	9890
004	iphone	7847
008	samsung	9432
009	samsung s10	9432
010	orange	34

Problem Get all the regNumber that matches and display as a consolidated data (single data)

Acceptable solution For example if someone search for an id with '001' get all the SAME regNumber like this

Desc	regNumber	Total number of reg
iphone	9890	3

Part Solution I have listed all the regNumber like this:

var listOfRegs = _db.Table.Select(x=>x.regNumber).toList();

I now need to find a way to iterate this list to group how many times a regNumber matches which is the problem I'm having but something like this:

foreach(var i in listOfRegs)
{
  //find a way to group/list distinct regNumbers which is why I'm asking?

}

It's easier if the query was to search for the regNumber which you can pass through the action something like this:

var listOfRegs = _db.TABLE.where (a.regNumber== 
model.regNumberSearched).Select(x=>x.regNumber).Distinct().ToList();

//this works fine 

But the problem is that I'm not passing that regNumber Instead just got a list of regNumber that matches 'ID' from the table and as u can see from the table ID is always unique so passing model.id (the id being pass) will always only output a unique regNumber rather than counting all matches. so how do I proceed please?

Answer 1

You can use GroupBy() and project the result into an anonymous type:

var groupedData = _db.Table.GroupBy(x => x.regNumber)  // group by regNumber
                           .Select(g => new 
                           { 
                               Desc = g.First().Desc,  // use the first Desc
                               RegNumber = g.Key,      // RegNumber is the group Key 
                               Count = g.Count()       // number of elements in group
                           })
                           .ToList();

This will give you output similar to:

Desc	RegNumber	Count
iphone	9890	3
samsung	9432	2
orange	34	1

---

If you have a list of Table IDs to filter on, say listOfIds, you can filter that first to get all associated regNumber, then use the list of regNumbers to group the data:

List<int> listOfIds = // your list of Table Ids from the DB
// get your list of regNumbers based on table IDs
var listOfRegNumbers = _db.Table.Where(t => listOfIds.Any(id => id == t.Id))
                                .Select(t => t.regNumber)
                                .Distinct();
// group the data
var groupedData = _db.Table.Where(x => listOfRegNumbers.Any(id => id == x.regNumber))
                           .GroupBy(x => x.regNumber)  // group by regNumber
                           .Select(g => new 
                           { 
                               Desc = g.First().Desc,  // use the first Desc
                               RegNumber = g.Key,      // RegNumber is the group Key 
                               Count = g.Count()       // number of elements in group
                           })
                           .ToList();

However, I not sure that's required for your case. Using List<string> would work above too, depending on your data type of the column.

Here is an online demo (adapted from Prasad Telkikar's - thank you!).