Writing LINQ that produces optimal SQL can be even harder as developers often don’t have visibility into the process. It becomes even more confusing when the application is designed to run against different databases.
We often find ourselves questioning whether this particular query will fall in line with our expectations. And until not so long ago our tool of choice was a SQL Profiler, that ships with SQL Server. It’s plenty powerful but has one flaw – it pretty much requires the SQL Server installation. This might be a deal breaker for some clients using other DBs, like Postgres or MySQL (which are all supported by the way).
EF to the resque
Instead of firing off the profiler and fishing out the batches, we could have Entity Framework itself pass us the result. After all, it needs to build SQL before sending it off the the database, so all we have to do it to ask nicely. Stack Overflow is quite helpful here:
public static class IQueryableExtensions // this is the EF Core 3.1 version.
{
public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
{
var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
var relationalCommandCache = enumerator.Private("_relationalCommandCache");
var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");
var sqlGenerator = factory.Create();
var command = sqlGenerator.GetCommand(selectExpression);
string sql = command.CommandText;
return sql;
}
private static object Private(this object obj, string privateField) => obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
private static T Private<T>(this object obj, string privateField) => (T)obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
}
The usage is simple
Suppose we’ve got the following inputs: One simple table, that we’d like to group by one field and total by another. Database Context is also pretty much boilerplate. One thing to note here is a couple of database providers we are going to try the query against.
public class SomeTable
{
public int Id { get; set; }
public int Foobar { get; set; }
public int Quantity { get; set; }
}
class MyDbContext : DbContext
{
public DbSet<SomeTable> SomeTables { get; set; }
public static readonly LoggerFactory DbCommandConsoleLoggerFactory
= new LoggerFactory(new[] {
new ConsoleLoggerProvider ((category, level) =>
category == DbLoggerCategory.Database.Command.Name &&
level == LogLevel.Trace, true)
});
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
//run with SQL Server provider to get T-SQL
optionsBuilder.UseNpgsql("Server=localhost;Port=5432;Database=test;User Id=;Password=;")
//alternatively use other supported provider
//optionsBuilder.UseSqlServer("Server=.\\SQLEXPRESS;Database=test;Trusted_Connection=true")
;
base.OnConfiguring(optionsBuilder);
}
}
The test bench would look something like so
class Program
{
static void Main(string[] args)
{
var context = new MyDbContext();
var someTableData = context.SomeTables
.GroupBy(x => x.Foobar)
.Select(x => new { Foobar = x.Key, Quantity = x.Sum(y => y.Quantity) })
.OrderByDescending(x => x.Quantity)
.Take(10) // we've built our query as per normal
.ToSql(); // this is the magic
Console.Write(someTableData);
Console.ReadKey();
}
}
And depending on our choice of provider the output would show ef core generated sql for SQL Server and Postgres
-- MSSQL
SELECT TOP(@__p_0) [s].[Foobar], SUM([s].[Quantity]) AS [Quantity]
FROM [SomeTables] AS [s]
GROUP BY [s].[Foobar]
ORDER BY SUM([s].[Quantity]) DESC
-- PG SQL
SELECT s."Foobar", SUM(s."Quantity")::INT AS "Quantity"
FROM "SomeTables" AS s
GROUP BY s."Foobar"
ORDER BY SUM(s."Quantity")::INT DESC
LIMIT @__p_0
Top comments (0)