Masui Masanori

Posted on Jan 23, 2022

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

#csharp #entityframeworkcore #postgres

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.

[ASP.NET Core][Entity Framework Core][Npgsql] Try code first & DB first

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

I think this is because this issue.

StopTheHostException should be made public to be dealt with gracefully #60600 - GitHub

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();
}

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.

Appendix K. PostgreSQL Limits - PostgreSQL: Documentation: 14

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,
        };
    }
}

[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);
                });
...

[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"はすでに存在します

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!;
}

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>();
}

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>();
}

[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");
        }
...

DEV Community