Running Postgres stored procedure with C#

user_6713015 0
Viewed 1607

I created a stored procedure stocks.public.daily_actions_full() that not receive or return something, it just inserts data from a view to a table.

After using using dbcontext scaffold all the tables and views were added except the stored procedures.

I saw that it's a known issue and the option to call stored procedures is to use ExecuteSqlCommand.

I tried the following code:

using (var db = new stocksContext())
{
    var rowsAffected = db.Database.ExecuteSqlCommand("CALL stocks.public.daily_actions_full()");
}

But I'm getting the following error:

error

Not sure what I'm missing here or if there's another way to call stored procedures.

Maybe adding it to the context file manually? (Couldn't find how)

Thanks!

entity-framework-core postgresql stored-procedures c#
2 Answers

As @Alexander Petrov wrote here:
Use ExecuteSqlRaw to solve it.
Thanks everyone for your help.

user_67130150

Make sure that you have installed package Npgsql.EntityFrameworkCore.PostgreSQL and Microsoft.EntityFrameworkCore.SqlServer

There is no overload ExecuteSqlCommand without any parameters. So try this

 var rowsAffected = db.Database
.ExecuteSqlCommand("Call stocks.public.daily_actions_full()", null);

UPDATE

You can try to use ExecuteSqlRaw but in this case you will need much more code

Create model for return result

public class SpResult
{
 public int RowsAffected {get; set;}
}

Then add this model to the dbcontext

  public virtual DbSet<SpResult> SpResults { get; set; }

   //and OnModelCreating

   modelBuilder.Entity<SpResult>(e =>
            {
                e.HasNoKey();
            });

Only after this you can get your result from SP


public string GetApproverCode(int ReqId)
    {
       var rowsAffectedObj = db.SpResults.ExecuteSqlRaw("Call stocks.public.daily_actions_full()", null)
              .ToList()
             .FirstOrDefault();
   
  var rowsAffected=0; 

 if(rowsAffectedObj !=null) rowsAffected= rowsAffectedObj.RowsAffected;
user_113922900
Related