Saving Entity causes duplicate insert into lookup data

Question

I am using EF 4.1 "code first" to create my db and objects.

Given:

public class Order 
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual OrderType OrderType { get; set; }
}

public class OrderType 
{
    public int Id { get; set; }
    public string Name { get; set; }
}

An order has one ordertype. An order type is just a look up table. The values dont change. Using Fluent API:

//Order
ToTable("order");
HasKey(key => key.Id);
Property(item => item.Id).HasColumnName("order_id").HasColumnType("int");
Property(item => item.Name).HasColumnName("name").HasColumnType("string").HasMaxLength(10).IsRequired();

HasRequired(item => item.OrderType).WithMany().Map(x => x.MapKey("order_type_id")).WillCascadeOnDelete(false);

//OrderType
ToTable("order_type");
HasKey(key => key.Id);

Property(item => item.Id).HasColumnName("order_type_id").HasColumnType("int");
Property(item => item.Name).HasColumnName("name").HasColumnType("nvarchar").HasMaxLength(100).IsRequired();

Now in our App we load all our lookup data and cache it.

var order = new Order
{
   Name = "Bob"
   OrderType = GetFromOurCache(5) //Get order type for id 5
};

var db = _db.GetContext();
db.Order.Add(order);
db.SaveChanges();

Our you-beaut order is saved but with a new order type, courtesy of EF. So now we have two same order types in our database. What can I do to alter this behaviour?

TIA

score 6 · Accepted Answer · answered Apr 15 '11 at 14:38

6

With EF 4.1 you can do this before calling SaveChanges:

db.Entry(order.OrderType).State = EntityState.Unchanged;

answered Apr 15 '11 at 14:38

Yakimych

17,612
7
52
69

score 2 · Answer 2 · answered Apr 15 '11 at 14:58

Alternatively to Yakimych's solution you can attach the OrderType to the context before you add the order to let EF know that the OrderType already exists in the database:

var order = new Order
{
    Name = "Bob"
    OrderType = GetFromOurCache(5) //Get order type for id 5
};

var db = _db.GetContext();
db.OrderTypes.Attach(order.OrderType);
db.Order.Add(order);
db.SaveChanges();

score 0 · Answer 3 · answered Apr 18 '11 at 00:11

Yakimych / Slauma - thanks for the answers. Interestingly I tried both ways and neither worked. Hence I asked the question. Your answers confirmed that I must be doing something wrong, and sure enough I wasnt managing my dbContext properly.

Still its a pain that EF automatically wants to insert lookup/static data even when you supply the full object (including the lookups unique Id). It puts the onus on the developer to remember to set the state. To make things a little easier I do:

var properties = entry.GetType().GetProperties().Where(x => x.PropertyType.GetInterface(typeof(ISeedData).Name) != null);
foreach (var staticProperty in properties)
{
    var n = staticProperty.GetValue(entry, null);
    Entry(n).State = EntityState.Unchanged;
}

in SaveChanges override.

Again thanks for the help.

Saving Entity causes duplicate insert into lookup data

3 Answers3

Linked