CODEWITHSUNDEEP
c#.netpostgresqldapper
Developer
Developer

Reputation: 11

Dapper not map the return results from the function in PostgreSQL

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

Upvotes: 1

Views: 1436

Answers (1)

Developer
Developer

Reputation: 21

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

Upvotes: 2

Related Questions