Dapper Query "QueryAsync" returning null columns

Question

I'm attempting to return a List of entities using dapper and a MySQL Stored Procedure, but it returns the data with null values for some reason. It returns the list of entities, but only displays their Id -- CourseName and HoleLayouts are always null. It's a simple query, which is why it is very confusing to me, and it also works for different entities/database tables. I changed the column for course_name to be the type of VARCHAR(255) since originally it was VARCHAR(45) which didn't make a difference. Even with debugging, I can see through the stack trace that the course names are null through the repositories return statement but not their id.

I'll have the code below to try and help further explain.

Entity

public class Course
{
    public int Id { get; set; }
    public string CourseName { get; set; } = null!;
    public ICollection<HoleLayout> HoleLayouts { get; set; } = null!;
}

Repository/Query

    public async Task<IEnumerable<Course>> FindAllAsync()
    { 
        using var connection = _dataAccessor.CreateConnection();

        var courses = await connection.QueryAsync<Course>("GetAllCourses",
            commandType: CommandType.StoredProcedure);

        return courses;
    }

Stored Procedure

create
    definer = root@localhost procedure GetAllCourses()
BEGIN
    SELECT * FROM courses;
END;

JSON Response

[
    {
        "id": 1,
        "courseName": null,
        "holeLayouts": null
    }
]

Course schema

I think .ToList(); is needed, var courses = await connection.QueryAsync<Course>("GetAllCourses", commandType: CommandType.StoredProcedure).ToList(); stackoverflow.com/a/41698348 — wei
– wei, Commented Dec 12, 2022 at 7:19
Can you show the schema of courses? does it have a courseName column? note: Dapper can't help you with holdLayouts - that's complex structured data, which Dapper doesn't do. Also: what data provider is this (in case it is relevant - I simply don't recognize that SQL variant) ? edit: I would expect course_name to work, but again: seeing the schema would help — Marc Gravell
– Marc Gravell, Commented Dec 12, 2022 at 10:13
As a tip: never use * in SQL; if that SP is meant to return the Id, CourseName and something else: say that explicitly - this avoids problems where "safe" changes to the schema (like adding a column) change the way your application works; it also avoids fetching unnecessary data - perhaps a few large text or image columns - over the network, just to be ignored — Marc Gravell
– Marc Gravell, Commented Dec 12, 2022 at 10:15
@MarcGravell Thanks Marc, I updated the post to include an image of the schema. It has a course_name column. I'm using MySQL if that helps... I use the same query type to fetch all users and it works fine. Also, regarding the tip to never use *, would using DTOs fix that issue? — charbs29
– charbs29, Commented Dec 12, 2022 at 18:28
@charbs29 no, using DTOs doesn't fix the advice re *; on the same; weird, I would have expected that to work - maybe try adding an underscore in the C#, just to see if that helps? — Marc Gravell
– Marc Gravell, Commented Dec 12, 2022 at 23:06

charbs29 · Accepted Answer · 2022-12-15 03:41:03Z

1

As @MarcGravell hinted, it was in fact an issue with the column name in the schema. The solution was for the Entity value name to match the column name.

answered Dec 15, 2022 at 3:41

charbs29

1493 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

Dapper Query "QueryAsync" returning null columns

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