Error: column "Gender" is of type enum but expression is of type text.

Are you getting this error when you are using enum datatype in PostgreSQL database? This blog post explains how to fix the error with C# and Dapper. The following environment is used for this post:

ASP.NET Core 1.0.1

Postgresql 9.5.4 database

Npgsql 3.1.9

Dapper 1.50.2

Visual Studio 2015 update 3

For the demo, suppose in database, Gender column is enum type (say enumgender) and we are going to insert using Dapper:

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string insertQuery = @"INSERT INTO [dbo].[Customer]([FirstName], [LastName], [Gender],[CreatedOn]) VALUES (@FirstName, @LastName, @Gender, @CreatedOn)";

    var result = db.Execute(insertQuery, new
    {
        customerModel.FirstName,
        customerModel.LastName,
        customerModel.Gender,
        CreatedOn = DateTime.Now
    });
}

It will throw the error due to mismatch of enum type. To fix this:

1. In the SQL query, add sufffix "::enumtype" with parameter value (i.e. @Gender::enumgender).

2. In customerViewModel, If Gender is property of C# Enum type then you have to convert into string. Make sure it must matches with database enum value.

using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["myDbConnection"].ConnectionString))
{
    string insertQuery = @"INSERT INTO [dbo].[Customer]([FirstName], [LastName], [Gender],[CreatedOn]) VALUES (@FirstName, @LastName, @Gender::enumgender, @CreatedOn)";

    var result = db.Execute(insertQuery, new
    {
        customerModel.FirstName,
        customerModel.LastName,
        customerModel.Gender.ToString(),
        CreatedOn = DateTime.Now
    });
}

Hope It helps and saves your time.