How to Get GroupBy and Count to generate logical SQL?

Hi Guys,

I am getting some very strange SQL results when trying to do simple group by with count, again I am unsure if I am doing anything wrong or if I need to change what I am doing or something needs to be fixed.

Basically I am expecting SQL like this...

SELECT
  CONVERT(DATE, LoginLogs.CreatedOn) AS [LoginLogs.CreatedOn_CONVERT],
  LoginLogs.Success AS [Success],
  COUNT(1) AS [Count]
FROM
  LoginLogs LoginLogs
WHERE
  LoginLogs.CreatedOn > '16/05/2015 00:00:00'
GROUP BY
  CONVERT(DATE, LoginLogs.CreatedOn),
  LoginLogs.Success

This is the linq I am using...

UoW.LoginLogs.Where(log=>log.CreatedOn > DateTime.Today.AddDays(-30)).GroupBy(log=> new { log.CreatedOn.Date, log.Success }).Select (group => new { group.Key.Date, group.Key.Success, Count = group.Count() })

But this is returning an initial SQL, and then an extra SQL for everysingle row returned...

SELECT
  g1.[LoginLogs.CreatedOn_CONVERT],
  g1.Success
FROM
  (
    SELECT
      CONVERT(DATE, LoginLogs.CreatedOn) AS [LoginLogs.CreatedOn_CONVERT],
      LoginLogs.Success AS [Success]
    FROM
      LoginLogs LoginLogs
    WHERE
      LoginLogs.CreatedOn > '16/05/2015 00:00:00'
    GROUP BY
      CONVERT(DATE, LoginLogs.CreatedOn),
      LoginLogs.Success
  )
  g1

--> Time:   53 ms


SELECT
  LoginLogs.Id AS [LoginLogs.Id],
  LoginLogs.CreatedOn AS [LoginLogs.CreatedOn],
  LoginLogs.IpAddress AS [LoginLogs.IpAddress],
  LoginLogs.Success AS [LoginLogs.Success],
  LoginLogs.UserAccountId AS [LoginLogs.UserAccountId],
  LoginLogs.UserName AS [LoginLogs.UserName]
FROM
  LoginLogs
WHERE
  ((LoginLogs.CreatedOn > '16/05/2015 00:00:00' AND CONVERT(DATE, LoginLogs.CreatedOn) = '16/05/2015 00:00:00') AND LoginLogs.Success = True)

--> Time:   56 ms


SELECT
  LoginLogs.Id AS [LoginLogs.Id],
  LoginLogs.CreatedOn AS [LoginLogs.CreatedOn],
  LoginLogs.IpAddress AS [LoginLogs.IpAddress],
  LoginLogs.Success AS [LoginLogs.Success],
  LoginLogs.UserAccountId AS [LoginLogs.UserAccountId],
  LoginLogs.UserName AS [LoginLogs.UserName]
FROM
  LoginLogs
WHERE
  ((LoginLogs.CreatedOn > '16/05/2015 00:00:00' AND CONVERT(DATE, LoginLogs.CreatedOn) = '17/05/2015 00:00:00') AND LoginLogs.Success = True)

--> Time:   58 ms

And so on lots more of this SQL

If I take out the .Date method, so the Linq is like this...

LoginLogs.Where(log=>log.CreatedOn > DateTime.Today.AddDays(-30)).GroupBy(log=> new { Date = log.CreatedOn, log.Success }).Select (group => new { group.Key.Date, group.Key.Success, Count = group.Count() })

I get two SQL statements being run, like this...

SELECT
  g1.CreatedOn,
  g1.Success
FROM
  (
    SELECT
      LoginLogs.CreatedOn AS [CreatedOn],
      LoginLogs.Success AS [Success]
    FROM
      LoginLogs LoginLogs
    WHERE
      LoginLogs.CreatedOn > '16/05/2015 00:00:00'
    GROUP BY
      LoginLogs.CreatedOn,
      LoginLogs.Success
  )
  g1

--> Time:   53 ms


SELECT
  LoginLogs.Id AS [LoginLogs.Id],
  LoginLogs.CreatedOn AS [LoginLogs.CreatedOn],
  LoginLogs.IpAddress AS [LoginLogs.IpAddress],
  LoginLogs.Success AS [LoginLogs.Success],
  LoginLogs.UserAccountId AS [LoginLogs.UserAccountId],
  LoginLogs.UserName AS [LoginLogs.UserName]
FROM
  LoginLogs
WHERE
  LoginLogs.CreatedOn > '16/05/2015 00:00:00'

--> Time:   63 ms

While the above is useless to me since I need to group on the Date and not the time, it does seem to show the issue is around the date conversion in Lightspeed.

But even with the above, why is there two SQL statements, instead of a single one with a COUNT(1) in it?

Hopefully I am just missing something and this can be easily resolved :)

Cheers, Sean

The two statements is intentional - we generate the initial statement to determine the grouping sets and then we issue the subsequent statements to load the associated entities as needed.

Jeremy