EF Core 5.0

CreateDbCommand: I'll see your string and raise you a command...

My previous post showed using ToQueryString to get generated SQL. This will commonly be copy-pasted, but it could also be executed directly by the application. For example:

var city = "London";
var query = context.Customers.Where(c => c.City == city);

var connection = context.Database.GetDbConnection();
using (var command = connection.CreateCommand())
{
    command.CommandText = query.ToQueryString();

    connection.Open();
    using (var results = command.ExecuteReader())
    {
        // Do something...
    }
    connection.Close();
}

This will work in simple cases, but the translation to a query string and back to a command loses some information. For example, if a transaction is being used then the code above would need to find that transaction and associate itself.

Instead, EF Core 5.0 introduces CreateDbCommand which creates and configures a DbCommand just as EF does to execute the query. For example:

var city = "London";
var query = context.Customers.Where(c => c.City == city);

var connection = context.Database.GetDbConnection();
using (var command = query.CreateDbCommand())
{
    connection.Open();
    using (var results = command.ExecuteReader())
    {
        // Do something...
    }
    connection.Close();
}

Using this code the command is already configured with transactions, etc. Also, the parameters are configured on the command in exactly the way EF Core does to run the query. This makes CreateDbCommand the highest fidelity way of exactly getting the DbCommand what EF would use.

A few things to note:

This is not the actual DbCommand instance that EF will use to execute the query. If EF executes the query, then it will create a new DbCommand instance to do so.
The command must be disposed by application code, just as if it had been manually created.
CreateDbCommand is only available for relational database providers. ToQueryString, on the other hand, also works with the Cosmos provider.

Caution!

Be careful what you do with generated commands. EF Core coordinates the setup of the command with the expected form and shape of the results. It may not always be what you are expecting! It also may change as EF internals continue to evolve.

This page is up-to-date as of January 15th, 2020. Some things change. Some things stay the same. Use your noggin.