Error executing stored procedure with FromSqlInterpolated in ASP.NET Core 6 with EF Core 6

Question

I have in my SQL Server this stored procedure with a single parameter and that returns a single record.

When I call it like this:

public async Task<EmpleadoDTO> GetEmpleado(string codigoSAP)
{
     var empleado = await _context.Empleados
                                  .FromSqlInterpolated($"GetEmpleadoByCodigoSAP {codigoSAP}")
                                  .FirstOrDefaultAsync();

     return EmpleadoToDTO(empleado);
}

I get this error

System.InvalidOperationException: 'FromSqlRaw' or 'FromSqlInterpolated' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.

But if I execute the SQL directly:

var empleado = await _context.Empleados
                             .FromSqlInterpolated($"select empID as EmpleadoId,firstName as Nombre,middleName as Apellido1,lastName as Apellido2,Email as Email,U_CODEMPL as CodigoSAP,U_Activo as Activo,U_Sociedad as Sociedad from OHEM where U_CODEMPL={codigoSAP}")
                             .FirstOrDefaultAsync();

it works fine.

Any ideas, please?

Thanks

Answer 1

Starting with EF Core 3.1 FromSqlRaw / FromSqlInterpolated methods when used with stored procedure cannot be composed so you should use AsEnumerable / AsAsyncEnumerable.

ref Microsoft docs