C#•16mo 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);
        });
}

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

Update-Database

Update-Database

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

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")

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?

NeophyteOP•9/13/24, 4:15 PM

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

NeophyteOP•9/13/24, 4:46 PM

I found out that not even the migration was successful.
ie.: Default value for stateId was not applied.

NeophyteOP•9/13/24, 4:47 PM

having a second look at the migration file, none of the default values are added

NeophyteOP•9/13/24, 7:04 PM

migration seems to fail to consider the fluentApi settings.

TeBeCo•9/13/24, 7:58 PM

stop using AddAsync

TeBeCo•9/13/24, 7:58 PM

just .Add and then SaveChangesAsync

TeBeCo•9/13/24, 7:58 PM

TeBeCo•9/13/24, 8:00 PM

TeBeCo•9/13/24, 8:00 PM

https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbcontext.addasync?view=efcore-8.0

DbContext.AddAsync Method (Microsoft.EntityFrameworkCore)

Begins tracking the given entity, and any other reachable entities that are not already being tracked, in the Added state such that they will be inserted into the database when SaveChanges() is called.

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

Similar Threads

Similar Threads

Similar Threads