Execute raw SQL commands in Entity Framework Core

I have never seen a real-life project where object-relational mapper generates 100% of needed SQL. There have always been those special cases when raw SQL commands are needed. In this post I will demonstrate how to run raw SQL commands in Entity Framework Commands and how to read data from database without DbSet and query types.

Executing raw SQL commands

Entity Framework Core has ExecuteSqlCommand() and ExecuteSqlCommandAsync() methods to run custom SQL queries and commands. Here’s the example of running stored procedure to update balance for all customers.

public async Task UpdateBalanceForCustomers()
{
    await Database.ExecuteSqlCommandAsync("EXEC sp_BalanceUpdate");
}

We can also use parameters with these methods like shown here.

public async Task UpdateBalanceForCustomer(int customerId)
{
    await Database.ExecuteSqlCommandAsync("EXEC sp_Customer BalanceUpdate @p1", customerId);
}

These methods are fine but how they are executed is decided by Entity Framework Core.

Using ADO.NET

We can use ADO.NET data objects to get complete control and go around all wisdom coming with Entity Framework Core. Here’s the code from first sample of this post but it is implemented using raw database objects.

public async Task UpdateBalanceForCustomers()
{
    using (var command = Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = "sp_BalanceUpdate";
        command.CommandType = CommandType.StoredProcedure;

        await command.ExecuteNonQueryAsync();
    }
}

This code is pretty primitive. It just executes stored procedure and this is it.

Getting data from raw SQL commands

Sometimes we need to get data back from custom raw SQL. Good example is reporting data we are showing as a table on web page. It’s sometimes tempting to return data reader to keep things minimal but be aware – you are taking a great responsibility! Before running any other command on same connection the data reader must be closed or you end up with exception.

What has best worked for me is loading data to DataTable like shown here.

public async Task<DataTable> GetAverageTemperatures(DateTime forDate)
{
    using(var command = Database.GetDbConnection().CreateCommand())
    { 
        command.CommandText = "sp_AverageTemperaturesReport";
        command.CommandType = CommandType.StoredProcedure;

        var forDateParam = command.CreateParameter();
        forDateParam.ParameterName = "@ForDate";
        forDateParam.DbType = DbType.Date;
        forDateParam.Value = forDate;
        command.Parameters.Add(forDateParam);

        using(var reader = await command.ExecuteReaderAsync())
        { 
            var table = new DataTable();
            table.Load(reader);

            return table;
        }
    }
}

Using this code we can also run custom SQL commands and not only stored procedures. We are still running commands in database context but what we are doing is not monitored by Entity Framework core. For reporting DataTable is ideal – we can change the structure of data returned by SQL command and everything still works.

Common view for DataTable. If you write views or procedures for reporting where field names are replaced by human readable names then you can use my common DataTable view to display DataTables. You can also derive your own common view for your DataTables using my code as a starting point.

Wrapping up

There are multiple ways to execute raw SQL commands using Entity Framework Core. This post focused on a shortcut methods like ExecuteSqlCommand() and ExecuteSqlCommandAsync() and also took raw ADO.NET to focus as it is sometimes the work horse we actually need. The code here doesn’t conflict with Entity Framework database context and it is safe to use for data reading as it doesn’t leave any data readers open.

The post Execute raw SQL commands in Entity Framework Core appeared first on Gunnar Peipman - Programming Blog.