Distinct Linq search query-CodePudding

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?

CodePudding user response：

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
List<int> listOfRegNumbers = _db.Table.Where(t => listOfIds.Any(id => t.Id))
                                      .Select(t => t.regNumber)
                                      .Distinct();
// group the data
var groupedData = _db.Table.Where(x => listOfRegNumbers.Any(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.

CodePudding user response：

You can use GroupBy() to get the count of regNumber,

    var idToBeSearch = 1;
    var result = tableas
        .Where(x => x.Id == idToBeSearch)      //Filter tableas by Id.
        .GroupBy(x => x.regNumber)            //Group all the filtered record by regNumber
        .Select(x => new                      //Create Anonymous objects for each regNumber group
           {Description = x.First().Desc, regNumber=x.Key, TotalCount = x.Count()})  
        .OrderByDescending(x => x.TotalCount)  //Sort data by Count of regNumber
        .First();                              //Store record with highest count of regNumbers

.NET Fiddle

Here your idToBeSearch is 1 and output you will get is

Desc	regNumber	Total number of reg
iphone	9890	3