CODEWITHSUNDEEP
c#entity-framework-core
Elizur
Elizur

Reputation: 31

use stored procedure with EFCore5.0 error System.InvalidOperationException

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.

Upvotes: 3

Views: 502

Answers (2)

user423430
user423430

Reputation: 3704

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.

Upvotes: 0

Charlieface
Charlieface

Reputation: 71119

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.

Upvotes: 2

Related Questions