How to map properties that belongs to fields of the join table of a many to many relationship?

Question

I have a table bills and a table documents. A bill can be related with many documents and a document can be related with many bills. A many to many relationship.

Also, in the join table, a have a field, page, that indicates the page in which the document has information about the related bill.

So in my database I have this:

• Bills(Id, Reference...)
• Documents(Id, Name...)
• DocumentsOfBills(IdBill, IdDocument, Page)

Now this is my entities in which map the tables:

public class Bill
{
    long Id;
    string Reference;

    HashSet<Document> Documents;
}



public class Document
{
    long Id;
    string Name;
}

if I don't inlcude the page, I know how to configure this relationship with fluent API and EF Core 7:

internal class BillConfiguration : IEntityTypeConfiguration<Bill>
{
    public void Configure(EntityTypeBuilder<Bill> paramBillfiguracion)
    {
        paramFacturafiguracion.ToTable("Bills");


        paramBillfiguracion.HasKey(o => o.Id);
        paramBillfiguracion.Property<long>(o => o.Id)
            .HasColumnName("Id")
            .HasColumnType("bigint")
            .IsRequired();

        paramBillfiguracion.Property<string?>(o => o.Reference)
            .HasColumnName("Reference")
            .HasColumnType("varchar(50)");


            paramBillfiguracion
                .HasMany(x => x.Documents)
                .WithMany()
                .UsingEntity(
                    "DocumentsOfBills",
                    l => l.HasOne(typeof(Document)).WithMany().HasForeignKey("Id").HasPrincipalKey(nameof(Document.Id)),
                    r => r.HasOne(typeof(Bill)).WithMany().HasForeignKey("Id").HasPrincipalKey(nameof(Bill.Id)),
                    j => j.HasKey("IdDocument", "IdBill"));
    }
}

The problem is How to include the page in the document. I was thinking to add the property page in the Document entity:

public class Document
{
    long Id;
    string Name;
    int Page;
}

But I don't know how to configure that using fluent API. how to configure to set the page in the entity property? Is it possible to do it in this way? I would like to avoid the needed to have a join entity only to has access to the page number.

Thanks.

Answer 1

Well, all the solutions that I have seen use a an entity for the join table, but i would like to avoid this because it makes to have larger code because you have to navigate for different properties.

The solution is using Entity Core 7, but I don't know if it works in an earlier version.

This are my entities:

public class Bill
{
    long Id;
    string Reference;

    HashSet<Document> Documents;
}



public class Document
{
    long Id;
    string Name;
    int page;
}

This is the configuration of for Bills:

internal class BillConfiguration : IEntityTypeConfiguration<Bill>
{
    public void Configure(EntityTypeBuilder<Bill> paramBillfiguracion)
    {
        paramFacturafiguracion.ToTable("Bills");


        paramBillfiguracion.HasKey(o => o.Id);
        paramBillfiguracion.Property<long>(o => o.Id)
            .HasColumnName("Id")
            .HasColumnType("bigint")
            .IsRequired();

        paramBillfiguracion.Property<string?>(o => o.Reference)
            .HasColumnName("Reference")
            .HasColumnType("varchar(50)");


            paramBillfiguracion
                .HasMany(x => x.Documents)
                .WithMany()
                .UsingEntity(
                    "DocumentsOfBills",
                    l => l.HasOne(typeof(Document)).WithMany().HasForeignKey("Id").HasPrincipalKey(nameof(Document.Id)),
                    r => r.HasOne(typeof(Bill)).WithMany().HasForeignKey("Id").HasPrincipalKey(nameof(Bill.Id)),
                    j => j.HasKey("IdDocument", "IdBill"));
    }
}

This is the configuration for document. It is where is the key part:

internal class ConfiguracionDocuments : IEntityTypeConfiguration<Document>
{
    public void Configure(EntityTypeBuilder<Document> paramDocumentConfiguracion)
    {
        paramDocumentConfiguracion.ToTable("Documents")
            .SplitToTable("DocumentsOfBills",
                TableBuilder =>
                {
                    TableBuilder.Property(document => document.Id).HasColumnName("IDDocument");
                    TableBuilder.Property(document => document.Page).HasColumnName("Page");
                });


        paramDocumentConfiguracion.HasKey(o => o.Id);
        //Se indica que el ID lo asingará la base de datos al agregar la entidad.
        paramDocumentConfiguracion.Property<long>(o => o.Id)
            .HasColumnName("IDDocument")
            .HasColumnType("bigint")
            .IsRequired();

        paramDocumentConfiguracion.Property<string>(o => o.Name)
            .HasColumnName("Name")
            .HasColumnType("varchar(900)")
            .IsRequired();
    }
}

And specificly, this is the important part:

    paramDocumentConfiguracion.ToTable("Documents")
        .SplitToTable("DocumentsOfBills",
            TableBuilder =>
            {
                TableBuilder.Property(document => document.Id).HasColumnName("IDDocument");
                TableBuilder.Property(document => document.Page).HasColumnName("Page");
            });

Here I am telling that the Document entity use two table to get the data, with SplitTable.

TableBuilder.Property(document => document.Id).HasColumnName("IDDocument");

This stablish the relation between the join table and the documents table. Personally I prefer to set it, but in this case it is not really needed because is set in the bills configuration, when I set the many to many relationship.

TableBuilder.Property(document => document.Page).HasColumnName("Page");

In this line I set that the Page property in the Document entity get the data from the field of the join table in the database.

The documentation is this: https://learn.microsoft.com/en-us/ef/core/modeling/table-splitting#configuration-1

I hope this code could help.