CODEWITHSUNDEEP
c#sqllinqlambda
nick gowdy
nick gowdy

Reputation: 6511

Rewriting this SQL in Lambda - using count and group by

I'm stuck rewriting this SQL in Lambda:

SELECT TOP 1000 COUNT(LoginDateTime)
      ,[LoginDateTime]
      ,[Culture]
  FROM [LearningApp].[dbo].[UserActivities]
  group by LoginDateTime, Culture

Result:

+-----+---------------------------+----------+
|     |      LoginDateTime        |  Culture |
+-----+---------------------------+----------+
|  1  |  2016-07-14 12:21:23.307  |    de    |
|  4  |  2016-07-13 12:21:23.307  |    en    |
|  2  |  2016-07-14 12:21:23.307  |    en    |
+-----+---------------------------+----------+

And my code:

public List<UserActivityResponseContract> GetUserActivity()
{
    var userActivityResponseContracts = new List<UserActivityResponseContract>();

    var userActivitiesList = _baseCommands.GetAll<UserActivity>()
        .Select(x => new
        {
            x.LoginDateTime, 
            x.Culture
        })
        .GroupBy(x => new { x.LoginDateTime, x.Culture});

    foreach (var userActivity in userActivitiesList)
    {
        userActivityResponseContracts.Add(new UserActivityResponseContract
        {
            ActivityDate = userActivity.Key.LoginDateTime.ToShortDateString(),
            NumberOfTimesLoggedIn = userActivity.Count(),
            Culture = userActivity.Key.Culture
        });
    }
    return userActivityResponseContracts;
}

It doesn't seem very difficult but I am a bit stuck.

Upvotes: 0

Views: 69

Answers (1)

Gilad Green
Gilad Green

Reputation: 37299

Method Syntax:

var result = _baseCommands.GetAll<UserActivity>()
                          .GroupBy(x => new { x.LoginDateTime, x.Culture})
                          .Select (x => new UserActivityResponseContract
                          { 
                              ActivityDate = x.Key.LoginDateTime.ToShortDateString(),
                              Culture = x.Key.Culture, 
                              NumberOfTimesLoggedIn = x.Count()
                          })
                          .Take(1000).ToList();

You can also use an overload of GroupBy that enables you to pass the select function as a second parameter

Query Syntax:

var result = (from x in _baseCommands.GetAll<UserActivity>()
              group x by new { x.LoginDateTime, x.Culture} into g
              select new UserActivityResponseContract
              { 
                 ActivityDate = g.Key.LoginDateTime.ToShortDateString(),
                 Culture = g.Key.Culture, 
                 NumberOfTimesLoggedIn = g.Count()
              }).Take(1000).ToList();

To GroupBy just the Date part of this DateTime do: x.LoginDateTime.Date

Upvotes: 3

Related Questions