DEV Community

Masui Masanori
Masui Masanori

Posted on

[Entity Framework Core][PostgreSQL][C#] Using long identifier

Intro

When I migrated my database by Entity Framework Core, I got an exception.
That was because one foreign key was too long.

So I will try using long names as tables and columns in this time.

Environments

  • .NET 6.0.101
  • Microsoft.EntityFrameworkCore ver.6.0.1
  • Microsoft.EntityFrameworkCore.Design ver.6.0.1
  • Npgsql.EntityFrameworkCore.PostgreSQL ver.6.0.2
  • NLog.Web.AspNetCore ver.4.14.0
  • PostgreSQL ver.14.1

StopTheHostException

When I add migrations or update database, I get exceptions like below.

2022-01-20 00:05:28.1349||ERROR|Program|Stopped program because of exception Microsoft.Extensions.Hosting.HostFactoryResolver+HostingListener+StopTheHostException: Exception of type 'Microsoft.Extensions.Hosting.HostFactoryResolver+HostingListener+StopTheHostException' was thrown.
   at Microsoft.Extensions.Hosting.HostFactoryResolver.HostingListener.OnNext(KeyValuePair`2 value)
   at System.Diagnostics.DiagnosticListener.Write(String name, Object value)
   at Microsoft.Extensions.Hosting.HostBuilder.Build()
   at Microsoft.AspNetCore.Builder.WebApplicationBuilder.Build()
   at Program.<Main>$(String[] args) in /home/example/Documents/workspace/BookshelfSample/BookshelfSample/Program.cs:line 24
Enter fullscreen mode Exit fullscreen mode

I think this is because this issue.

So I ignore the exceptions now.

Program.cs

using BookshelfSample.Books;
using BookshelfSample.FkSamples;
using BookshelfSample.Models;
using Microsoft.EntityFrameworkCore;
using NLog.Web;

var logger = NLogBuilder.ConfigureNLog("Nlog.config").GetCurrentClassLogger();
try
{
    var builder = WebApplication.CreateBuilder(args);
    builder.Host.ConfigureLogging(logging =>
    {
        logging.ClearProviders();
        logging.AddConsole();
    })
    .UseNLog();
    builder.Services.AddControllers();
    builder.Services.AddDbContext<BookshelfContext>(options =>
                    options.UseNpgsql(builder.Configuration["DbConnection"]));
    builder.Services.AddScoped<ILanguages, Languages>();
    builder.Services.AddScoped<IAuthors, Authors>();
    builder.Services.AddScoped<IBooks, Books>();
    builder.Services.AddScoped<IBookService, BookService>();
    builder.Services.AddScoped<ILongNameService, LongNameService>();
    var app = builder.Build();

    app.UseStaticFiles();
    app.UseRouting();

    app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllers();
    });
    app.Run();
}
catch (Exception ex)
{
    //  Ignore StopTheHostException.
    string type = ex.GetType().Name;
    if (type.Equals("StopTheHostException", StringComparison.Ordinal))
    {
        throw;
    }
    logger.Error(ex, "Stopped program because of exception");
}
finally {
    NLog.LogManager.Shutdown();
}
Enter fullscreen mode Exit fullscreen mode

Setting long name

Tables

Because PostgreSQL identifiers are limited 63 bytes, if table names or foreign keys, etc. are named over 63 charactors, the names will be omitted.

I couldn't find these limitations in the documents of EF Core and Npgsql.

SuuuuupppppeeeeerrrrrLooooonnnnngggggName.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace BookshelfSample.FkSamples;
[Table("sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee")]
public record SuuuuupppppeeeeerrrrrLooooonnnnngggggName
{
    [Key]
    [Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; init; }
    [Required]
    [Column("name")]
    public string Name { get; init; } = "";
    public List<ReferSample> ReferSamples { get; init; } = new List<ReferSample>();

    public static SuuuuupppppeeeeerrrrrLooooonnnnngggggName Create(SuuuuupppppeeeeerrrrrLooooonnnnngggggName item)
    {
        return new SuuuuupppppeeeeerrrrrLooooonnnnngggggName
        {
            Name = item.Name,
        };
    }
}
Enter fullscreen mode Exit fullscreen mode

[MigrationFile] AddSuperLongName.cs

using Microsoft.EntityFrameworkCore.Migrations;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;

#nullable disable

namespace BookshelfSample.Migrations
{
    public partial class AddSuperLongName : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee",
                columns: table => new
                {
                    id = table.Column<int>(type: "integer", nullable: false)
                        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                    name = table.Column<string>(type: "text", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaamm~", x => x.id);
                });
...
Enter fullscreen mode Exit fullscreen mode

[Database] Table name

  • sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaammmmme

[Database] Primary key

  • PK_sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaamm~

So if I try adding one more class what is named "sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee2", I will get an exception.

$ dotnet ef database update --project BookshelfSample
...
info: Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20220120163326_AddSuperLongName2'.
Applying migration '20220120163326_AddSuperLongName2'.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (92ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE refer_sample DROP CONSTRAINT "FK_refer_sample_sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg~";
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (81ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      ALTER TABLE refer_sample ADD "SuperLongName2Id" integer NULL;
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (98ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee2 (
          id integer GENERATED BY DEFAULT AS IDENTITY,
          name text NOT NULL,
          CONSTRAINT "PK_sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaamm~" PRIMARY KEY (id)
      );
Failed executing DbCommand (98ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee2 (
    id integer GENERATED BY DEFAULT AS IDENTITY,
    name text NOT NULL,
    CONSTRAINT "PK_sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaamm~" PRIMARY KEY (id)
);
Npgsql.PostgresException (0x80004005): 42P07: リレーション"sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaammmmme"はすでに存在します
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
  Exception data:
    Severity: ERROR
    SqlState: 42P07
    MessageText: リレーション"sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaammmmme"はすでに存在します
    File: heap.c
    Line: 1200
    Routine: heap_create_with_catalog
42P07: リレーション"sssssuuuuupppppeeeeerrrrr_lllllooooonnnnnggggg_nnnnnaaaaammmmme"はすでに存在します
Enter fullscreen mode Exit fullscreen mode

Foreign keys

How about foreign keys?

By default, foreign keys are named as "FK_{OwnTableName}{ReferencedTableName}{ReferencedColumnName}".

What happens if the foreign key is omitted and the name is duplicated?

The answer is "automatically renamed".

LooooonnnnngggggNaaaaammmmmeeeee.cs


using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace BookshelfSample.FkSamples;
[Table("lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee")]
public record LooooonnnnngggggNaaaaammmmmeeeee
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("looooonnnnnggggg_naaaaammmmmeeeee_id")]
    public int Id { get; init; }
    [Required]
    [Column("looooonnnnnggggg_naaaaammmmmeeeee_name")]
    public string Name { get; init; } = "";
    public LooooonnnnngggggNaaaaammmmmeeeee2 LllllooooonnnnngggggNnnnnaaaaammmmmeeeee2 { get; init; } = null!;
    public LooooonnnnngggggNaaaaammmmmeeeee3 LllllooooonnnnngggggNnnnnaaaaammmmmeeeee3 { get; init; } = null!;
}
Enter fullscreen mode Exit fullscreen mode

LooooonnnnngggggNaaaaammmmmeeeee2.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace BookshelfSample.FkSamples;
[Table("looooonnnnnggggg_name_2")]
public record LooooonnnnngggggNaaaaammmmmeeeee2
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("looooonnnnnggggg_naaaaammmmmeeeee_id")]
    public int Id { get; init; }
    [Required]
    [Column("looooonnnnnggggg_naaaaammmmmeeeee_name")]
    public string Name { get; init; } = "";
    public List<LooooonnnnngggggNaaaaammmmmeeeee> LooooonnnnngggggNaaaaammmmmeeeee { get; init; } = new List<LooooonnnnngggggNaaaaammmmmeeeee>();
}
Enter fullscreen mode Exit fullscreen mode

LooooonnnnngggggNaaaaammmmmeeeee3.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace BookshelfSample.FkSamples;
[Table("looooonnnnnggggg_name_3")]
public record LooooonnnnngggggNaaaaammmmmeeeee3
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("looooonnnnnggggg_naaaaammmmmeeeee_id")]
    public int Id { get; init; }
    [Required]
    [Column("looooonnnnnggggg_naaaaammmmmeeeee_name")]
    public string Name { get; init; } = "";
    public List<LooooonnnnngggggNaaaaammmmmeeeee> LooooonnnnngggggNaaaaammmmmeeeee { get; init; } = new List<LooooonnnnngggggNaaaaammmmmeeeee>();
}
Enter fullscreen mode Exit fullscreen mode

[MigrationFile] AddLongName.cs

using Microsoft.EntityFrameworkCore.Migrations;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;

#nullable disable

namespace BookshelfSample.Migrations
{
    public partial class AddLongName : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "looooonnnnnggggg_name_2",
                columns: table => new
                {
                    looooonnnnnggggg_naaaaammmmmeeeee_id = table.Column<int>(type: "integer", nullable: false)
                        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                    looooonnnnnggggg_naaaaammmmmeeeee_name = table.Column<string>(type: "text", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_looooonnnnnggggg_name_2", x => x.looooonnnnnggggg_naaaaammmmmeeeee_id);
                });

            migrationBuilder.CreateTable(
                name: "looooonnnnnggggg_name_3",
                columns: table => new
                {
                    looooonnnnnggggg_naaaaammmmmeeeee_id = table.Column<int>(type: "integer", nullable: false)
                        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                    looooonnnnnggggg_naaaaammmmmeeeee_name = table.Column<string>(type: "text", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_looooonnnnnggggg_name_3", x => x.looooonnnnnggggg_naaaaammmmmeeeee_id);
                });

            migrationBuilder.CreateTable(
                name: "lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee",
                columns: table => new
                {
                    looooonnnnnggggg_naaaaammmmmeeeee_id = table.Column<int>(type: "integer", nullable: false)
                        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                    looooonnnnnggggg_naaaaammmmmeeeee_name = table.Column<string>(type: "text", nullable: false),
                    LllllooooonnnnngggggNnnnnaaaaammmmmeeeee2Id = table.Column<int>(type: "integer", nullable: false),
                    LllllooooonnnnngggggNnnnnaaaaammmmmeeeee3Id = table.Column<int>(type: "integer", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee", x => x.looooonnnnnggggg_naaaaammmmmeeeee_id);
                    table.ForeignKey(
                        name: "FK_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee_looooonnnnnggggg_~",
                        column: x => x.LllllooooonnnnngggggNnnnnaaaaammmmmeeeee2Id,
                        principalTable: "looooonnnnnggggg_name_2",
                        principalColumn: "looooonnnnnggggg_naaaaammmmmeeeee_id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee_looooonnnnnggggg~1",
                        column: x => x.LllllooooonnnnngggggNnnnnaaaaammmmmeeeee3Id,
                        principalTable: "looooonnnnnggggg_name_3",
                        principalColumn: "looooonnnnnggggg_naaaaammmmmeeeee_id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee_Lllllooooonnnnng~1",
                table: "lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee",
                column: "LllllooooonnnnngggggNnnnnaaaaammmmmeeeee3Id");

            migrationBuilder.CreateIndex(
                name: "IX_lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee_Lllllooooonnnnngg~",
                table: "lllllooooonnnnnggggg_nnnnnaaaaammmmmeeeee",
                column: "LllllooooonnnnngggggNnnnnaaaaammmmmeeeee2Id");
        }
...
Enter fullscreen mode Exit fullscreen mode

Discussion (0)