CODEWITHSUNDEEP
c#entity-frameworkforeign-keysentity-framework-6
Eric Tung
Eric Tung

Reputation: 310

EntityFramework 6 Using the wrong column as Parent ID

I am working with a bit of code where a record has children records in Entity Framework 6.

However the issue that I am running into is when EF generates the SQL to select the child records it is selecting the wrong column in the child model to key off of.

Example code (some properties clipped for brevity):

[Table("OrderTracking")]
public class OrderTrackingEntity
{
    [Key]
    public int          ID                  { get; set; }

    public int?         OrderEntryID        { get; set; }
    public string       PONumber            { get; set; }
    public string       OrderType           { get; set; }
    public int?         CustomerID          { get; set; }
    public int?         ManufacturerID      { get; set; }

    public string       Name                { get; set; }
    public string       Address1            { get; set; }

    public virtual ICollection<OrderTrackingDetailEntity> Items { get; set; }
}

[Table("OrderTrackingDetails")]
public class OrderTrackingDetailEntity
{
    // I want to key off of this field, OrderTrackingID == OrderTrackingEntity.ID
    [Key, Column(Order = 0)]
    public int          OrderTrackingID         { get; set; }

    // EF Keeps on selecting this field
    [Key, Column(Order = 1)]
    public int          ID                      { get; set; }

    public string       LineNumber              { get; set; }
    public decimal?     UnitPrice               { get; set; }
    public int          Quantity                { get; set; }
}

The output SQL is as follows:

SELECT 
    [Extent1].[OrderTrackingID] AS [OrderTrackingID], 
    [Extent1].[ID] AS [ID], 
    [Extent1].[LineNumber] AS [LineNumber], 
    [Extent1].[UnitPrice] AS [UnitPrice], 
    [Extent1].[Quantity] AS [Quantity], 
    FROM [dbo].[OrderTrackingDetails] AS [Extent1]
    WHERE [Extent1].[ID] = @EntityKeyValue1 -- << WRONG FIELD

How do I make it use OrderTrackingDetailEntity.OrderTrackingID as the id to key off of? (preferably using attributes)

Upvotes: 0

Views: 651

Answers (2)

DavidG
DavidG

Reputation: 118987

Firstly you have no navigation property on your child entity. Now EF should pick up the right property. However, I prefer to be explicit with these things so I would add a ForeignKey attribute too:

[ForeignKey("OrderTrackingID")]
public virtual OrderTrackingEntity OrderTracking { get; set; }

Upvotes: 2

Red
Red

Reputation: 2776

You can do configuration in your DbContext like this, but it also requires you to remove [Key] attrobute from OrderTrackingID property:

modelBuilder.Entity<OrderTrackingEntity>().HasMany(x => x.OrderTrackingDetailEntity).WithRequired().HasForeignKey(x=>x.OrderTrackingID         );

Because it looks to me like your OrderTrackingID property is not a real key, but a Foreign Key instead.

Also consider referencing real entities instead of keeping only keys for your relationship.

You can update your OrderTrackingDetailEntity to reference OrderTrackingEntity instead of having only a key for it and it would map fine:

[Table("OrderTrackingDetails")]
public class OrderTrackingDetailEntity
{
    public int          OrderTrackingID         { get; set; }
    [ForeignKey("OrderTrackingID")]
    public OrderTrackingEntity OrderTracking         { get; set; }

    // EF Keeps on selecting this field
    [Key, Column(Order = 1)]
    public int          ID                      { get; set; }

    public string       LineNumber              { get; set; }
    public decimal?     UnitPrice               { get; set; }
    public int          Quantity                { get; set; }
}

Upvotes: 1

Related Questions