EF Core 5.0: Using ToQueryString() method to translate LINQ query to SQL

Entity Framework Core 5.0 comes with SQL-based brother to ToString() method for LINQ-queries. This method is called ToQueryString() and it returns provider-specific SQL without connecting to database server. In some means it’s similar to ToTraceString() method of Entity Framework. This blog post shows how ToQueryString() method works.

NB! At the time of writing this post .NET Core 5.0 and EF Core 5.0 are both in Preview and things may change in future versions of EF Core 5.0.

I created new ASP.NET Core web application project with latest Visual Studio 2019 Preview and connected EF Core to one of my existing databases. I added just few entities and primitive DbContext to try out how ToQueryString() extension method works.

public class Customer
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}
 
public class Project
{
    public Guid Id { get; set; }
    public string Title { get; set; }
    public string ProjectKey { get; set; }
 
    public Customer Customer { get; set; }
}
 
public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {
    }
 
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Project> Projects { get; set; }
}

I injected my DbContext to HomeController and in Index() action I wrote a query to see what ToQueryString() method returns. I was a bit clever when writing the query – notice how I used variable called prefix in LINQ expression.

public class HomeController : Controller
{
    private readonly MyDbContext _dataContext;
 
    public HomeController(MyDbContext dataContext)
    {
        _dataContext = dataContext;
    }
 
    public IActionResult Index()
    {
        var prefix = "A";
        var query = _dataContext.Projects
                                .Include(p => p.Customer)
                                .Where(p => p.Customer.Name.StartsWith(prefix))
                                .OrderBy(p => p.Customer.Name)
                                .ThenBy(p => p.Title)
                                .Select(p => new { Project = p.Title, Customer = p.Customer.Name });
 
        var queryString = query.ToQueryString();
        var result = query.FirstOrDefault();
        return View();
    }
}

When running the code in debug mode and checking the value of queryString variable we can see nice formatted SQL in Text Visualizer window.

The best thing is – we can take this SQL with copy and paste to SQL Server Management Studio (SSMS) or some other database tool to investigate the query closer. It’s ready to run without any modifications.

When ToQueryString() doesn’t work

There’s a little gotcha with ToQueryString() method – we cannot use it with methods that immediately run SQL query. It gives us valid query for ToList() and ToListAsync() methods but if we call FirstOrDefault() or FirstOrDefaultAsync() method on query then these methods add some more SQL that we cannot see with ToQueryString() method.

Let’s see what SQL is generated when we apply FirstOrDefault() method to query above. The actual query that is sent to database server is here.

SELECT TOP(1) [p].[Title] AS [Project], [c].[Name] AS [Customer]
FROM [Projects] AS [p]
LEFT JOIN [Customers] AS [c] ON [p].[CustomerId] = [c].[Id]
WHERE (@__prefix_0 = N'') OR ([c].[Name] IS NOT NULL AND (LEFT
([c].[Name], LEN(@__prefix_0)) = @__prefix_0))
ORDER BY [c].[Name], [p].[Title]

It’s almost the same but there’s TOP(1) after SELECT. The query gets even more different when we apply Count() method instead of FirstOrDefault().

SELECT COUNT(*)
FROM [Projects] AS [p]
LEFT JOIN [Customers] AS [c] ON [p].[CustomerId] = [c].[Id]
WHERE (@__prefix_0 = N'') OR ([c].[Name] IS NOT NULL AND (LEFT
([c].[Name], LEN(@__prefix_0)) = @__prefix_0))

For methods like FirstOrDefault() and Count() we need EF Core query logging. I will cover it in some of my future posts.

Wrapping up

ToQueryString() method helps us to see database queries generated by EF Core. It’s new feature in coming EF Core 5.0. We can use this method to get main part of SQL based on LINQ query we wrote. We cannot use ToQueryString() method to find out how query is modified by methods that send query to database – FirstOrDefault(), Count(), etc. For these methods we have to use EF Core logging that is not covered here. ToQueryString() method gives us SQL without making actual request to database server. The SQL we get is ready to run in database management tools for further investigation of generated query.