CODEWITHSUNDEEP
c#sql-server-2012linq-to-entitiesentity-framework-coreasp.net-core-1.1
nam
nam

Reputation: 23749

LINQ Group By method not generating the expected SQL

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

Upvotes: 4

Views: 1529

Answers (2)

Mad Myche
Mad Myche

Reputation: 1075

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());

Upvotes: -1

Ivan Stoev
Ivan Stoev

Reputation: 205539

This time it's not really a bug (according to EF Core team), but incomplete feature (because in EF6 it worked the way you expect). You can see it "documented" in the EF Core Roadmap:

The things we think we need before we say 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.x a better option.

and then

GroupBy translation will move translation of the LINQ GroupBy operator to the database, rather than in-memory.

The so called client evaluation (a feature of EF Core which doesn't exist in previous EF versions) is the root of all evil. It allows EF Core to "process successfully" many queries in memory, thus introducing performance issues (although by definition they should produce the correct result).

That's why I would suggest always turning on the EF Core Logging to monitor what really happens with your queries. For instance, for the sample query you would 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 client evaluation by adding the following inside your DbContext OnConfiguring override:

optionsBuilder.ConfigureWarnings(bulder => bulder.Throw(RelationalEventId.QueryClientEvaluationWarning));

but now you will simple get a runtime exception from that query.

If this is important for you, then you probably fall in the category of applications the lack of these features will make EF6.x a better option.

Upvotes: 6

Related Questions