LINQ to SQL using GROUP BY and COUNT with date (CONVERT issue)
I read this question , but it is not exactly what I was looking for. My problem is that I want 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:
Date Count
02/03/2009 4
24/02/2009 3
25/02/2009 3
26/02/2009 3
and i do 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()}
what in LINQPad does it 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 my understanding is that the difference is the CONVERT part. So how can I convert from Date or Datetime to SmallDate or something similar in LINQ?
thanks
+1
a source to share
1 answer
Try the following:
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 NULL, you will need to use the Value parameter before retrieving the date. You can also omit the date extraction in the WHERE clause depending on the meaning of the end date check (inclusive, then use Date, else omit Date).
+1
a source to share