How to pass a row as a parameter to a Postgres function using .net core DBFunction

Question

Passing pgsql table object as parameter using c# .net core?

HI in pgsql we can pass table row as parameter , I want to know how we can use it in .net core?

like below function takes some parameters and give its functionality

   -CREATE OR REPLACE FUNCTION public."GetUserName"(u "User_", pmed "Pharmacy", puser "Pharmacy", m "Medication")
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
BEGIN
    return (
        case when u."Id" > 0 then
            (
                u."FirstName" || ' ' ||coalesce(u."LastName", '')
                || (
                    case when u."Pharmacy_Id" > 0 then
                            (case when puser."PharmacyId" > 0 then ' (' || puser."PharmacyName" || ') ' else '' end)
                    else '' end
                )
            )
        when coalesce (u."Id" , 0) = 0 then pmed."PharmacyName" else '' end
    );
END
$function$
;

I want to call this function using .net core ef core without dynamic queries using DbFunctions appraoch

Shay Rojansky · Accepted Answer · 2020-11-30 15:14:29Z

1

Rather than trying to pass an entire row as a parameter to a function - which usually wouldn't be very efficient - try passing the primary key of that row instead. Your function can then perform any queries or updates on that row as usual.

In general, in relational databases a row doesn't exist as an entity you pass around or manipulate as such (although PostgreSQL does have 1st-class support for composite types, which also back tables).

answered Nov 30, 2020 at 15:14

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

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

4 Comments

Ali Hussain Over a year ago

yes but I not want to pass ids for many records and it will check db everytime

Ali Hussain Over a year ago

I just want to pass whole row data to be used only in function

Shay Rojansky Over a year ago

As I wrote above, PG supports the notion of "composite types", which can be used this way. These aren't supported in EF Core (github.com/npgsql/efcore.pg/issues/22).

Shay Rojansky Over a year ago

I'll just say that it's somewhat questionable (especially in EF Core) to send row values to a function that doesn't actually access any table, but just does something with what you send it and returns the result... You may just as well perform the calculation client-side.

Collectives™ on Stack Overflow

How to pass a row as a parameter to a Postgres function using .net core DBFunction

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related