How do you do lookup tables via code first

Question

I've been searching this without any luck on how to resolve. I have a list of available departments that can be used within my stores. Since stores vary, some departments may not exist and I want to keep track of how much shelving space each department has for each store. What's the best way to create this?

Here's my model:

public class Store
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ID { get; set; } //StoreNumber
    public virtual List<StoreDepartment> StoreDepartments { get; set; }
}

public class StoreDepartment
{
    [Key]
    public int ID { get; set; }
    public int StoreID { get; set; }
    public Department Department { get; set; }
    public int ShelvingLinealFT { get; set; }
}

public class Department
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ID { get; set; } //DepartmentNumber
    public string Name { get; set; }
    public bool InActive { get; set; }
}

I've already populated my Department tables, but when I attempt to save a StoreDepartment object, I get an error stating that it can't insert a row since its trying to create a duplicate key. It's like it's trying to create a new record.

Any help would be appreciated.

Here's the code for my DbContext:

public class StoresRepository:DbContext
{
    public DbSet<Store> Stores { get; set; }
    public DbSet<StoreDepartment> StoreDepartments { get; set; }
    public DbSet<Department> Departments { get; set; }
}

Here is my Save method:

    /// <summary>
/// Saves a StoreDepartment Object to the store("dept.storeid")
/// Adds a new record if ID is 0
/// </summary>
/// <param name="dept"></param>
/// <returns></returns>
public bool Save(StoreDepartment dept)
{
    bool retval = false;
    try
    {
        using (var db = new StoresRepository())
        {
            if (dept.ID.Equals(0))
            {
                //Add Store Department
                db.StoreDepartments.Add(dept);                            
            }
            else
            {
                //this is an update
                StoreDepartment  department = db.StoreDepartments.Where(p => p.ID.Equals(dept.ID)).FirstOrDefault();
                department.Department = dept.Department;
                department.ShelvingLinealFT = dept.ShelvingLinealFT;
            }
            int rowsupdated = db.SaveChanges();
            retval = true;
        }
    }
    catch (Exception ex)
    {
        Utils.Trace(string.Format("StoresContext.cs: StoreDepartments.Save(). ID:{1}. Exception: {0}", ex, dept.ID), Utils.ErrorTypes.Error);
    }
    return retval;

    }

score 0 · Answer 1 · edited May 23 '17 at 10:28

You probably change the state of the Department to added when you add the StoreDepartment object. Something like this:

using(var db = new MyContext())
{
    var storeDepartment = new StoreDepartment();
    storeDepartment.StoreId = storeId;
    storeDeparemtent.Department = department;
    db.StoreDepartments.Add(storeDepartment); // also marks Department as added
    db.SaveChanges();
}

The solution is to move up the line where you add the object:

using(var db = new MyContext())
{
    var storeDepartment = new StoreDepartment();
    db.StoreDepartments.Add(storeDepartment);
    storeDepartment.StoreId = storeId;
    ....
}

You can also add a DepartmentId to the StoreDepartment class and set its value, as you do with StoreId. Together with the Department property this is called a foreign key association.

I actually don't think that's the issue. My save method requires a "StoreDepartment" parameter and attempts to save it. It keeps trying to re-add the Department instead of referencing it by id. — user2259709, May 02 '13 at 23:47

score 0 · Accepted Answer · answered May 10 '13 at 18:21

I figured it out.

Here are the correct models:

public class StoreDepartment
{
    [Key]
    public int ID { get; set; }
    public int DepartmentID { get; set; }
    public virtual Department Department { get; set; }
    public int ShelvingFootage { get; set; }
}

public class Department
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ID { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }
    public virtual ICollection<StoreDepartment> StoreDepartments { get; set; }
}

Using Affluent API, I setup my relationships as follows:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<StoreDepartment>().HasRequired(d => d.Department);
    modelBuilder.Entity<Department>().HasMany(d => d.StoreDepartments);            
}

Once I had this setup, one of the main issues I had was with populating the object. Normally, you would do the following:

StoreDepartment sd = new StoreDepartment();
sd.Department = new Department(){
    ID = 302,        
    Name = "Deli"
};
sd.ShelvingFootage = 100;

However when trying to save this object, Entity would attempt to add a new record in the Department table which of course would throw an exception due to a violation in the primary key.

The trick was to not update this directly and to build my StoreDepartment object as follows:

StoreDepartment sd = new StoreDepartment();
sd.DepartmentID = 302;
sd.ShelvingFootage = 100;

By doing this, you are only updating the foreign key for the StoreDepartment.Department object.

How do you do lookup tables via code first

2 Answers2