X

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.

Liked this post? Empower your friends by sharing it!

View Comments (10)

  • Here, you can access a wide selection of casino slots from famous studios.
    Players can enjoy traditional machines as well as new-generation slots with vivid animation and bonus rounds.
    If you're just starting out or an experienced player, there’s always a slot to match your mood.
    casino
    Each title are instantly accessible anytime and optimized for laptops and tablets alike.
    You don’t need to install anything, so you can jump into the action right away.
    The interface is user-friendly, making it convenient to browse the collection.
    Register now, and dive into the world of online slots!

  • Supplier Sinar Baja Medan - Supplier BESI WELDED BEAM Terlengkap di medan, Distributor PLAT LUBANG Terbaik di medan, Supplier PLAT HAIR
    LINE Terlengkap dikota medan , Harga
    PLAT RAIL Termurah di medan - Sinar Baja

  • Supplier Sinar Baja Medan - Distributor BESI COIL Termurah
    di medan, Toko BESI UNP / CNP Terlengkap di medan, Distributor PLAT BESI Termurah dikota medan , Harga BESI SIKU Termurah di medan - Sinar Baja

  • Supplier Sinar Baja Medan - Agen PLAT KUNINGAN Terlengkap di medan, Supplier
    BESI EXPANDED Terbaik dikota medan, Distributor PLAT LUBANG
    Terbaik di medan , Toko PLAT BESI Terlengkap dikota medan - Sinar Baja

  • This post is really a nice one it helps new web viewers, who are wishing for blogging.

  • Supplier Sinar Baja Medan - Harga PLAT RAIL Terbaik di medan,
    Supplier BESI UNP / CNP Termurah di medan,
    Toko BESI BAJA Terbaik di medan , Agen BESI H BEAM Terbaik dikota medan - Sinar Baja

  • Supplier Sinar Baja Medan - Harga PLAT BESI Terlengkap di
    medan, Supplier BESI COIL Terbaik dikota medan, Supplier
    BESI WF Termurah di medan , Supplier BESI SIKU
    Terpercaya dikota medan - Sinar Baja

Related Post