LINQ Group By method not generating expected SQL
The following LINQ query should return the number of logins for each 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 it SQL Profiler of SQL Server 2012
returns the following weird 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 :
- I don't know why even the column
Hit_Count
is in the profiler's output query, since it shouldn't play any role here - all I'm trying to do is show the total number of logins for each user. Moreover, in the release of SQL Profiler, I was expecting something similar to the following t-sql: - This is the only LINQ application being executed by the application, so I am not mistaken about choosing the wrong SQL in SQL Profiler
- Result in view is also wrong [which actually led me to all the research shown in this post]
- Maybe this is another EF Core 1.1.1 bug as another pointed out by another user here
The expected [or similar] output from SQL Profiler is:
SELECT username, COUNT(*)
FROM LoginHistory
GROUP BY username
source to share
A lot of people are surprised when they use SQL + LINQ + Entity Framework and when they want to run a simple aggregate function like yours to find that the Sql Profiler doesn't reflect the aggregation and shows something very similar to the generic one SELECT * FROM table
.
While most applications that use LINQ and EF also use a database server, others use or also use and display data from other data sources such as XML, flat files, Excel spreadsheets into application entities / models / classes.
Thus, the normal mode of operation when aggregating data in LINQ is to load and display resource data, and then execute the required functions within the application.
This might work fine for some, but in my situation, I have limited application server resources and a ton of database resources, so I decided to switch these features to my SQL Server and then create a method inside the class to use ADO and execute the raw SQL.
We will have something similar to this for your specific model, it may vary depending on your 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 can be updated something like this:
var LoginList = new LoginHistory().GetList_LoginTotals();
return View(LoginList);
// or the one liner: return View(new LoginHistory().GetList_LoginTotals());
source to share
This time it's not a bug (according to the EF Core team), but an incomplete feature (because in EF6 it worked as you'd expect). You can see it "documented" in the EF Core Roadmap :
It seems to us what we need before we say that EF Core is the recommended version of EF. Until we implement these features, EF Core will be a valid option for many applications, especially on platforms such as UWP and .NET Core where EF6.x does not work, but for many applications, the lack of these features will make EF6.xa a better option.
and then
The GroupBy translation will move the LINQ GroupBy statement to the database, not memory.
This so-called customer score (a feature of EF Core that doesn't exist in previous versions of EF) is the root of all evil. This allows EF Core to "successfully process" many queries in memory, thus leading to performance problems (although by definition they should be correct).
This is why I suggest always enabling EF Core Logging to keep track of what is really going on with your requests. For example, for the sample request, you will see the following warnings:
The LINQ expression 'GroupBy([l].UserName, [l])' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
The LINQ expression 'GroupBy([l].UserName, [l])' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
The LINQ expression 'Count()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
You can also turn off customer rating by adding the following to your DbContext
OnConfiguring
override:
optionsBuilder.ConfigureWarnings(bulder => bulder.Throw(RelationalEventId.QueryClientEvaluationWarning));
but now you will just get a runtime exception from this request.
If this is important to you, then you probably fall into the category of applications, the lack of these features will make EF6.x a better option.
source to share