Executing raw SQL queries with EF Core and SQL Server - System.Data.SqlClient.SqlException: 'Could not find stored procedure 'Core.SetContextInfo'

Question

I use EF Core 7 in an ASP.NET Core project. EF Core's FromSql method thinks the provided parameter is the name for a stored procedure, when it's an actual SQL query. How can I make it run select queries and retrieve results?

Based on the EF Core documentation, I need to use either FromSql or FromSqlInterpolated methods to perform SELECT operations with raw SQL input. However, when I use FromSql, I get the error:

Microsoft.Data.SqlClient.SqlException Could not find stored procedure 'SELECT * FROM dbo.Country'

This simple test query (SELECT * FROM dbo.Country) works fine when I run it in SSMS. However, for some reason, EF Core thinks it is the name for a stored procedure, and attempts to find a stored procedure, which obviously fails.

I tried to see if FromSql method has an overload for specifying whether the string parameter is an actual query or the name for a stored procedure, but it doesn't seem to be a possibility. It looks like the method only takes in a single string parameter, and somehow makes a decision inside on whether the provided parameter is a stored procedure or a query.

On the documentation page, it is clear that FromSql is capable of taking actual queries as input.

Here is my code where I'm getting the error (parameter is SELECT * FROM dbo.Country):

public async Task<List<T>> RunRawSql(string query)
{
    try
    {
        var a = _context.Set<T>().FromSql($"{query}");
        return a.ToList();
    }
    catch (Exception ex)
    {
        return null;
    }
}

Answer 1

The problem was that FromSql method does not allow parameterizing actual database schema (table names, column names, etc). In my case, I am sending the whole query as a parameter, which falls into the category of parameterizing database schema, thus FromSql does not work. Apparently, if you need to parameterize schema, you must use FromSqlRaw instead.

Note that FromSqlRaw is prone to SQL injection attacks, so you should instead use FromSql or FromSqlInterpolated whenever possible. You must pay attention to never send any user input into here. However, as long as you completely decouple this method from any user input, it is okay to use FromSqlRaw.