Dapper not map the return results from the function in PostgreSQL

Question

I am new to PostgreSQL and trying to get the result from the function that I just created into Dapper. the Dapper's Query function was successful, but the result is not being mapped to the object model.

Here is the code that I am using:

PostgreSQL function:

CREATE OR REPLACE FUNCTION GetUsers(Name VARCHAR)
    RETURNS TABLE (ID INTEGER, Username VARCHAR, Password VARCHAR, IsActive BOOLEAN)
AS
$BODY$
    SELECT ID, Username, Password, IsActive FROM Users WHERE Username = Name AND IsActive = true;
$BODY$
    LANGUAGE sql;

C# Code:

public List<User> GetUsers(string Name)
        {
            string Query = "SELECT GetUsers (@Name)";

            var DynamicParams = new DynamicParameters();

            DynamicParams.Add("@Name", Name);

            using (var Conn = new NpgsqlConnection(ConnectionString))
                return Conn.Query<User>(Query, param: DynamicParams, commandType: CommandType.Text).ToList();
        }

public class User
    {
        public int ID { get; set; }

        public string Username { get; set; }

        public string Password { get; set; }

        public bool IsActive { get; set; }
    }

Is the above code that I am writing is correct? Do the "Returns Table" will be returning records of Users table (more than 1 record) or it will return 1 record only?

When I am trying in PostgreSQL with the same parameter value, it returns the result as expected:

SELECT GetUsers('Developer');

Result:

Result from PostgreSQL

"not being mapped" well what do you get, this looks like it should work — Charlieface
– Charlieface, Commented Jul 11, 2021 at 16:06
As a test, try replacing the query in your C# code with the query from your function. If it works, the problem is likely that Dapper doesn't know what to do with a table result from your database. — Patrick Tucci
– Patrick Tucci, Commented Jul 11, 2021 at 16:27
Not a postgresql guy, but shouldn't the query be SELECT * FROM GetUsers(@Name)? — Robert McKee
– Robert McKee, Commented Jul 12, 2021 at 2:15

Developer · Accepted Answer · 2021-07-12 02:08:20Z

2

For those who are wondering, the above query which is:

string Query = "SELECT GetUsers (@Name)";

will returns as object with the name of "GetUsers" and will consists of multiple values, so you have to add the following code in the model to get the Dapper map it correctly.

public class User
    {
        /* the rest of the model */
        public object GetUsers { get; set; }
    }

Or else you can do the following query for Dapper to map it correctly without adding the "GetUsers" object.

SELECT * FROM GetUsers (@Name)

The above query will return columns instead of single object with multiple values as what has been written on the function in PostgreSQL, in this case the columns are: ID, Username, Password, IsActive

answered Jul 12, 2021 at 2:08

Developer

211 bronze badge

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

Collectives™ on Stack Overflow

Dapper not map the return results from the function in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related