How to call a stored procedure in EF Core 3.0 via FromSqlRaw

Question

I recently migrated from EF Core 2.2 to EF Core 3.0.

Unfortunately, I haven't found a way to call a stored procedure that returns an entity.

In EF Core 2.0 it was possible:

var spParams = new object[] { "bla", "xx" };
var createdPath = ModelContext.Paths.FromSql("AddNodeWithPathProc  @p0, @p1", spParams).Single();

In EF Core 3.0 the method FromSQL is replaced with FromSqlRaw. However, I didn't manage to successfully call a stored procedure and then process the value. This is useful when the stored procedure inserts data into the database.

So in EF Core 3.0, I use this code:

var createdPath = ModelContext.Paths.FromSqlRaw("AddNodeWithPathProc @p0, @p1", spParams).Single();

but it will throw an exception, because the generated SQL is invalid and looks something like this:

exec sp_executesql N'SELECT TOP(2) [p].[PathId], [p].[Level], [p].[NodeId], [p].[NodePath], [p].[NodePathString]
FROM (
     @sql @p0, @p1
) AS [p]',N'@p0 nvarchar(4000),@p1 nvarchar(4000), @sql nvarchar(100)',@p0=N'1a',@p1=N'', @sql=N'AddNodeWithPathProc'

I tried quite a few variations, but without success.

I'm starting to think that it is not possible to run stored procedures with ModelContext.[IQueryable].FromSqlRaw. In my opinion this kind defeats one of the major reasons for FromSqlRaw because, for normal select statements, LINQ is normally good enough.

Does anyone know how to use stored procedures in combination with FromSqlRaw in EF Core 3.0? Any help is greatly appreciated.

Thanks in advance

PS: I know you can execute a stored procedure with this.Database.ExecuteSqlRaw(SQL, parameters). However, that way it is not possible retrieve any entities that the stored procedure queries.

Answer 1

Solution (thanks to David Browne, you should have posted it as an answer):

Replacing Single with ToList works :-)

var createdPath = ModelContext.Paths.FromSqlRaw("AddNodeWithPathProc  {0}, {1}", nodeTitle, parentPathString).ToList();

Answer 2

It is extremely strange… just before a few days ago I have the same problem and follow this post. I had this call:

 public IEnumerable<TableChange> GetTableLastChanges(string tableName, string keyColumn, out int synchronizationVersion)
    {
        var parameters = new[] {
            new SqlParameter("@table_name", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = tableName },
            new SqlParameter("@key_column", SqlDbType.VarChar) { Direction = ParameterDirection.Input, Value = keyColumn },
            new SqlParameter("@synchronization_version", SqlDbType.BigInt) { Direction = ParameterDirection.InputOutput, Value = 0 }
        };

        var changes = this.TableChanges.FromSqlRaw("[dbo].[GetTableLastChanges] @table_name, @key_column, @synchronization_version OUTPUT", parameters).ToList();

        synchronizationVersion = Convert.ToInt32(parameters[2].Value);

        return changes;
    }

Right now everything is fine and this call works as expected. Therefore I should admit that there is no problem with datasets and params return for EF on Core 3.

Answer 3

var result=context.yourmodelclass.FromSqlInterpolated($"StoredProcedureName {param1},{param2}").tolist();

You can add multiple parameters if needed. Note:

• context => your name of database.
• yourmodelclass => the class in models folder you create for fetching the output result from the stored procedure result.

Answer 4

I am not where I can test but I think the following will work:

var createdPath = ModelContext.Paths.FromSqlRaw("AddNodeWithPathProc {0}, {1}", parm1 parm2).Single();