LINQ Group By method not generating the expected SQL

Question

Following LINQ query is supposed to return number of logins per user:

Controller:

var lst = _context.LoginHistory.GroupBy(l => l.UserName).Select(lg => new { user_name = lg.Key, cnt = lg.Count() });

 return View(lst.ToList());

But the SQL Profiler of SQL Server 2012 is returning the following strange query:

SQL Profiler Output:

SELECT [l].[LoginHistory], [l].[Hit_Count], [l].[LastLogin], [l].[UserName]
FROM [LoginHistory] AS [l]
ORDER BY [l].[UserName]

Model:

public class LoginHistory
{
   public int LoginHistoryId { get; set; }
   public string UserName { get; set; }
   public int Hit_Count { get; set; }
   public DateTime LoginDate { get; set; }
}

NOTE:

1. I don't know why even column Hit_Count is in the profiler output query as it should play no role here - all I'm trying is to display total number of logins per user. Moreover, in SQL Profiler output, I was expecting something similar to the following t-sql:
2. It's the only LINQ qry the app executes so it's not that I'm mistakenly picking the wrong SQL in the SQL Profiler
3. The result in the view is also not correct [that actually led me to all the investigation shown in this post]
4. Could it be another EF Core 1.1.1 bug as the other one pointed out by another user here

Expected [or something similar] SQL Profiler output:

SELECT username, COUNT(*)
FROM LoginHistory
GROUP BY username

Answer 1

Many people are surprised when they are using SQL + LINQ + Entity Framework and when they want to run a simple aggregate function such as yours to find that the Sql Profiler doesn't reflect the aggregation and shows something very similar to a generic SELECT * FROM table.

While most applications using LINQ and EF are also using a database server, others are using or are also using and mapping data from other data sources, such as XML, flat files, Excel spreadsheets into the application's entities/models/classes.

So the normal mode of operation when aggregating data within LINQ is to load and map the resource data, and then perform the desired functions within the application.

This may work fine for some, but in my situation I have limited application server resources and a ton of database resources, so I choose to shift these functions onto my SQL Server and then create a method within the class to use ADO and execute raw SQL.

Applied to your particular model we would have something similar to this, it may vary depending on your particular coding style and any applicable standards.

public class LoginHistory {
    public int LoginHistoryId { get; set; }
    public string UserName { get; set; }
    public int Hit_Count { get; set; }
    public DateTime LoginDate { get; set; }

    public List<LoginHistory> GetList_LoginTotals() {
        List<LoginHistory> retValue = new List<LoginHistory>();

        StringBuilder sbQuery = new StringBuilder();
        sbQuery.AppendLine("SELECT username, COUNT(*) ");
        sbQuery.AppendLine("FROM LoginHistory ");
        sbQuery.AppendLine("GROUP BY username");

        using (SqlConnection conn = new SqlConnection(strConn)) {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(sbQuery.ToString(), conn)) {
                cmd.CommandType = CommandType.Text;
                using (SqlDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        var row = new LoginHistory {
                            UserName = reader.GetString(0)
                            , Hit_Count = reader.GetInt32(1)
                        };
                        retValue.Add(row);
                    }
                }
            }
            conn.Close();
        }
        return retValue;
    }
}

And your controller code could be updated to something similar to this:

var LoginList = new LoginHistory().GetList_LoginTotals(); 
return View(LoginList);

// or the one liner: return View(new LoginHistory().GetList_LoginTotals());