Dapper enum mapping

Question

I need help for mapping enums with Dapper and Oracle.

I have a field in Oracle type NUMBER (1) and must turn into an enum in my entity.

public Status Status { get; set; }

Status is a enum:

public enum Status
{
    [Description("Inactive", "0")]
    Inactive = 0,

    [Description("Active", "1")]
    Active = 1,
 }

How to make the mapping using Dapper?

Euphemiah

Dapper will do this automatically, just ensure your query returns the number field with the same name as your Status property in the entity class – Wagram

Answer 1

Enums should JustWork™ for either integer or string representations. If it isn't working, you'll have to be more specific about any exception that it is throwing. For example, thinking aloud and pure speculation, but: IIRC Oracle has a habit of wanting to treat numbers as 64-bit, and I wonder if the enum mapping code handles all flavors of numeric conversion. If it doesn't, then that's a bug.

So: does it work?

Tocsin

re "Enums should JustWork" . I'm struggling to make it so. Just a standard enum definition, trying to insert into mysql table where the field is defined as INT(11). keep getting a SQL error telling me the field cannot be NULL. So Dapper is mapping my enum to null instead of an int? – Plaything

@Plaything do you have an example? – Tocsin

On Further Testing.. my problem wasn't an ENUM problem. I switched to an int data type in my class and had the identical problem. I was using the Devart MySql driver. I switched to use the MySqlConnector async driver downloaded from github everything started working. Both enum's and int's. SO.. there is definitely something up with the Devart provider. But I'm OK with MySqlConnector, so my problem is solved. – Plaything

I am using Dapper with a Mysql db, and while the insert command works, the update command is not. My Enum in the db are string values (Mysql), but apparently, Dapper is trying to pass numerical values to them. – Biodegradable

There is, apparently, an exception to the enum mapping rule when the type parameter for, for example, the Query<T>() method is itself an enum: I tried selecting string values from a column and have Dapper return it as an IEnumerable of an enum, but it throws an ArgumentException, claiming "The value passed in must be an enum base or an underlying type for an enum, such as an Int32". I have checked the output of the query and ensured it only returns string values that match the enum's field names. Kind of awkward to have to resort to Query<string>().Select(Enum.Parse) in only one use case – Dentil

Hmm this should still be considered a bug, Dapper doesn't work converting from Enum to String for inserts into the database which makes it annoying to work with in PostgreSQL. It works fine pulling data out as string > enum. But there seems to be no way to get enums to cast to strings instead of ints. – Schaumberger

Answer 2

Just to be explicit with the sound advice in the other answers. Your database might have the data stored in a column called StatusId whereas you object's property might simply be Status.

I typically get around this by using an alias in the SQL query:

SELECT StatusId AS Status FROM Table

Dapper implicitly understands how to map an int database field to a C# enum. I usually give my enums explicit values to make things as clear as possible, e.g.

public enum Status
{
   Active = 1,
   Inactive = 2
}

Jolin

Your entity has a enum Status property ? Works for Insert-Update too ? – Plaintive

Answer 3

I have not been able to get nullable enums to work when using dapper.

To get around this I create two classes: an object model and a SQL model.

The SQL model is a simple version of the object model, with enums as the strings by which they are saved in the database. The object model has a constructor taking a SQL model as input.

class ThingSQLModel
{
    public int? Id;

    public String? ThingType;

 ....


}

class Thing
{
    public int? Id;

    public EnumThingType? ThingType{ get; set; }

 ....

    public Thing(ThingSQLModel)
    {
        Id = output.Id;

        ThingType= EnumHelper.TryParseNullable<EnumThingType>(output.ThingType);
    }
}

for TryParseNullable<EnumThingType> see : Parsing value into nullable enumeration

Perhaps there is a better approach by explicitly overriding the default parsing for this type? hmm. I'll experiment and comeback with an answer.

Explication

Answer 4

In addition to the existing answers, there is another special case.

I found that Dapper maps enums automatically if the enum maps to a property inside a model and your Query<Model> is of that model type.

However, when you Query<MyEnum> directly, it throws an exception and expects an int32 from the database.

To get around this I just made a little EnumWrapper like so:

public class EnumWrapper<T> where T:struct
{
    public T Value { get; set; }
}

And then proceed to query like so:

    var sql = @"SELECT E.[Status] AS [Value] FROM [Exmaple].Example E";

    var connection = await this.GetConnection();
    
    var status = await connection.QuerySingleAsync<EnumWrapper<TheEnum>>(sql);

    return status.Value;

It's important that the SQL alias matches the property in the EnumWrapper. In this case it's Value .

Fraenum

Not working for me, that returns everytime the first value of the enum. .QueryAsync<string>(...).Select(i => Enum.Parse<TheEnum>(i)) does the trick. – Engedus