use stored procedure with EFCore5.0 error System.InvalidOperationException

Question

I'm using the latest EF.core version (5.0.7) and attempting to retrieve entities via stored procedure. Tried the following:

 //attemp 1
 res = _context.Entity.FromSqlRaw($"exec dbo.ProcedureName");
 //attemp 2
 res = _context.Entity.FromSqlRaw("exec ProcedureName", new SqlParameter("@ParamName", paramValue));
 //attemp 3
 res = _context.Entity.FromSqlRaw($"exec ProcedureName {paramValue}");
 //attemp 4
 res = _context.Entity.FromSqlInterpolated($"exec ProcedureName @ParamName = {paramValue}");
 //attemp 5
 res = _context.Entity.FromSqlInterpolated($"exec ProcedureName {paramValue}");

All of the examples and without the "exec", parameter name with and without "@" and procedure name with and without leading "dbo" I keep getting the error "System.InvalidOperationException: FromSqlRaw or FromSqlInterpolated was called with non-compostable SQL and with a query composing over it. Consider calling AsEnumerable after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client-side." what am I doing wrong?

P.S.

Client-side composition is not an option.

The procedure works fine in SSMS.

In another thread This was claimed to be an issue resolved by EF5.0, but it clearly isn't.

Answer 1

In order to use FromSqlRaw, you need to let it do the interpolation, it will convert it to parameters. Do not do it yourself.

res = _context.Entity
  .FromSqlRaw("exec ProcedureName @ParamName = {paramValue}", paramvalue)
  .AsEnumerable();

Note the lack of the $. You also need to add AsEnumerable to prevent it from trying to compose it.