Dapper - How to Map Resultset From A Postgres Function Resultset Column To A Class Property

Question

I am trying to learn how to use C# with dapper, mainly to call a postgreSQL function. I am struggling with mapping a column in the resultset to a class property.

My Class is defined as:

public class CourseUnit
{
   public int unitId { get; set; }
   public string description { get; set; }
}

The stored procedure is invoked as shown in the draft listing below. The stored procedure resultset contains two columns:

1. UnitID
2. UnitName

I am having difficulties mapping UnitName from stored procedure to the description property of my class. The code calls the procedure and produces a resultset with values for UnitID but the description values are null. How do I map UnitName to the description property of my class?

public async Task<IEnumerable<CourseUnit>> GetCourseUnitsAsync(int courseId)
{
    using (var connection = new NpgsqlConnection(_connectionString))
    {
        connection.Open();

        Console.WriteLine($"About to execute the course units query for course {courseId}");

        try 
        {
            var p = new DynamicParameters();
            p.Add("@course_id", courseId);
            var result = await connection.QueryAsync<CourseUnit>(@"coursemanagement.select_course_units",
            p,
            commandType: CommandType.StoredProcedure);
            Console.WriteLine($"Total results of course units are {result.AsList().Count}");
            return result;
        }
        catch(Exception)
        {
            throw new KeyNotFoundException($"No Units found for course {courseId}");
        }

        throw new KeyNotFoundException();
    }
}

I have tried invoking the stored procedure as follows:

var result = await connection.QueryAsync<CourseUnit>(@"SELECT 'UnitID' as unitid, 'UnitName' as description FROM coursemanagement.select_course_units",
    new { courseId });
Console.WriteLine($"Total results of course units are {result.AsList().Count}");
return result;

However, I receive an exception =>

Exception encountered while retrieving course units 42P01: relation "coursemanagement.select_course_units" does not exist

Answer 1

I went for updating the stored procedure to introduce a column alias with the same name as the POCO class property.

I have included a link to the following stackoverflow resource that details other possibilities:

• This post suggests using dynamic typed query with LINQ to map between resultset and POCO class.
• Use Dapper custom property to type mappers as detailed here

PostgreSQL allows the resultset of a function to be queried, e.g.

SELECT UnitID, UnitName from myStoredProc(parameterValue);

If using PostgreSQL then could use the ExecuteReader class to run raw SQL code to select from the stored procedure and prepare DataTable, but not ideal.