I often speak very highly of LINQ and also LINQPad.  This morning, I was struggling with some sql that would let me do a count by DateTime while stripping out the time portion.  That is, I just want to know how many entries are in the table for each Date (regardless of what time).  I tried lots of solutions I got from search, and they all gave not correct results, usually involving Casting and other non fun sql programming constructs.

It occurred to me I could do it in LINQ, then grab out the SQL it generated.  The tool?  LINQPad of course.  So, here is what I typed into LINQPad:

(from data in Attendees
let dateNoTime = 
    new DateTime
     (data.CreationDate.Value.Year,
      data.CreationDate.Value.Month,
      data.CreationDate.Value.Day)
orderby dateNoTime
group data by dateNoTime into g
where g.Count() > 0
select new 
    {
    g.Key,
    Number_Registered = g.Count() 
    }).OrderByDescending(f=>f.Key)

Then, After pasting that into LINQPad, choosing the SQL tab on the bottom, it gives me this:

SELECT [t2].[value3] AS [Key], [t2].[value2] AS [Number_Registered]
FROM (
SELECT COUNT(*) AS [value], COUNT(*) AS [value2], [t1].[value] 
          AS [value3]
FROM (
    SELECT CONVERT
         (DATETIME, CONVERT(NCHAR(2), DATEPART(Month, [t0].[CreationDate])) 
           + ('/' + (CONVERT(NCHAR(2), DATEPART(Day, [t0].[CreationDate])) +
              ('/' + CONVERT(NCHAR(4), DATEPART(Year, [t0].[CreationDate]))))), 101) 
                    AS [value]
        FROM [Attendees] AS [t0]
        ) AS [t1]
    GROUP BY [t1].[value]
    ) AS [t2]
WHERE [t2].[value] > @p0
ORDER BY [t2].[value3] DESC

And, the nice screen shot of LINQPad

And, it works perfect!  Even the TSql generated looks pretty nice.  Group By, Convert, DatePart and everything. Somehow, I feel like I’ve cheated, but I’m good with that in this case.

Hope this helps!

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me: