CODEWITHSUNDEEP
entity-framework
Deepanjan Nag
Deepanjan Nag

Reputation: 931

Entity Framework Core, Stored Procedure

I am totally confused regarding how to use Stored Procedures using Entity Framework Core. If the stored procedure return an anonymous type, how do I retrieve the data? If the return type is not anonymous, what should I do? How do I add input/output parameters?

I am asking these questions because everywhere I look, I get a different answer. I guess EF Core is evolving rapidly and Microsoft is dabbling with a lot of ideas.

Upvotes: 0

Views: 1050

Answers (3)

Manfred Wippel
Manfred Wippel

Reputation: 2076

you might use an extention like StoredProcedureEFCore

Then the usage is more intuitively.

List rows = null;

ctx.LoadStoredProc("dbo.ListAll")
   .AddParam("limit", 300L)
   .AddParam("limitOut", out IOutParam<long> limitOut)
   .Exec(r => rows = r.ToList<Model>());

long limitOutValue = limitOut.Value;

ctx.LoadStoredProc("dbo.ReturnBoolean")
   .AddParam("boolean_to_return", true)
   .ReturnValue(out IOutParam<bool> retParam)
   .ExecNonQuery();

bool b = retParam.Value;

ctx.LoadStoredProc("dbo.ListAll")
   .AddParam("limit", 1L)
   .ExecScalar(out long l);

Upvotes: 0

MORCHARD
MORCHARD

Reputation: 263

This solution provides methods that call a stored procedure and maps the returned value to a defined (non-model) entity. https://github.com/verdie-g/StoredProcedureDotNetCore

Microsoft address this issue: "SQL queries can only be used to return entity types that are part of your model. There is an enhancement on our backlog to enable returning ad-hoc types from raw SQL queries." https://learn.microsoft.com/en-us/ef/core/querying/raw-sql

And here is the issue tracked in GitHub: https://github.com/aspnet/EntityFramework/issues/1862

Upvotes: 0

Dhananjay
Dhananjay

Reputation: 53

How do I add input/output parameters?

I'm going to answer this particular question of yours.

Below is a TSQL stored procedure with two input and two output parameters

CREATE PROCEDURE [dbo].[yourstoredprocedure] 
-- Add the parameters for the stored procedure here
    @varone bigint
    ,@vartwo Date
    ,@varthree double precision OUTPUT
    ,@varfour bigint OUTPUT
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  -- YOUR CODE HERE
  SET @varthree = 10.02;
  SET @varfour = @varone;
  return;
END

Now To execute this stored procedure using Entity Framework Core

MyContext.Database
                   .ExecuteSqlCommand(@"EXECUTE [yourstoredprocedure] " +
                                       " {0} " +
                                       ", {1} " +
                                       ",@varthree OUTPUT " +
                                       ", @varfour OUTPUT ", dataOne, dataTwo, outputVarOne, outputVarTwo);


var outputResultOne= outputVarOne.Value as double?;
var outputResultTwo= outputVarTwo.Value as long?;

You can pass your input simply using parameterized query as above. You can also create named parameters. such as for output parameters, I've created two named parameters as -

var outputVarOne = new SqlParameter
        {
            ParameterName = "@varthree ",
            DbType = System.Data.DbType.Double,
            Direction = System.Data.ParameterDirection.Output
        };
var outputVarTwo = new SqlParameter
        {
            ParameterName = "@varfour ",
            DbType = System.Data.DbType.Int64,
            Direction = System.Data.ParameterDirection.Output
        };

And This is how using EF Core you execute a stored procedure with input and output parameters. Hope this helps someone.

Upvotes: 1

Related Questions