Enum tables PostgreSQL

[QifiMicke]

I have a table status

CREATE  TABLE  types.status
(id int PRIMARY KEY,
name varchar(10))

INSERT INTO "types"."Status" (id, "name")
VALUES(1, 'Todo'), (2, 'InProgress'), (3, 'Done');

in tt file

 new EnumerationSettings
        {
            Name       = "Status",
            Table      = "types.status",
            NameField  = "id",
            ValueField = "name"
        },

I have also added config into

Settings.AddEnumDefinitions = delegate(List<EnumDefinition> enumDefinitions)

enumDefinitions.Add(new EnumDefinition {
    Schema = Settings.DefaultSchema,
    Table = "Request", 
    Column = "statusId", 
    EnumType = "types_Status" });

Keep in mind that I have two schemas types public

Public is the schema that I want to use for these.

reverse poco does not create an enum value object when saving tt file

 public partial class types_Status
 {
     public int Id { get; set; } // id (Primary key)
     public string Name { get; set; } // name (length: 20)
}

for request

public partial class Request
{
    public int Id { get; set; } // id (Primary key)
    public string Name { get; set; } // name (length: 255)
    public string CdsId { get; set; } // cdsId (length: 10)
    public int? VehiclePlatformId { get; set; } // vehiclePlatformId
    public int? VehicleProjectId { get; set; } // vehicleProjectId
    public types_Status? StatusId { get; set; } // statusId
    public int? SupplierCompanyId { get; set; } // supplierCompanyId
    public string CreatedBy { get; set; } // createdBy (length: 10)
    public DateTime CreatedDate { get; set; } // createdDate
    public string UpdatedBy { get; set; } // updatedBy (length: 10)
    public DateTime UpdatedDate { get; set; } // updatedDate
}

expected

public Enum types_Status
 {
     Todo = 1
     InProgress = 2
     Done = 3
}

[mrmyroll2]

in tt file

Based on the tutorial, I think the value should be setup like this

new EnumerationSettings
{
            Name       = "Status",
            Table      = "types.status",
            NameField  = "name",
            ValueField = "id"
},

[sjh37]

I haven't tested as much with Postgres as I should. Let me know if this works for you.

[mrmyroll2]

I just tested generating the enum with Postgres, but it doesn’t seem to work. Instead of an enum, it generated a POCO class.

[QifiMicke]

It does not work still

[QifiMicke]

I haven't tested as much with Postgres as I should. Let me know if this works for you.

it only generates POCO class

[QifiMicke]

Any news or progress on this?

[mfaghfoory]

Hi @sjh37, by debugging the tt file, I realized that "EnumSQL" in EF.Reverse.POCO.v3.ttinclude (line number 14446) generates a wrong query in two senses. The generated query is like this SELECT "name" as "NameField", "id" as "ValueField", "" as "GroupField", * FROM "types.Status";

The first issue is that it puts the schema name inside the same "" as table name which is wrong in Postgres and the second issue is that, although we haven't populated GroupField property, it is included it in the query which is again wrong.

Proposed change:

protected override string EnumSQL(string table, string nameField, string valueField, string groupField)
        {
            string formattedTable = table.Contains(".")
                ? $@"""{table.Split('.')[0]}"".""{table.Split('.')[1]}"""
                : $@"""{table}""";

            if (!string.IsNullOrEmpty(groupField))
            {
                return $@"SELECT ""{nameField}"" AS ""NameField"", ""{valueField}"" AS ""ValueField"", ""{groupField}"" AS ""GroupField"", * FROM {formattedTable};";
            }
            else
            {
                return $@"SELECT ""{nameField}"" AS ""NameField"", ""{valueField}"" AS ""ValueField"", * FROM {formattedTable};";
            }
       }

I hope it helps