How to Access Dapper Results When Using Dynamic Due to Enum

Question

I have a Dapper DB call that has a column that maps to an enum on the type. From what I understand, I have to query using dynamic because the query can't map to my Request type since Status is an enum on it. So I tried using this:

public async Task<Request> GetByRequestId(long id)
{
    string sql = "SELECT * FROM Request WHERE ID = @id";

    using (var connection = new SqlConnection(_connectionString))
    {
        // Need dynamic here because of the conversion of the Status column as an enum.
        // If someone has a better way...
        dynamic dynamicRequest = await connection.QueryFirstOrDefaultAsync(sql, new { id });
        if (dynamicRequest == null) { return default; }

        var request = new Request
        {
            Id = dynamicRequest.ID,
            Status = (RequestStatus)Enum.Parse(typeof(RequestStatus), dynamicRequest.Status)
        };

        return request;
    }
}

However, I can't access any of the properties on the dynamic object, called dynamicRequest. When debugging, I can see its value like this:

{{DapperRow, ID = '8', FooID = '12', Status = 'N'}}

How can I access ID, or FooID, or Status? This doesn't work:

dynamicRequest.ID
dynamicRequest[0]
dynamicRequest["ID"]
// and many other tries...

This is the error I get if I try using the actual class, instead of dynamic:

System.Data.DataException HResult=0x80131501 Message=Error parsing column 4 (Status=N - String) ArgumentException: Requested value 'N' was not found.

And this is the enum. I just added the JsonConverter attribute to try and get this to work with the actual Request class.

[JsonConverter(typeof(StringEnumConverter))]
public enum RequestStatus
{
    [EnumMember(Value = "N")]
    New = 1,

    [EnumMember(Value = "A")]
    Approved = 2,

    [EnumMember(Value = "C")]
    Cancelled = 3,

    [EnumMember(Value = "D")]
    Declined = 4,

    [EnumMember(Value = "P")]
    Pending = 5,
}

Here is a screenshot of dynamicRequest and an attempt at accessing the ID property.

Id personally change your string field to integer in the db and ditch the dynamic completely (assuming you can) — TheGeneral, Feb 12 '20 at 02:48
I may be able to do that, but the code in my post would work if I could just get access to `dynamicRequest`. I can see the values in the debugger. — Bob Horn, Feb 12 '20 at 02:52
No, I want to create a proper `Request` object from the return value of the query and have the method return it. If I figure out how to access the values in the debugger (immediate window), that will help me do it in code. — Bob Horn, Feb 12 '20 at 03:07
What happens if you take out the Enum parse hooey, does it then map your other fields? — Jim, Feb 12 '20 at 13:16
No. The enum parse isn't the issue (yet). I can't access any of the values on `dynamicRequest`. — Bob Horn, Feb 12 '20 at 13:55

Jim · Accepted Answer · 2020-02-12T14:23:31.840

2

Your problem is the Enum.Parse doesn't honor your EnumMember attributes.

This works however.

public static async Task<Request> GetByRequestId(long id)
{
    string sql = "SELECT * FROM Request WHERE ID = @id";

    using (var connection = new SqlConnection(_connectionString))
    {
        // Need dynamic here because of the conversion of the Status column as an enum.
        // If someone has a better way...
        dynamic dynamicRequest = await connection.QueryFirstOrDefaultAsync(sql, new { id });
        if (dynamicRequest == null) { return default; }

        var request = new Request
        {
            Id     = dynamicRequest.ID,
            Status = JsonConvert.DeserializeObject<RequestStatus>($"\"{dynamicRequest.Status}\""),
        };

        return request;
    }
}

Running I get...

Then the mapped Request...

This is what my database table looks like...

edited Feb 12 '20 at 14:23

answered Feb 12 '20 at 13:35

Jim

4,910
4
32
50

The problem isn't trying to parse the status. I can't even get to that. From my post: However, I can't access any of the properties on the dynamic object, called dynamicRequest. When debugging, I can see its value like this: {{DapperRow, ID = '8', FooID = '12', Status = 'N'}} – Bob Horn Feb 12 '20 at 13:42
1

Ah, you are probably right. I actually just accessed the ID property from `dynamicRequest` when I had it on its own line. Let me see about getting the `Status` property your way now... – Bob Horn Feb 12 '20 at 14:27

How to Access Dapper Results When Using Dynamic Due to Enum

1 Answers1