LINQ to SQL using GROUP BY and COUNT with Date (CONVERT problem)

Question

Hi. I have read this question but it's not quite what I was looking for. My problem is I whant to do this:

SELECT CONVERT(varchar(10), m.CreateDate, 103) as [Date] , count(*) as [Count] FROM MEMBERS as m
WHERE m.CreateDate >= '24/01/2008' and m.CreateDate <= '26/06/2009' 
Group by CONVERT(varchar(10), m.CreateDate, 103)

result is:

 Date     Count 

 02/03/2009 4  
 24/02/2009 3   
 25/02/2009 3  
 26/02/2009 3

and I'm doing this:

From m In Me.Members _
Where m.CreateDate >= "22/02/2008" And m.CreateDate <= "22/05/2009" _
Group m By m.CreateDate Into g = Group _
Select New With {CreateDate, .Count = g.Count()}

that acoording to LINQPad does this in SQL:

SELECT COUNT(*) AS [Count], [t0].[CreateDate]
FROM [Members] AS [t0]
WHERE ([t0].[CreateDate] >= @p0) AND ([t0].[CreateDate] <= @p1)
GROUP BY [t0].[CreateDate]

and the result is:

      Date          Count
24/02/2009 00:00:00  1
24/02/2009 07:07:10  1
24/02/2009 12:24:10  1
25/02/2009 03:43:05  1 
25/02/2009 03:48:36  1 
25/02/2009 04:25:11  1 
26/02/2009 01:51:24  1 
26/02/2009 09:54:55  1 
26/02/2009 09:55:31  1 
02/03/2009 05:29:22  1 
02/03/2009 05:45:50  1 
02/03/2009 06:15:31  1 
02/03/2009 06:59:07  1

So I understant that the diference is the CONVERT part. So How can I convert from Date or Datetime to SmallDate or something like that in LINQ ?

Thanks

Answer 1

It seems that this solves something:

From m In Me.Members _
Where m.CreateDate >= "22/02/2008" And m.CreateDate <= "22/05/2009" _
Group m By dat = m.CreateDate.Value.Date Into g = Group _
Select New With {dat, .Count = g.Count()}

24/02/2009 00:00:00 3
25/02/2009 00:00:00 3
26/02/2009 00:00:00 3
02/03/2009 00:00:00 4

But its still not quite the result that I wanted:

Date     Count  
02/03/2009 4   
24/02/2009 3    
25/02/2009 3   
26/02/2009 3

Answer 2

Try this:

From m In Me.Members _
Where m.CreateDate.Date >= new DateTime( 2009, 2, 22 ) And m.CreateDate.Date <= new DateTime( 2009, 5, 22) _
Group m By day = m.CreateDate.Date Into g = Group _
Select New With { .Date = day.ToString( "dd/MM/yyyy" ), .Count = g.Count()}

If CreateDate is nullable, then you'll need to use the Value parameter before extracting the Date. You may also be able to omit the extraction of the Date on the WHERE query depending on the sense of the end date check (is it inclusive, then use Date, else omit Date).