C
C#3mo ago
Neophyte

✅ .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
NeophyteOP3mo 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
Unknown User3mo ago
Message Not Public
Sign In & Join Server To View
Want results from more Discord servers?
Add your server