BUG #18796: Npgsql Driver: Foreign Key Read Inversion in INSERT Operations - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18796
Logged by:          Iza Souza
Email address:      rujjinjakka@rocketmail.com
PostgreSQL version: 16.0
Operating system:   Windows
Description:

given a basic SQL statement:
INSERT INTO public."Proposals"(
"SubscriberId", "ClientId", "ClientType", "ProposalIdentifier", "Active",
"Soon", "Start", "End", "Validity", "IntervalFrequency",
"FirstVisit", "ServiceDescription", "AdditionalInformation",
"ProposalServicesId", "ProposalType", "TreatmentForm",
"ProposalPaymentMethod", "ProposalPaymentMethod", "ProposalPaymentType",
"FinancialObservations", "Value", "ProposalProgress",
"NumberOfVisits", "InclusionDate", "ChangeDate")
VALUES (
1, 6, 2, 'PXKSR-939',
true, null, '2025-02-05T19:55:45.110771', '2025-03-07T19:55:45.110781',
'2025-04-06T19:55:45.110788', 10,
'2025-02-05T19:55:45.110792', 'Random service description', 'Random
additional information', NULL,
1, 'Random form of address', 2,
1, 3, 'Random financial observations', 62232, 1,
21, '2025-02-05T19:55:45.110801', '2025-02-05T19:55:45.110802');

Contains foreign key: ubscSriberId is the Subscriber ID - Subscriber
contains the primary key. I can insert it without any problems using the
command prompt and pgAdmin 4. But the Posgres driver on Windows - for this
same insert in json format:
INSERT INTO "Proposals" ("ProposalProgress", "SubscriberId", "Active",
"ClientId", "ChangeDate", "InclusionDate", "ServiceDescription", "End",
"ProposalPaymentMethodId", "TreatmentMethod", "IntervalFrequency",
"ProposalIdentifier", "AdditionalInformation", "Start", "Soon",
"ProposalPaymentMethodId", "FinancialObservations", "FirstVisit",
"VisitQuantity", "ProposalServicesId", "ClientType",
"ProposalPaymentTypeId", "ProposalType", "Validity", "Value")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12,
@p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24)
RETURNING "Id";
2025-02-05 17:06:35 [Error] An exception occurred in the database while
saving changes for context type
'VEL.Infra.Data.EntityFramework.Context.VelContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while
saving the entity changes. See the inner exception for details.
 ---> Npgsql.PostgresException (0x80004005): 23503: insertion or update in
table "Proposals" violates foreign key constraint
"FK_Proposals_Subscriber_SubscriberId"

DETAIL: Detail redacted as it may contain sensitive data. Specify 'Include
Error Detail' in the connection string to include this information. at
Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async,
DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean
isReadingPrependedMessage)
 at

System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16
token)
 at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming,
CancellationToken cancellationToken)
 at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming,
CancellationToken cancellationToken)
 at Npgsql.NpgsqlDataReader.NextResult()
 at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior
behavior, CancellationToken cancellationToken)
 at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior
behavior, CancellationToken cancellationToken)
 at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
 at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
 at
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject
parameterObject)
 at
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection
connection)
 Exception data:
 severity: ERROR
SqlState: 23503
MessageText: Insertion or update in table "Proposals" violates foreign key
constraint "FK_Proposals_Subscriber_SubscriberId"
Detail: Detail redacted as it may contain sensitive data. Specify 'Include
Error Detail' in the connection string to include this information.
SchemaName: public
 TableName: Proposals
 ConstraintName: FK_Proposals_Subscriber_SubscriberId
 File: ri_triggers.c
 Line: 2619
 Routine: ri_ReportViolation
 --- End of inner exception stack trace ---
 at
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection
connection)
 at
Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1
commandBatches, IRelationalConnection connection)
 at
Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1
entries)
 at
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1
entriesToSave)
 at
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager
stateManager, Boolean acceptAllChangesOnSuccess)
 at
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__112_0(DbContext
_, ValueTuple`2 t)

I'm noticing that the key is being read in reverse order in the ORM. It's
not matching what's happening in the table. I'm going to be forced to delete
a foreign key that was created correctly, but is not being read correctly.
The table was created by the Ngpsql driver in a C# API. It was created
correctly - along with the foreign keys. I can insert via SQL, but the ORM
can't insert into what it created itself, I'm on .NET 8 and I can't migrate
to .NET 9. What can I do? Create navigation? The FK was created and is
working... at least for Postgres;


 public static class ProposalMap
 {
 public static void Configure(ModelBuilder modelBuilder)
 {
 modelBuilder.Entity<Proposal>(entity =>
 {
 entity.HasKey(psg => psg.Id);

 entity
 .HasOne<Subscriber>()
 .WithMany()
 .HasForeignKey(c => c.SubscriberId)
 .OnDelete(DeleteBehavior.Restrict);

 entity
 .HasOne(c => c.PaymentProposal)
 .WithMany()
 .IsRequired(false)
 .HasForeignKey(c => c.PaymentProposal))
 .OnDelete(DeleteBehavior.Restrict);

 entity
 .HasOne(c => c.PaymentFormProposal)
 .WithMany()
 .IsRequired(false)
 .HasForeignKey(c => c.PaymentFormProposalId)
 .OnDelete(DeleteBehavior.Restrict);

 entity
 .HasOne(c => c.TypePaymentProposal)
 .WithMany()
 .IsRequired(false)
 .HasForeignKey(c => c.TypePaymentProposalId)
 .OnDelete(DeleteBehavior.Restrict);

 entity.HasMany(c => c.SelectedServices).WithOne().HasForeignKey(s =>
s.ProposalId);


 entity.ToTable("Proposals");
 });
 }
 }
}