Running Postgres stored procedure with C#

Question

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:

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!

ExecuteSqlCommand was removed from EF core after 3.1. There is ExecuteSqlCommandAsync — Neil
– Neil, Commented May 3, 2021 at 15:34
I'm still getting the error I mentioned above. 'DatabaseFacade' does not contain a definition for 'ExecuteSqlCommandAsync' and no accessible extension method 'ExecuteSqlCommandAsync' accepting a first argument of type 'DatabaseFacade' could be found — Amirp96
– Amirp96, Commented May 19, 2021 at 15:45
Do you have using System.Data.Entity; at the top of the file? — Neil
– Neil, Commented May 19, 2021 at 16:48
There is no ExecuteSqlCommand method in the DatabaseFacade class. There are ExecuteSqlRaw and ExecuteSqlInterpolated methods (and their asynchronous versions). — Alexander Petrov
– Alexander Petrov, Commented May 19, 2021 at 17:26

Amirp96 · Accepted Answer · 2021-05-24 21:58:18Z

3

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

answered May 24, 2021 at 21:58

Amirp96

911 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Serge · Accepted Answer · 2021-05-24 21:47:19Z

0

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;

edited May 24, 2021 at 21:47

answered May 3, 2021 at 15:35

Serge

44.4k4 gold badges26 silver badges53 bronze badges

9 Comments

Amirp96 Over a year ago

I'm still getting the error I mentioned above.

'DatabaseFacade' does not contain a definition for 'ExecuteSqlCommand' and no accessible extension method 'ExecuteSqlCommand' accepting a first argument of type 'DatabaseFacade' could be found

Serge Over a year ago

Can you post your startup pls? What kind of dbcontext are you using?

Ctznkane525 Over a year ago

isnt the command type required and not the "exec" part

Serge Over a year ago

Yes, you certainaly need it to execute a strored procedure. Exec or execute

Serge Over a year ago

Sorry it is fo MS Sql Server . But for Postgresql is used Call

|

Collectives™ on Stack Overflow

Running Postgres stored procedure with C#

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related