C#•2mo ago

✅ .NET 8, EF Core, Fluent API - Schema definition is ignored when accessing via DbSet

HI, I would appreciate some hints where I have made the mistake. I am running an ASP.NET API. I am relying on EF Core for data accessing ORM. The db is scaffolded with Code first approach. I have created and mapped a new Entity.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.EnsureSchema("authentication");

    migrationBuilder.CreateTable(
        schema: "authentication",
        name: "AuthenticationAttempt",
        columns: table => new
        {
            Id = table.Column<long>(type: "bigint", nullable: false)
                .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
            StateId = table.Column<Guid>(type: "uuid", nullable: false),
            IsValid = table.Column<bool>(type: "boolean", nullable: false),
            CreatedAt = table.Column<DateTimeOffset>(type: "timestamp with time zone", nullable: false),
            ExpiringAt = table.Column<DateTimeOffset>(type: "timestamp with time zone", nullable: false),
            ConsumedAt = table.Column<DateTimeOffset>(type: "timestamp with time zone", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_AuthenticationAttempt", x => x.Id);
        });
}

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.EnsureSchema("authentication");

    migrationBuilder.CreateTable(
        schema: "authentication",
        name: "AuthenticationAttempt",
        columns: table => new
        {
            Id = table.Column<long>(type: "bigint", nullable: false)
                .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
            StateId = table.Column<Guid>(type: "uuid", nullable: false),
            IsValid = table.Column<bool>(type: "boolean", nullable: false),
            CreatedAt = table.Column<DateTimeOffset>(type: "timestamp with time zone", nullable: false),
            ExpiringAt = table.Column<DateTimeOffset>(type: "timestamp with time zone", nullable: false),
            ConsumedAt = table.Column<DateTimeOffset>(type: "timestamp with time zone", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_AuthenticationAttempt", x => x.Id);
        });
}

Update-Database run properly, the schema and and table created properly. When I try to test and persist an entity, it results in error:

var dummyAttempt = new AttemptEntity();
await _context.AuthenticationAttempt.AddAsync(dummyAttempt);
await _context.SaveChangesAsync();

var dummyAttempt = new AttemptEntity();
await _context.AuthenticationAttempt.AddAsync(dummyAttempt);
await _context.SaveChangesAsync();

Error:

ERROR:  relation "AuthenticationAttempt" does not exist at character 13
STATEMENT:  INSERT INTO "AuthenticationAttempt" ("ConsumedAt", "CreatedAt", "ExpiringAt", "IsValid", "StateId")

ERROR:  relation "AuthenticationAttempt" does not exist at character 13
STATEMENT:  INSERT INTO "AuthenticationAttempt" ("ConsumedAt", "CreatedAt", "ExpiringAt", "IsValid", "StateId")

QQ: what am I missing making the Db operation look for the table at the default schema, instead of the specified one?

2 Replies

Neophyte•2mo ago

The fluent configuration is as:

internal class AuthenticationAttemptConfiguration : IEntityTypeConfiguration<AuthenticationAttemptEntity>
{
    public void Configure(EntityTypeBuilder<AuthenticationAttemptEntity> builder)
    {
        builder.ToTable("AuthenticationAttempt", "authentication");
        builder.HasKey(a => a.Id);
        builder.HasIndex(a => a.StateId);

        builder.Property(a => a.Id)
            .HasColumnName("id")
            .IsRequired()
            .UseIdentityAlwaysColumn();

        builder.Property(a => a.StateId)
            .HasColumnName("state_id")
            .IsRequired()
            .HasColumnType("uuid")
            .HasDefaultValueSql("uuid_generate_v4()");

        builder.Property(a => a.IsValid)
            .HasColumnName("is_valid")
            // This is a computed column, TRUE only if the expiration date is in the future and the attempt has not been consumed
            .HasComputedColumnSql("expiring_at > now() AND consumed_at IS NULL", stored: true)
            .IsRequired();

        builder.Property(a => a.CreatedAt)
            .HasColumnName("created_at")
            .HasColumnType("timestamptz")
            .HasDefaultValueSql("now() at time zone 'utc'")
            .IsRequired();

        builder.Property(a => a.ExpiringAt)
            .HasColumnName("expiring_at")
            .HasColumnType("timestamptz")
            .HasDefaultValueSql("now() at time zone 'utc' + interval '10 minutes'")
            .IsRequired();

        builder.Property(a => a.ConsumedAt)
            .HasColumnName("consumed_at")
            .HasColumnType("timestamptz")
            .IsRequired(false);
    }
}

internal class AuthenticationAttemptConfiguration : IEntityTypeConfiguration<AuthenticationAttemptEntity>
{
    public void Configure(EntityTypeBuilder<AuthenticationAttemptEntity> builder)
    {
        builder.ToTable("AuthenticationAttempt", "authentication");
        builder.HasKey(a => a.Id);
        builder.HasIndex(a => a.StateId);

        builder.Property(a => a.Id)
            .HasColumnName("id")
            .IsRequired()
            .UseIdentityAlwaysColumn();

        builder.Property(a => a.StateId)
            .HasColumnName("state_id")
            .IsRequired()
            .HasColumnType("uuid")
            .HasDefaultValueSql("uuid_generate_v4()");

        builder.Property(a => a.IsValid)
            .HasColumnName("is_valid")
            // This is a computed column, TRUE only if the expiration date is in the future and the attempt has not been consumed
            .HasComputedColumnSql("expiring_at > now() AND consumed_at IS NULL", stored: true)
            .IsRequired();

        builder.Property(a => a.CreatedAt)
            .HasColumnName("created_at")
            .HasColumnType("timestamptz")
            .HasDefaultValueSql("now() at time zone 'utc'")
            .IsRequired();

        builder.Property(a => a.ExpiringAt)
            .HasColumnName("expiring_at")
            .HasColumnType("timestamptz")
            .HasDefaultValueSql("now() at time zone 'utc' + interval '10 minutes'")
            .IsRequired();

        builder.Property(a => a.ConsumedAt)
            .HasColumnName("consumed_at")
            .HasColumnType("timestamptz")
            .IsRequired(false);
    }
}

I found out that not even the migration was successful. ie.: Default value for stateId was not applied. having a second look at the migration file, none of the default values are added migration seems to fail to consider the fluentApi settings.

Unknown User•2mo ago

Message Not Public

Gaming

Programming

✅ .NET 8, EF Core, Fluent API - Schema definition is ignored when accessing via DbSet