How to get data from PostgreSQL function in ASP.Net core

Question

I am beginner to asp.net core. I have a working function in my posgres called "GetDriverStatusReport" will return some datas. I would like to retrieve those values ​​in my Asp.Net core application. I tried researching on this but I couldn't get a proper solution. Can someone please tell me the correct of calling the stored procedure in Asp.net Core. Thanks in advance

Here is my Function

CREATE OR REPLACE FUNCTION GetDriverStatusReport(ref refcursor) RETURNS refcursor AS $$
BEGIN
  OPEN ref FOR (WITH union_tbl AS(
SELECT
   DR."DriverId" AS "Driver Id",
   DR."DriverName" As "Driver Name",                
   0 as "RiderIncome",
   0 as "Payable To Rider",
   0 as "Recievable Balance",
   DR."Amount" as "Amount Paid"
FROM "DriverPaymentReferences" as DR            
UNION ALL
SELECT
   DF."DriverId" AS "Driver Id",
   DF."DriverName" As "Driver Name",
   DF."RiderIncome" as "RiderIncome",
   0 as "Payable To Rider",
   0 as "Recievable Balance",
   0 as "Amount Paid"
FROM "DeliveryFinances" as DF 
UNION ALL
SELECT
   TF."DriverId" AS "Driver Id",
   TF."DriverName" As "Driver Name",
   0 as "RiderIncome",
   TF."PayableToRider" as "Payable To Rider",
   TF."RecievableBalance" as "Recievable Balance",
   0 as "Amount Paid"
FROM "TaxiFinances" as TF)
Select 
   "Driver Id", 
   "Driver Name",
   case when 
     ((Sum("Recievable Balance") - SUM("Payable To Rider")) -Sum("RiderIncome")- 
   SUM("Amount Paid")) > 0
   then 'CR'
   else 'DR' end as "Cash/Debit",
   ((Sum("Recievable Balance") - SUM("Payable To Rider")) -Sum("RiderIncome")- 
   SUM("Amount Paid")) AS "Amount"
FROM union_tbl
GROUP BY "Driver Id","Driver Name" );
  RETURN ref;                                                      
END;
$$ LANGUAGE plpgsql;

Answer 1

In my case I wrote an function in postgres that looks like this:

 create or replace function fn_get_product__by_id (
  productId int
) 
    returns table (
        ProductId uuid,
        ProductName text,
        ShortDesc text,
        ProductDesc text,
        ProductQualityReview text,
        ProductQualityScore text,
        PeopleRating int,
        BrandId int,
        VendorId int,
        SKU text,
        Price double PRECISION,
        PercentageDiscount double PRECISION ,
        ProductSizeHeight double PRECISION ,
        ProductSizeLenght double PRECISION ,
        ProductSizeWidth double PRECISION ,
        BoxOrFolder boolean,
        ProductSizeWithBoxHeight double PRECISION ,
        ProductSizeWithBoxLenght double PRECISION ,
        ProductSizeWithBoxWidth double PRECISION ,
        ProductWeight double PRECISION ,
        ProductWeightWithBox double PRECISION ,
        IsAvailable boolean
    ) 
    language plpgsql
as $$
begin
    return query 
            SELECT *
            FROM public."Product"
            WHERE ProductId = productId;

end;
$$

It can be called from Postgres like this:

SELECT * FROM fn_get_product__by_id (1)

On the Net Core side I am working with Entity Framework. I have a Product entity like this:

public class Product
{
    public Guid ProductId { get; set; }
    public string? ProductName { get; set; }
    public string? ShortDesc { get; set; }
    public string? ProductDesc { get; set; }
    public string? ProductQualityReview { get; set; }
    public string? ProductQualityScore { get; set; }
    public int PeopleRating { get; set; }
    public int BrandId { get; set; }
    public int VendorId { get; set; }
    public string? SKU { get; set; }
    public double Price { get; set; }
    public double PercentageDiscount { get; set; }
    public double ProductSizeHeight { get; set; }
    public double ProductSizeLenght { get; set; }
    public double ProductSizeWidth { get; set; }
    public bool BoxOrFolder { get; set; }
    public double ProductSizeWithBoxHeight { get; set; }
    public double ProductSizeWithBoxLenght { get; set; }
    public double ProductSizeWithBoxWidth { get; set; }
    public double ProductWeight { get; set; }
    public double ProductWeightWithBox { get; set; }
    public bool IsAvailable { get; set; }        
}

And a DbContext Like this:

public class ApplicationDbContext : DbContext
{
    private readonly DbContextOptions<ApplicationDbContext> options;

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
        this.options = options;
    }

    public DbSet<Product> Product { get; set; }
}

So in my Product Repository I have somethig like this:

public async Task<List<Product>> GetProductsByCtegoryAsync(int subCategoryId)
{
    string sqlQuery = $"SELECT * FROM fn_get_product__by_id ({subCategoryId})";
    return await this.dbContext.Product.FromSqlRaw(sqlQuery).ToListAsync();

}

That staement returns a single product. If you want you can just avoid the productId parameter and then it will give you all the Products in the table.

In my case,neither, the "EXE" or "EXECUTE" didn't work well.

Answer 2

Well, You can try this way,

Let's way I have a stored procedure called SP_GetAllMSManager like this

PG SQL

CREATE OR REPLACE FUNCTION GetWSManager() 
AS $$

BEGIN
      SELECT DISTINCT  WSManager FROM WsEmployee ORDER BY WSManager 
      COMMIT;      
END;
$$ LANGUAGE plpgsql;

C# ASP.NET Core:

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres;Password=pwd;Database=postgres;");

conn.Open();

// Passing PostGre SQL Function Name
NpgsqlCommand command = new NpgsqlCommand("EXE GetEmployeePrintInfo", conn);

// Execute the query and obtain a result set
NpgsqlDataReader reader = command.ExecuteReader();

// Reading from the database rows
List<string> listOfManager = new List<string>();

while (reader.Read())
{
    string WSManager = reader["WSManager"].ToString(); // Remember Type Casting is required here it has to be according to database column data type
    listOfManager.Add(WSManager);
}
reader.Close();

command.Dispose();
conn.Close();

OUTPUT:

If context is the same:

If you want to use same context then you can try this way:

C# ASP.NET Core:

  var printJobList =  _context.PrinterJobs
                     .FromSql("EXECUTE GetEmployeePrintInfo")
                     .ToList();

Note: This is belongs to _context.PrinterJobs means store procedure only fetch data from PrinterJobs table. So I can do above way. But if your stored procedure contains other entities data from join, then first approach can be convenient. You can have a look official document here

PG SQL:

CREATE OR REPLACE FUNCTION GetEmployeePrintInfo() 
AS $$
BEGIN
       SELECT DISTINCT PrinterId ,PrinterName,PrintedBy,TotalPrint FROM PrintJob 
       COMMIT;     
END;
$$ LANGUAGE plpgsql;

Hope it would help you accordingly.