CODEWITHSUNDEEP
c#sqlentity-framework
Jonathan
Jonathan

Reputation: 15462

Issue with relational tables in Entity Framework

I have a one-to-many relationship of order to payment:

public class Order
{
    [Key]
    public Guid SerialNumber { get; set; }

    public string OrderNumber { get; set; }

    ...

    [ForeignKey("OrderNumber")]
    public virtual ICollection<Payment> Payments { get; set; }
}

public class Payment
{
    [Key]
    public string SerialNumber { get; set; }

    public string OrderNumber { get; set; }

    ...

    public decimal Amount { get; set; }
}

Despite the records being available, the Payments collection always shows 0 elements. I've looked at the trace and the problem seems to be with the generated SQL query - it's trying to match Order.SerialNumber to Payment.OrderNumber.

How can I resolve this, preferably with data annotations? Thanks in advance!

Upvotes: 2

Views: 234

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364409

it's trying to match Order.SerialNumber to Payment.OrderNumber.

Yes because that is exactly what you have modeled. ForeignKey attribute doesn't describe the name of foreign key property in the related table. It describes the name of foreign key property in the same table and it is used to pair navigation property with its key so the correct usage is:

public class Order
{
    [Key]
    public Guid SerialNumber { get; set; }

    public string OrderNumber { get; set; }

    ...

    public virtual ICollection<Payment> Payments { get; set; }
}

public class Payment
{
    [Key]
    public string SerialNumber { get; set; }

    // Foreign key must have the same type as primary key in the principal table
    public Guid OrderNumber { get; set; }

    ...

    public decimal Amount { get; set; }

    // Reverse navigation property to principal associated with foreign key
    [ForeignKey("OrderNumber")]
    public virtual Order Order { get; set; }
}

If you don't want navigation property in Payment you have to use fluent API to describe the mapping:

modelBuilder.Entity<Order>()
            .HasMany(o => o.Payments)
            .WithRequired()
            .HasForeignKey(p => p.OrderNumber);

If you have existing database and your relation really targets OrderNumber column in the Order table it means it must be marked with unique constraint - such relation currently cannot be mapped in EF because it doesn't support unique constraints yet.

Upvotes: 1

Related Questions