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.

wrap the stored procedure call with `select * from (exec ...)` — abdusco, Jul 01 '21 at 07:41
Fixed is different issue. Composing over SPs in SqlServer can't be done (try doing it in SQL and you'll see). Replacing SPs with TVFs in the only option. — Ivan Stoev, Jul 01 '21 at 10:11
select * from (exec ...) is incorrect syntax in SSMS and throws "SqlException" of "Incorrect syntax" in C# — Elizur, Jul 01 '21 at 10:48
example of composing over SP `SELECT TOP 1 * FROM (exec ProcedureName)`. This is not a valid SQL query. So you need to call `FromSqlRaw(...).AsEnumerable()` — FireAlkazar, Jul 01 '21 at 12:03
Thanks @FireAlkazar, but this is not what I'm doing is it? or maybe because I'm using the ` _context.Entity` to run this SP it automatically surrounds it with a "SELECT" ? — Elizur, Jul 01 '21 at 13:08
@Elizur we are guessing here. Could you try your attempts as follows `res = _context.Entity.FromSqlRaw($"exec dbo.ProcedureName").AsEnumerable()`? It works fine for me to call SP this way in EF 5.0.7 — FireAlkazar, Jul 01 '21 at 17:01
@FireAlkazar thanks , but I've tried this in every one of my attempts, all this does is allowing client side composition witch is not relevant for SP. — Elizur, Jul 02 '21 at 08:43

Charlieface · Answer 1 · 2021-07-01T11:43:36.863

2

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.

edited Jul 01 '21 at 11:43

answered Jul 01 '21 at 10:39

Charlieface

52,284
6
19
43

FromSqlInterpolated only accepts one parameter of type "FormatedString" so your code doesn't compile – Elizur Jul 01 '21 at 10:56
@Elizur You're right, I meant `FromSqlRaw`. And you need to add `AsEnumerable` – Charlieface Jul 01 '21 at 11:43
no such luck. I still get the exact same error as before. besides - AsEnumerable() is trying to convert it to client side composition (filtering via Linq) and that is out of the question. But what do you mean with = {paramValue}" I'm guessing that you mean = {0}" (like in the old String.Format()) – Elizur Jul 01 '21 at 13:03

score 0 · Answer 2 · answered Dec 22 '21 at 18:50

Entity Framework throws the exception preventing server-side composition unless the query starts with "SELECT".

if (char.ToLowerInvariant(c) == 's'
    && char.ToLowerInvariant(NextChar()) == 'e'
    && char.ToLowerInvariant(NextChar()) == 'l'
    && char.ToLowerInvariant(NextChar()) == 'e'
    && char.ToLowerInvariant(NextChar()) == 'c'
    && char.ToLowerInvariant(NextChar()) == 't')
{
    var (c1, c2) = (NextChar(), NextChar());
    if (char.IsWhiteSpace(c1)
        || c1 == '-' && c2 == '-'
        || c1 == '/' && c2 == '*')
    {
        return;
    }
}

throw new InvalidOperationException(RelationalStrings.FromSqlNonComposable);

You specifically mention "Client-side composition is not an option" but as Ivan Stoev commented, SQL Server does not support composition over stored procedures. The recommendation to re-write as a table-valued function may be possible if it can be done without side effects that modify the database state.

There's a whole pile of issues related to FromSql and mapping, some of which reference composition. I believe this one indicates that EF5.0 only resolved the 'non-composable' exception for root table-per-hierarchy entities.

use stored procedure with EFCore5.0 error System.InvalidOperationException

2 Answers2