Entity Framework Core - Using Stored Procedure with output parameters

asp.net entity-framework entity-framework-core

Question

I'm trying to use stored procedures with new Entity Framework Core. I need to start new project soon which will be ASP.Net 5, but not sure if Entity Framework will fit for the job. The application will be triggering several stored procedures per minute and I need output parameters. Will EF be good for this or should I use ADO.Net?

I have tried FromSql and database.ExecuteSqlCommand but no luck.

using (AppDbContext db = factory.Create())
        {
            var in1 = new SqlParameter
            {
                ParameterName = "ParamIn1",
                DbType = System.Data.DbType.Int64,
                Direction = System.Data.ParameterDirection.Input
            };
            var in2 = new SqlParameter
            {
                ParameterName = "ParamIn2",
                DbType = System.Data.DbType.String,
                Direction = System.Data.ParameterDirection.Input
            };
            var out1 = new SqlParameter
            {
                ParameterName = "ParamOut1",
                DbType = System.Data.DbType.Int64,
                Direction = System.Data.ParameterDirection.Output
            };
            var out2 = new SqlParameter
            {
                ParameterName = "ParamOut2",
                DbType = System.Data.DbType.String,
                Direction = System.Data.ParameterDirection.Output
            };

            var result = db.Database.ExecuteSqlCommand("exec spTestSp", in1, in2, out1, out2);
        }
1
9
7/22/2017 9:33:20 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Accepted Answer

It should work, but I believe you also need to include the parameter names and the OUT keyword in the command statement 

var sql = "exec spTestSp @ParamIn1, @ParamIn2, @ParamOut1 OUT, @ParamOut2 OUT";
var result = db.Database.ExecuteSqlCommand(sql, in1, in2, out1, out2);

var out1Value = (long) out1.Value;
var out2Value = (string) out2.Value;
12
7/22/2017 10:34:02 AM

Popular Answer

Full example of the solution as described above by @Nkosi and partially by @Whistler for the ease of future readers!

In my example, I was trying to execute a stored procedure existed in my database to get specific path.

string tableName = "DocumentStore";
string path;

        var in1 = new SqlParameter
        {
            ParameterName = "TableName",
            Value = tableName,
            Size = Int32.MaxValue,
            DbType = System.Data.DbType.String,
            Direction = System.Data.ParameterDirection.Input
        };

        var out1 = new SqlParameter
        {
            ParameterName = "Path",
            DbType = System.Data.DbType.String,
            Size = Int32.MaxValue,
            Direction = System.Data.ParameterDirection.Output
        };

        //_context is DbContext Object
        _context.Database.ExecuteSqlCommand("EXEC GetFileTableRootPath @TableName, @Path OUT", in1,out1);

        path = out1.Value.ToString();
Imran Faruqi

Related Questions

Executing a stored procedure that returns a result set and has output parameters with Entity Framework

It turns out that the output parameters aren't available until all results are read. I fixed my problem by appending ....ToList()... to the tend of the ...ExecuteFunction()... call. I figured it out by browsing the metadata in Visual Studio. You can see the relevant documentation here: ...I don't see this mentioned in the actual documentation on...
c# entity-framework entity-framework-6 stored-procedures
asked by Tim Coker

Calling Oracle stored procedure using Entity Framework with output parameter?

In this case, you shouldn't be calling:...var query = ctx.Database.SqlQuery<CmdRegisterAssetDto>(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam); ...But instead be calling:...var result = ctx.Database.ExecuteSqlCommand(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam); ...Notice that the onl...
c# entity-framework entity-framework-6 oracle oracle-manageddataaccess
asked by Jeff

Get output parameter value of a stored procedure using EF Core?

In EF Core you can't return ad-hoc types from raw SQL queries yet (they are working on this), so you will need a workaround for this issue. Add this class to your project:...using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Infrastructure; using Microsoft.EntityFrameworkCore.Internal; using Microsoft.EntityFrameworkCore.Stora...
asp.net-core c# entity-framework entity-framework-core
asked by Rohirrim

Entity framework core Output parameter not working in store procedure using FromSql method (Linq)

I don't' have access to your whole code, but nothing ever gets executed against your queryable source until you try to enumerate it. So, probably it didn't run the Stored Procedure when you tried to get the OUTPUT....To force immediate query evaluation you can do it:... IQueryable<T> foo = _context.Set<T>().FromSql("Sp_Todo @totalItems OUTPUT", new...
c# entity-framework-core linq sql sql-server
asked by Rohirrim

Exec Stored Procedure with output parameter in Entity Framework Core 2

You need to add comma's in between the parameters:...[My Stored Proc] @certificateTypeId, @personId, @...
c# entity-framework-core sql-server stored-procedures
asked by Peter B

SQL Server stored procedure output parameter and return data from select

The simplistic answer is to add ...@managerID... to your select statement...For a less simplistic perhaps the following, I changed the names a bit to reflect the use, get parameter after it closes....var managerIDParam = new SqlParameter("@managerID", System.Data.SqlDbType.Int); managerIDParam.Direction = ParameterDirection.Output; cmd.Parameters.A...
entity-framework-core output sql-server stored-procedures
asked by Mark Schultheiss

How to call stored procedure with table valued parameter from .net core Entity Framework

Finally I could able to call Table values parameter Stored procedure from my .net core service which uses EF core...I have created fake Response Domain Model ... public class MyResponse { public int Id { get; set; } public int Col1{ get; set; } public int Col2 { get; set; } } ...I called it Fake Domain Model becau...
.net c# entity-framework-core sql-server
asked by Rajiv

How to call stored procedure in Entity Framework Core with input and output parameters using mysql

I found the solution using @matt-g suggestion based on this ...Question.... I had to use ADO.net for this as...var technicians = new List<TechnicianModel>(); using (MySqlConnection lconn = new MySqlConnection(_context.Database.GetDbConnection().ConnectionString)) { lconn.Open(); using (MySqlCommand cmd = new MySqlCommand()) { c...
entity-framework-core input-parameters mysql output-parameter stored-procedures
asked by Hamza Khanzada

How to execute stored procedure for multiple parameters using FromSqlInterpolated in EF Core 3.0?

You can learn how to use sql command in ef core with this artical:...https://www.learnentityframeworkcore.com/raw-sql#stored-procedures...https://www.learnentityframeworkcore.com/raw-sql#database.executesqlcommand...As efcore updated. You don't need to build SqlParameter in the new efcore api....ExecuteSqlCommandAsync and FromSql is obsolete now ,y...
.net-core asp.net-core ef-core-3.0 entity-framework-core stored-procedures
asked by ws_

Entity Framework Core stored procedure with input parameter and return custom model

I am trying to return a list of students from the stored procedure. I am using ASP.NET boilerplate which uses Entity Framework Core 2.2.x....I am not sure how to execute stored procedure here. I tried to follow ...How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5...but I get an error:...Database...
aspnetboilerplate c# entity-framework-core stored-procedures
asked by Gopi

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow