MapValue to Postgres Enum Type

See original GitHub issue

I have been playing around with LINQ2DB so far, and I’m very much liking it. However, I am having troubles with mapping values as per the blog post listed here. In particular, given the following, I find that I end up raising NpgsqlExceptions depending on how I structure the code:

Database.tt: More or less the same as default, but I added the following line in between LoadPostgreSQLMetadata(...) and GenerateModel()

Tables["methods"].Columns["methodtype"].Type = "ModelMethodTypes";

Form1.cs: This is the minimal amount of code needed to reproduce this error:

using System;
using System.Windows.Forms;
using DataModel;
using LinqToDB;
using LinqToDB.Mapping;

public enum ModelMethodTypes
{
    [MapValue("TypeA")] TypeA,
    [MapValue("TypeB")] TypeB,
    [MapValue("TypeC")] TypeC,
    [MapValue("TypeD")] TypeD
}


namespace TestLinq2DB
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            DoModelThing(ModelMethodTypes.Capture);
        }

        private static int DoModelThing(ModelMethodTypes methodType)
        {
             using (var db = new TestDB())
            {
                var q = db.methods.Insert(() => new method
                {
                    methodtype = methodType,  // LINE 33 - This is where the problem happens
                    creationdate = DateTimeOffset.Now
                });

                return q;
            }           
        }
    }
}

Additionally, the following is the code I use to initialize my Postgres database (initdb.sql):

CREATE TYPE method_type AS ENUM (
    'TypeA',
    'TypeB',
    'TypeC',
    'TypeD'
);

CREATE TABLE methods (
    id bigserial PRIMARY KEY,
    methodtype method_type NOT NULL,
    creationdate timestamptz NOT NULL
);

The problem is that when I call the method like this, an NpgsqlException is raised, telling me the following:

Npgsql.NpgsqlException occurred
  BaseMessage=column "methodtype" is of type method_type but expression is of type text
  Code=42804
  ErrorCode=-2147467259
  File=src\backend\parser\parse_target.c
  HResult=-2147467259
  Hint=You will need to rewrite or cast the expression.
  InternalPosition=0
  Line=510
  Message=42804: column "methodtype" is of type method_type but expression is of type text
  MessageText=column "methodtype" is of type method_type but expression is of type text
  Position=77
  Routine=transformAssignedExpr
  Severity=ERROR
  Source=Npgsql
  StackTrace:
       at Npgsql.NpgsqlConnector.DoReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage)
       at Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage)
       at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
       at Npgsql.NpgsqlCommand.ExecuteNonQueryInternal()
       at Npgsql.NpgsqlCommand.ExecuteNonQuery()
       at LinqToDB.Data.DataConnection.ExecuteNonQuery() in i:\linq2db\Source\Data\DataConnection.cs:line 579
  InnerException: 

Normally, I would say that this is a problem with Npgsql, but I can’t confirm why it would be doing this. I say that in particular because I can swap out my LINE 33 in the Form1.cs file with the following:

methodtype = ModelMethodTypes.TypeA,

and the code works without fail. What could possibly be causing this problem, and what am I doing wrong to map to user-defined types using linq2db? A short-term solution would be to change my database schema to incorporate characters or integer values in place of an enum type, however I would much rather have the ability to map 1:1 my enumeration type in C# to an enum type in PostgreSQL. For that matter, using Npgsql without an ORM, the following can be done:

NpgsqlConnection.RegisterEnumGlobally<ModelMethodTypes>("method_type");

which registers the two together globally. In any case, I figured since this works one way (calling the enum explicitly) but not the other (passing the enum value in as a parameter first), this must be either a bug or I have mis-configured something horribly. I’ve tried looking at the documentation but beyond the blog post referenced above I cannot find anything that details how MapValue works or how I should go about tweaking it.

Issue Analytics

  • State:closed
  • Created 8 years ago
  • Comments:8 (2 by maintainers)

github_iconTop GitHub Comments

2reactions
hellznrgcommented, Jul 5, 2020

@ffandreassoroko I got it to work by telling linq2db to inline parameters.

In your DataConnection class, just use “InlineParameters = true;” in the constructor.

If InlineParameters is false, then the SQL will be generated like this:

DECLARE @Name as Text(5);
SET @Name = 'Simon';
DECLARE @Gender as Text(5);
SET @Gender = 'M';
Insert into person (name, gender) values (:name, :gender);

With InlineParameters true, it generates:

Insert into person (name, gender) values ('Simon', 'M');

Which is the correct Postgres syntax.

0reactions
garkushincommented, Sep 3, 2021

for those who are still looking for how to do it, I got it like this.

public enum PriorityLevel {...}
public class DataContext : DbContext 
{
...
        static DataContext()
        {
            var nameTranslator = NpgsqlConnection.GlobalTypeMapper.DefaultNameTranslator;

            NpgsqlConnection.GlobalTypeMapper.MapEnum<PriorityLevel>();

            MappingSchema.Default.SetDefaultFromEnumType(typeof(EdgeType), typeof(string));
            MappingSchema.Default.SetConverter<EdgeType, string>(c => nameTranslator.TranslateMemberName(c.ToString()));
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            builder.HasPostgresEnum<PriorityLevel>();
        }
...
}
Read more comments on GitHub >

github_iconTop Results From Across the Web

How to map JAVA enum to Postgresql Enum using ...
My application is using apache-thrift,hibernate/JPA(for ORM model),postgresql DB. Postgresql DB have defined enum using command. CREATE TYPE ...
Read more >
JPA — How To Map Java enum To PostgreSQL enum Type
Description: This application maps a Java enum type to PostgreSQL enum type via Hibernate Types library. Key points: install Hibernate Types library via...
Read more >
Enum Type Mapping
Creating your database enum. First, you must specify the PostgreSQL enum type on your model, just like you would with tables, sequences or...
Read more >
Map(key, value) | ClickHouse Docs
Map(key, value) data type stores key:value pairs. Parameters. key — The key part of the pair. String, Integer, LowCardinality, FixedString, UUID, Date, DateTime ......
Read more >
Postgres/SQLAlchemy/Alembic change datatype from enum to ...
Coding example for the question Postgres/SQLAlchemy/Alembic change datatype from enum to int and map value with using-postgresql.
Read more >

github_iconTop Related Medium Post

No results found

github_iconTop Related StackOverflow Question

No results found

github_iconTroubleshoot Live Code

Lightrun enables developers to add logs, metrics and snapshots to live code - no restarts or redeploys required.
Start Free

github_iconTop Related Reddit Thread

No results found

github_iconTop Related Hackernoon Post

No results found

github_iconTop Related Tweet

No results found

github_iconTop Related Dev.to Post

No results found

github_iconTop Related Hashnode Post

No results found