How to write a count and group by month query with linq

Question

This is my first C# application in ASP.NET Core using Entity Framework Core and I'm trying to write the following MySQL query in Linq:

SELECT districtid, COUNT(id), MONTH(registerDate)
FROM StreetCrimes
GROUP BY districtid, MONTH(registerDate)

The problem I'm facing is writing the linq code. I've tried the following to get close to the answer:

var StreetCrimes = this.database.StreetCrimes
        .Select(s => s.district)
        .Count(s => s.id > 0)
        .GroupBy(s => s.district)
        .ThenBy(s => s.registerDate);

Any help making this possible would be appreciated.

My StreetCrime class:

public class StreetCrime
{
    public int id { get; set; }

    public string caseNumber { get; set; }
    public District district { get; set; }
    public DateTime registerDate { get; set; }
    public TimeSpan crimeTime { get; set; }
}

Possible duplicate of [LINQ with groupby and count](https://stackoverflow.com/questions/7285714/linq-with-groupby-and-count) — devlin carnate, Mar 29 '18 at 16:13
@zackraiyan I'm trying to count the amount of crimes commited in each month grouped by district id — Debreker, Mar 29 '18 at 17:04
@Debreker,take a look at my solution , i've given you three options,you can go with any :) — Software Dev, Mar 29 '18 at 17:12

score 3 · Answer 1 · answered Mar 29 '18 at 16:29

District needs to inherit IEqualityComparer for the GroupBy to work. The linq should look like this :

       var StreetCrimes = this.database.StreetCrimes
       .Where(x => x.id > 0)
       .GroupBy(s => new {district = s.district, date = s.registerDate})
       .Select(x => new { count = x.Count(), district = x.Key.district, date = x.Key.date})
       .ToList();

score 3 · Accepted Answer · answered Mar 29 '18 at 17:59

Thanks to @jdweng I got closer to my answer. However I Needed the total amount each month.

        var StreetCrimes = this.database.StreetCrimes
        .Where(x => x.id > 0)
        .GroupBy(s => new {district = s.district, date = s.registerDate.Month})
        .Select(x => new { count = x.Count(), district = x.Key.district, date = x.Key.date})
        .ToList();

score 0 · Answer 3 · answered Mar 29 '18 at 17:08

You can go with LINQ

  var StreetCrimes = this.database.StreetCrimes
   .Where(x => x.id > 0)
   .GroupBy(s => new {district = s.district, date = s.registerDate})
   .Select(x => new { count = x.Count(), district = x.Key.district, date = x.Key.date})
   .ToList();

but you can also go with a DataTable.

SqlCommand cmd = new SqlCommand("SELECT districtid,COUNT(id),MONTH(registerDate) FROM StreetCrimes GROUP BY districtid,MONTH(registerDate)",connectionString)

DataTable dt = new DataTable();
SqlDataAdapter ada = new SqlDataAdapter(cmd);
ada.Fill(dt)
int crimeCount = dt.Rows.Count();

or maybe two lines will do :

 SqlCommand cmd = new SqlCommand("SELECT districtid,     COUNT(id),MONTH(registerDate) FROM StreetCrimes GROUP BY districtid,MONTH(registerDate)",connectionString)

 int crimeCount = CInt(cmd.ExecuteScalar());

score 0 · Answer 4 · answered Mar 29 '18 at 20:00

This should work:

var StreetCrimes = this.database.StreetCrimes
       .Where(x => x.id > 0)
       .GroupBy(s => new {district = s.district, month = s.registerDate.Month})
       .Select(x => new { count = x.Count(), district = x.Key.district, month = x.Key.month})
       .ToList();

How to write a count and group by month query with linq

4 Answers4