LINQ with Group By and Having Clause with Min(string)

Question

There are examples with group by - having count or select minimum date with linq on the web but I couldn't find a particular solution for my question. And also I don't have an advanced linq understanding to combine these solutions that I found so far.

I have a SQL query like this:

select client_id 
from my_table /* Column1 : client_id, Column2 : _month */
group by client_id
having min(_month) = '2009-11'

My question is: How can I convert this query into c# linq?. So far I wrote something like this but it doesn't give what I have to have:

var query = dataTable.AsEnumerable() // This is my_table
  .GroupBy(c => c.Field<Int32>("client_id"))
  .Select(g => new
  {
      g.Key,
      minMonth = g.Min(c => c.Field<string>("_month"))
  })
  .Where(d => d.minMonth == "Some String like '2009-11'");   

It actually gives me the result of this SQL which I don't need:

select client_id, min(_month) 
from my_table 
where _month = '2009-11' 
group by client_id

Note: _month is a string which is formatted like YYYY-MM.

Answer 1

Try this:

var results = mytable.GroupBy(x => x.client_id)
                     .Where(x => x.Min(y => DateTime.ParseExact(y._month,"yyyy-MM",null))
                                   == new DateTime(2009,11,1))
                     .Select(x=>x.Key);

The having clause is implemented within the Where in this LINQ statement. After grouping by client_id, we use x.Min to implement the min aggregate function on _month, and then use the ParseExact method to parse as a year-month combination to do the comparison.

From the SQLFiddle provided by OP, there are 4 records returned. A demo using identical data and the above query also returns the same 4 records.