UNIQUE columns with count using LINQ

Question

I have a table with the following columns

Id
Address
City
Date
maxSeat
StateId [Foreign key with table State with columns Id,Name] 

I want to write a LINQ query to get the List of unique StateId and its count

For example

State1 5 rows

State2 3 rows

State3 1 rows

State4 5 rows

List<int> uniqStates = dbContext.conflocations.Select(item => item.StateId)
                                              .Distinct().ToList();

This returns unique list of stateId only. How can I get associated count as well along with State Name using LINQ?

Answer 1

You need GroupBy:-

var uniqStates = dbContext.conflocations.GroupBy(item => item.StateId)
                          .Select(x => new 
                                  {
                                      StateId = x.Key,
                                      Count = x.Count()
                                  });

Answer 2

You can do that using the GroupBy method:

var uniqStates = dbContext.conflocations.GroupBy(item => item.StateId).Select(g=>new {StateId=g.Key,Count=g.Count()}).ToList();

Or using query syntax, you can also do:

 var uniqStates= from conf in dbContext.conflocations
                 group conf by conf.StateId into g
                 select new {StateId=g.Key,
                             Count=g.Count()
                            }; 

Now to get the state's name, if you have a navigation property of type State in your Conflocation entity, then you can do something like this:

var uniqStates= from conf in dbContext.conflocations
                 group conf by conf.StateId into g
                 select new {StateId=g.Key,
                             Name=g.FirstOrDefault().State.Name
                             Count=g.Count()
                            }; 

Update

If your StateWiseVenues class has the same property types of the anonymous type where this query is projecting the result, then you can do this:

var uniqStates= from conf in dbContext.conflocations
                group conf by conf.StateId into g
                select new StateWiseVenues {StateId=g.Key,
                                            Name=g.FirstOrDefault().State.Name
                                            Count=g.Count()
                                           }; 
 if(uniqStates !=null) 
 { 
   state_venues = uniqStates.ToList();
 }