5

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.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Baz Guvenkaya
  • 1,482
  • 3
  • 17
  • 26

1 Answers1

6

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.

shree.pat18
  • 21,449
  • 3
  • 43
  • 63
  • Why to parse left datetime from string rather than construct it directly? – abatishchev Jul 21 '14 at 03:27
  • @abatishchev Fair enough, can be done directly as well. I will update my answer to include that. `_month` is a string from what I understand, so that has to be parsed. – shree.pat18 Jul 21 '14 at 03:28
  • Thanks for the quick reply and explanation. Although it looks promising, after compiling this solution gives the same result with my own solution. What I am expecting is the result of my first SQL snippet with **having min(_month) = '2009-11'** clause. – Baz Guvenkaya Jul 21 '14 at 04:31
  • And it doesn't give you that? What do you get once you run this query? Here is a demo of the above: http://rextester.com/FZYOEQ94595 and here is a demo of your SQL query with identical data: http://rextester.com/JKE76750 – shree.pat18 Jul 21 '14 at 04:31
  • You may try with the actual data to see the actual difference between those two SQL snippets. https://www.dropbox.com/s/l1ju4cctrktkty3/dcbb4d98-9224-46ca-858b-4dd16013b36f.txt – Baz Guvenkaya Jul 21 '14 at 04:45
  • Can you briefly explain what goes wrong? I can't download anything on my work machine so I can't check your data. Or else, maybe set up an SQL Fiddle with representative data so I can get a better idea. – shree.pat18 Jul 21 '14 at 04:48
  • You may observe the 1 record difference between those SQL snippets. With real data the difference is more than 800 records. http://sqlfiddle.com/#!6/2f81f/3 – Baz Guvenkaya Jul 21 '14 at 05:55
  • I think this is a big question mark for LINQ developers. They didn't implement 'having min(column)' with the same logic how SQL works. Even omitting 'having' and replacing with 'where not exists' shows the same wrong output. This is a main bug and should be reported. I solved this problem by replacing the LINQ query with a SQL query. Thanks everyone. – Baz Guvenkaya Jul 22 '14 at 02:03