How to set a relationship between two entities that don't have an (integer) foreign key?

asp.net-core ef-code-first entity-framework-core

Question

In my database I have the two tables tblOrders and tblPayments, both containing a property named ReferenceNumber (varchar 50). Unfortunately tblPayments doesn't have a foreign key pointing to tblOrders, so the only way to put the records from these two tables in relationship is by referring to the matching ReferenceNumber (1 order can have n payments).

In my ASP.NET Core 1.1 application that uses Entity Framework Core I'm trying to define this relationship for two models Order.cs and Payment.cs.

In the related configuration files I tried to set them up as following:

OrderConfiguration.cs:

    public void Map(EntityTypeBuilder<Order> builder)
    {
        builder.HasKey(m => m.Id);

        builder.Property(m => m.Id).HasColumnName("ID");
        ... several other mappings...

        builder.HasMany(m => m.Payments).WithOne(p => p.Order).HasForeignKey(m => m.ReferenceNumber);


        builder.ToTable("tblOrders");
    }

PaymentConfiguration.cs:

    public void Map(EntityTypeBuilder<Payment> modelBuilder)
    {
        modelBuilder.HasKey(m => m.Id);

        modelBuilder.Property(m => m.Id).HasColumnName("ID");
        ... several other mappings...

        modelBuilder.HasOne(m => m.Order).WithMany(o => o.Payments).HasForeignKey(m => m.ReferenceNumber);

        modelBuilder.ToTable("tblPayments");
    }

when I debug the application, i obtain the following exception:

System.InvalidOperationException: 'The relationship from 'Payment.Order' to 'Order.Payments' with foreign key properties {'ReferenceNumber' : string} cannot target the primary key {'Id' : int} because it is not compatible. Configure a principal key or a set of compatible foreign key properties for this relationship.'

This sounds pretty reasonable, but how can i set the relationship properly considering the situation mentioned above?

1
0
6/30/2017 3:13:15 PM

Accepted Answer

You can't have it both ways. It seems you're using an existing database, which literally does not have a foreign key. You can't simply tell EF to pretend as if there is one. You either need to alter that database so it has an appropriate foreign key or you need to dispense with the EF modeling of the relationship and just manually query the related entities. For example, to get the payments for a particular order:

var payments = db.Payments.Where(m => m.ReferenceNumber == order.ReferenceNumber);

Instead of the easier order.Payments, which requires the foreign key.

2
6/30/2017 3:22:04 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow