UNIQUE columns with count using LINQ

UNIQUE columns with count using LINQ

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 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? 

Solutions/Answers:

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();
 } 

Answer 3:

You need to jint the two tables:

using( var dbContext=...)
{
   var results = from c in dbContext.conflocations
                 from s in dbContext.States.Where(x=>x.Id = c.StateId).DefaultIfEmpty()
                 group new {c, s} by s.StateId into grp
                 select new { 
                              StateId = grp.Key,
                              StateName= grp.Max(x=>x.s.Name),
                              Count = grp.Count()
                            };
   ...
}

Our Awesome Tools

References

The entity type ‘Microsoft.AspNet.Mvc.Rendering.SelectListGroup’ requires a key to be defined

The entity type ‘Microsoft.AspNet.Mvc.Rendering.SelectListGroup’ requires a key to be defined

Not sure what happened but I am getting the following error while attempting to pull up any view in my web app. The code is auto generated by visual studio and I am not getting any errors before building. Using ASP.Net MVC 6, EF7.

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.Core.dll but was not handled in user code
Additional information: The entity type 'Microsoft.AspNet.Mvc.Rendering.SelectListGroup' requires a key to be defined.

Here is the line the code is erroring out on.
public IActionResult Index()
{
    var schoolContext = _context.Schools
                                .Include(s => s.District)
                                .Include(s => s.Location)
                                .Include(s => s.Tier);
    return View(schoolContext.ToList());
}

After some searching I can't figure out exactly what I need to fix. This was working at one point. Not sure what changed.
The view does have a defenition
@model IEnumerable

As requested here is the School model
public class School
{
    //Original Fields
    public int SchoolId { get; set; }

    [Display(Name = "Name")]
    public string SchoolName { get; set; }

    [Display(Name = "Date Added")]
    public DateTime SchoolDateAdded { get; set; }

    [Display(Name = "Last Update")]
    public DateTime SchoolLastUpdate { get; set; }

    [Display(Name="Updated By")]
    public string SchoolUpdatedBy { get; set; }

    //Referance Fields
    public int DistrictId { get; set; }
    public IEnumerable DistrictList { get; set; }
    public int LocationId { get; set; }
    public IEnumerable LocationList { get; set; }
    public int TierId { get; set; }
    public IEnumerable TierList { get; set; }

    //Navigation Property
    public District District { get; set; }
    public Location Location { get; set; }
    public Tier Tier { get; set; }
}

Solutions/Answers:

Answer 1:

These IEnumerable<SelectListItem>s should not be part of your EF model. Remember the single responsibility principle. Keep any UI framework away from your DAL implementation. Use a view model representing a School.

As for the error, from EF’s point of view, School has a 1-n association with SelectListItem, so it tries to make it part of its mapping schema. But each mapped type needs a primary key, which of course isn’t mapped, and EF can’t infer any.

A quick, but dirty, fix would be to exclude the properties from being mapped by the [NotMapped] attribute, but a better segregation of your code is the real remedy.

Our Awesome Tools

References