Scaffold warning message in one-to-one relationship tables

[PedroGGaspar]

My database has a main parent table with some child tables with a one-to-one relationship. So, ParentTable has a primary key in a ParentId field, and all ChildTables also have a ParentId field, and all these child tables have a foreign key in the ChildTable.ParentId field and also a primary key in the same ChildTable.ParentId field.

When I run the Scaffold-DbContext command line on my database, for each of these child tables I get this warning message:

"_For foreign key fk_ChildTable_ParentTable on table ChildTable, unable to model the end of the foreign key on principal table (null). This is usually because the principal table was not included in the selection set._"

Is it expected or some kind of bug? I was unable to find information about this message.

Despite this, the code that is generated seems fine:

public partial class ParentTable
{
    [Key]
    [Column("ParentID", TypeName = "counter")]
    public int ParentId { get; set; }

    // [...]
}

[Index("ParentId", Name = "fk_ChildTable_ParentTable", IsUnique = true)]
public partial class ChildTable
{
    [Key]
    [Column("ParentID")]
    public int? ParentId { get; set; }

    // [...]
}

Although now I noticed that the ParentId field was created as nullable in the ChildTable.

[ChrisJollyAU]

Can I ask, what version of Efcore.Jet are you using, what .Net version?

Also are you able to share the whole scaffold command line? Are you specifying any tables as part of that command line to include?

[PedroGGaspar]

Yes, you can!

EFCore.Jet: 7.0.3 MS EFCore: 7.0.15 .NET: 6.0 -> Platform target: Any CPU

The command line:

Scaffold-DbContext -Connection "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\database\Modelo.mdb;" -Provider EntityFrameworkCore.Jet -OutputDir ".\Models" -ContextDir ".\" -Namespace "QuartaDb.Models" -ContextNamespace "QuartaDb" -Context "QuartaDbContext" -DataAnnotations -NoPluralize

I'm using Any CPU as the platform target and "Microsoft.ACE.OLEDB.12.0" provider just in the scaffold project, in the real project I use x86 as the platform target and "Microsoft.Jet.OLEDB.4.0" as the provider.

[ChrisJollyAU]

My database has a main parent table with some child tables with a one-to-one relationship. So, ParentTable has a primary key in a ParentId field, and all ChildTables also have a ParentId field, and all these child tables have a foreign key in the ChildTable.ParentId field and also a primary key in the same ChildTable.ParentId field.

If I understand this properly, in ChildTable the ParentId is meant to be both the primary key and foreign key. From your code snippet I can see that in ChildTable it has created ParentId with a unique index but there is no mention of foreign key which there should be.

Just looking at the error message, there is one thing that stands out. The principal table name is (null).

Having a look where it is read, see https://github.com/CirrusRedOrg/EntityFrameworkCore.Jet/blob/71814274862516e4e5eb776b76918d0de6336adc/src/EFCore.Jet/Scaffolding/Internal/JetDatabaseModelFactory.cs#L498

it almost looks like the relation/foreign key isn't set up properly.

It would help if you could show the design view of the parent and child in MS access, or preferably link to a copy of the database itself (perhaps a minimal db with just a parent and child table in it)

[PedroGGaspar]

I attached a minimal db, 'Modelo.mdb' inside 'Modelo_mdb.zip': Modelo_mdb.zip

The parent table is Funcionarios (Employees), the child table is FuncionarioDocumentos (EmployeeDocuments), the key field that relates both tables is FuncionarioID (EmployeeID). Inside MS Access, when I display relationships, I get:

I also checked the MSysRelantionships system table within MS Access and there is this row:

Using that command line I showed in this database, I still get the same message:

_For foreign key fk_FuncionarioDocumentos_Funcionarios on table FuncionarioDocumentos, unable to model the end of the foreign key on principal table (null). This is usually because the principal table was not included in the selection set._

[ChrisJollyAU]

Thanks for the sample database, helped heaps.

Ultimately, we can ignore this warning. The generated code is fine

public partial class Funcionarios
{
    [Key]
    [Column("FuncionarioID", TypeName = "counter")]
    public int FuncionarioId { get; set; }

    [StringLength(70)]
    public string? Nome { get; set; }

    [InverseProperty("Funcionario")]
    public virtual FuncionarioDocumentos? FuncionarioDocumentos { get; set; }
}

[Index("FuncionarioId", Name = "fk_FuncionarioDocumentos_Funcionarios", IsUnique = true)]
public partial class FuncionarioDocumentos
{
    [Key]
    [Column("FuncionarioID")]
    public int FuncionarioId { get; set; }

    [Column("CPF")]
    [StringLength(11)]
    public string? Cpf { get; set; }

    [Column("RG")]
    [StringLength(20)]
    public string? Rg { get; set; }

    [ForeignKey("FuncionarioId")]
    [InverseProperty("FuncionarioDocumentos")]
    public virtual Funcionarios Funcionario { get; set; } = null!;
}

And the OnModelCreating has this

modelBuilder.Entity<FuncionarioDocumentos>(entity =>
{
    entity.HasKey(e => e.FuncionarioId).HasName("pk_FuncionarioDocumentos");

    entity.Property(e => e.FuncionarioId).ValueGeneratedNever();

    entity.HasOne(d => d.Funcionario).WithOne(p => p.FuncionarioDocumentos).HasConstraintName("fk_FuncionarioDocumentos_Funcionarios");
});

modelBuilder.Entity<Funcionarios>(entity =>
{
    entity.HasKey(e => e.FuncionarioId).HasName("pk_Funcionarios");
});

OnModelCreatingPartial(modelBuilder);

A bit of background on what is happening.

• When scaffolding, we use a technology called AdoX to get the details of the tables
• For AdoX, to get the foreign key details it is part of the Keys property of each table. This returns primary keys, foreign keys and unique keys. Currently this returns details from all the keys. Naturally the unique and primary don't have a principal table
• Thankfully where this is processed (GetRelations in JetDatabaseModelFactory) we have a query to get the columns for the relations/foreign keys (as you can have a multi column foreign key). That query, while using the same mechanism as above to loop through the keys, does check and return only details for the type of foreign keys.
• When looping through something that was a primary key or unique key, the name of that is different and no columns for that relation are found. Hence skipped without any error or warning.
• In your case what made it unique, is that for some reason the foreign key and the unique key that is coming up has exactly the same name. That is why it tries to do something with that row that originally came from the unique key and not the foreign key

It's a fairly easy fix to make sure AdoX only returns results for the foreign keys when getting the relations in the database (primary keys and unique are dealt with elsewhere). It should make its way into the 8.0 series coming up shortly but the 7.0 series won't be updated. It's really just a difference of a result row not being returned at all, or the result row being returned and then being skipped with a warning when we get to that row

[PedroGGaspar]

Thanks for the clarification and keep up the good work! I'm happy to be able to help in some way.