CODEWITHSUNDEEP
c#entity-frameworkasp.net-mvc-4
Hamish
Hamish

Reputation: 119

Entity Framework is adding an extra foreign key to my table

I have a very simple data model: a bog standard 1:m relationship. But I want it slightly denormalised for performance reasons and the EF is not doing what I expect it to do.

I am getting an extra foreign key on a join table when I have a 1:m relationship and also slightly denormalise the data so that my Product table has a reference to a specific order as well as a list of orders.

Take 2 classes: Customer and Product. They have a m:m relationship, which is to be joined by the Orders class.

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Order> Orders { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Order> Orders { get; set; } 
}

public class Order
{
    public int OrderId { get; set; }
    public virtual Customer Customer { get; set; }
    public virtual Product Product { get; set; }
}

So far so simple. Everything is as I expect it to be with the table definitions:

Customers

CustomerId int
Name nvarchar(MAX)

Products

ProductId int
Name nvarchar(MAX)

Orders

OrderId int
Customer_CustomerId int
Product_ProductId int

Now the issue. For reasons I won't go into right now, I want to hold the latest order for a product on the product table itself, rather than have to query and do a WHERE OrderId = MAX(OrderId) on the Orders table.

So I change my model class by adding a single line:

public virtual Order MostRecentOrder { get; set; }

The DB definition for the Product table looks just as I would expect:

MostRecentOrder_OrderId int

However, EF has also added a foreign key to the Orders table:

Product_ProductId1 int

That shouldn't be there. Only one Order can be the LATEST order, and I have a single instance of the order in my Product class.

So I tried doing it a bit more explicitly in the Product class:

    public int MostRecentOrderId { get; set; }

    [ForeignKey("MostRecentOrderId")]
    public virtual Order MostRecentOrder { get; set; }

The Product field gets a name change to reflect the explicitly named column in my class, but the Orders table still has that extra foreign key to the Product table.

I kept playing and found that I could get rid of the erroneous foreign key on the Orders table by unmapping the Product class:

    public int? MostRecentOrderId { get; set; }

    [ForeignKey("MostRecentOrderId")]
    [NotMapped]
    public virtual Order MostRecentOrder { get; set; }

I also missed the nullable requirement in my prototype ;)

However, now I cannot make use of pre-loading the data.

This code:

ApplicationDbContext db = new ApplicationDbContext();
var products = db.Products.Include("Orders").Include("MostRecentOrder").ToList();

throws this exception:

A specified Include path is not valid. The EntityType 'WebApplication1.Models.Product' does not declare a navigation property with the name 'MostRecentOrder'.

What am I missing here? I just wish the Product table to have a list of orders, and a reference to one (special) order. In traditional client/server dev, I would code this SQL to get the data back:

-- to go into the "Product" object
SELECT * 
FROM Products
LEFT JOIN Orders ON Products.MostRecentOrderId = Orders.OrderId;
WHERE ProductId = 4

and

-- to go into the "Product.Orders collection"
SELECT * 
FROM Orders 
WHERE ProductId = 4;

Upvotes: 1

Views: 1088

Answers (1)

Yacoub Massad
Yacoub Massad

Reputation: 27861

So, after you add the MostRecentOrder navigation property, the Product class would look like this:

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
    public virtual Order MostRecentOrder { get; set; }
}

What you can do next is use the Fluent API to configure the relationships inside your context class like this:

public class Context : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Customer> Customers { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<Order>()
            .HasRequired(x => x.Product)
            .WithMany(x => x.Orders);

        modelBuilder
            .Entity<Product>()
            .HasOptional(x => x.MostRecentOrder);
    }
}

It seems to me that convention based configuration are not explicit enough to tell EF about your relationships in such case. Fluent API is more explicit.

Upvotes: 1

Related Questions