"SQLite Error 1: 'no such column" exception with EFc5

[pas059]

Hi,

using EFc5 with EF Designer 3.0.6, i noticed incoherencies in the generated code during call to HasForeignKey().
For instance, if a entity Profession has an identity property ProfessionId, and if a entity Patient has a navigation property Profession to Profession entity, the code generated in OnModelCreating() contains line like:
modelBuilder. ... .HasForeignKey("ProfessionProfessionId");
but the corresponding column in the database table Patient is named 'Profession_ProfessionId'.
With previous versions (EFc 3.1 or EF Designer ??) the code generated in OnModelCreating() contains line like:
modelBuilder. ... .HasForeignKey("Profession_ProfessionId");

Consequently, all queries implying Patient and its navigation property Profession, fail by fire an exception telling "SQLite Error 1: 'no such column: p.ProfessionProfessionId'".

Any idea?
regards

[pas059]

Hi,

it seems that this is an old issue:
https://github.com/msawczyn/EFDesigner/issues/250

[msawczyn]

You should be able to set the pattern for shadow properties created for foreign key values. It's a property of the designer surface, labeled "Shadow Key Name Pattern"

Choices are "TableColumn" and "TableUnderscoreColumn". Is this not working for you?

[pas059]

Hello,
thanks for the reply. Indeed, this option solves the problem related in my first message. But now, as i progress in this upgrading process, i noticed another similar code generation error. Below the details.

My Patient entity has a 1to1 relation to an entity BiometricParameterSet. The corresponding foreignkey/column in BiometricParameterSets table is called "Patient.BiometricParameterSet_PatientId" (as usual) but now the code generated in OnModelCreating() contains line like (using the option TableUnderscoreColumn):

         modelBuilder.Entity<global::myDB.Patient>()
                     .HasOne<global::myDB.BiometricParameterSet>(p => p.BiometricParameterSet)
                     .WithOne()
                     .HasForeignKey("BiometricParameterSet", "Patient_BiometricParameterSet_PatientId")
                     .OnDelete(DeleteBehavior.Cascade)
                     .IsRequired();

as the column "Patient_BiometricParameterSet_PatientId" does not exists this generates exceptions during execution. The genrated code should be:

         modelBuilder.Entity<global::myDB.Patient>()
                     .HasOne<global::myDB.BiometricParameterSet>(p => p.BiometricParameterSet)
                     .WithOne()
                     .HasForeignKey("BiometricParameterSet", "Patient.BiometricParameterSet_PatientId")
                     .OnDelete(DeleteBehavior.Cascade)
                     .IsRequired();

with the above code, no exeption. Another remark, with EF designer 2.x targetting EFc 3.x, the generated code is:

         modelBuilder.Entity<global::myDB.Patient>()
                     .HasOne(x => x.BiometricParameterSet)
                     .WithOne()
                     .HasForeignKey<global::myDB.BiometricParameterSet>("Patient.BiometricParameterSet_PatientId")
                     .IsRequired()
                     .OnDelete(DeleteBehavior.Cascade);

which do the same thing.
Is there other option?
regards

[msawczyn]

Your best bet would be to add the code generation template to your project and edit it to emit the code you're after. This particular bit is emitted from EFCoreModelGenerator.ttinclude or EFCore5ModelGenerator.ttinclude (depending on whether you're targeting EFCore5 or not).

There's more info on this process at https://msawczyn.github.io/EFDesigner/Customizing.html#replacing-the-t4-templates

[pas059]

Hi,
thanks for the workaround; nevertheless, this is a bug as the column name is generated in one way, and the declaration of the corresponding foreign key in another way.

regards