0

I have a simple model:

class Employee {
 int EmployeeId {get;set;}
 List<Task> Tasks {get;set;}
}

class Manager {
 int ManagerId {get;set;}
 List<Task> Tasks {get;set;}
}

class Task {
 int TaskId {get;set;}
 EmployeeId {get;set;}
 ManagerId {get;set;}

 [ForeignKey("EmployeeId")]
 Employee Employee {get;set;}

 [ForeignKey("ManagerId")]
 Manager Manager {get;set;}
}

class Task1 : Task { }
class Task2 : Task { }

when I try to seed the database I get an exception, this is how I'm seeding:

Employee employee = new Employee();
context.Employees.Add(employee);
context.SaveChanges();

Manager manager = new Manager();
context.Managers.Add(manager);
context.SaveChanges();

List<Task> EmpTask = new List<Task>();
//EXCEPTION OCCURS HERE WHEN I TRY TO ADD NEW TASK ITEMS 
Task EmpOne = new Task1();
Task EmpTwo = new Task2();
EmpTask.Add(EmpOne);
EmpTask.Add(EmpTwo);

Employee.Tasks = EmpTask;
Context.Entry(employee).State = EntityState.Modified;
Context.SaveChanges();

List<Task> ManagerTask = new List<Task>();
Task ManagerOne = new Task1();
Task ManagerTwo = new Task2();
ManagerTask.Add(ManagerOne);
ManagerTask.Add(ManagerTwo);

Manager.Tasks = ManagerTask;
Context.Entry(manager).State = EntityState.Modified;
Context.SaveChanges();

The exception I keep getting is:

{"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_dbo.Task_dbo.Employee_EmployeeId\". The conflict occurred in database \"GIP\", table \"dbo.Employee\", column 'EmployeeId'.\r\nThe statement has been terminated."}

When I change my code to the following, however, I have no issues (seeding the same way, but no Manager portion):

class Employee {
 int EmployeeId {get;set;}
 List<Task> Tasks {get;set;}
}
class Task {
 int TaskId {get;set;}
 int EmployeeId {get;set;}
 [ForeignKey("EmployeeId")]
 Employee Employee {get;set;}
}

it seems like when the Task class has 2 foreign key constraints its getting confused or something...

Also, if I add the Manager class back, but REMOVE ALL ForeignKey constraints from the Task class, it has no issues because it creates it's own auto-generated foreign key columns in the database, with some employee foreign keys being Null for manager tasks, and manager foreign keys being null for employee tasks

Abdul Ahmad
  • 9,673
  • 16
  • 64
  • 127
  • Is this your actual code? – DavidG Nov 08 '14 at 21:39
  • part of it, its a bit longer with other parts (other models/entities/classes), but this is the relevant section, I'll update my question to add an addition piece of info that may give some more context – Abdul Ahmad Nov 08 '14 at 21:44
  • 1
    You seem to have left out the type of EmployeeId and ManagerId. Are they ints? Try Nullable. – fejesjoco Nov 08 '14 at 22:16
  • No exactly sure of your issue but a few things I noticed. When you are doing code first, you don't usually put foreign key columns. Your task has a property for employee and manager class and that's all EF needs to build the proper keys in the db. Also when you add data, you don't need to call save changes each time you insert something. This is not like adding data into a db with SQL. Make all your EF object relationships and call save changes at the end once. – Kirby Nov 08 '14 at 23:30
  • @Kirby I see, thank you for the clarification, I have a question though. What happens if I need to access the foreign keys in the database? The reason I'm putting foreign key columns myself is because I have a post method where I need to insert a task object into the right employee OR manager, but employee and manager are actually lists in another class in my full project, so I can have multiple employees and multiple managers – Abdul Ahmad Nov 09 '14 at 00:19
  • @fejesjoco bro, I love you, come give me a hug. Put that as an answer so I can accept it (the nullable thing) – Abdul Ahmad Nov 09 '14 at 00:28
  • You'll still have them, EF makes them automatically. I never specify keys with the annotations. I will specify if classes are required or optional with the fluent API. – Kirby Nov 09 '14 at 00:36
  • Right, but how do I access them in the controller for example? What do I write in the code? context.MyTable.??? – Abdul Ahmad Nov 09 '14 at 00:40
  • Microsoft actually recommends using FK properties together with the navigation properties. See: http://msdn.microsoft.com/en-us/data/jj713564.aspx "It is recommended to include properties in the model that map to foreign keys in the database." Of course, as Kirby suggests, it works without them as well, they are called independent associations. Actually I also prefer independent associations. – fejesjoco Nov 09 '14 at 07:43

2 Answers2

2

Make sure that your FK columns are nullable, eg. int? instead of int. If they are not null, then they are 0 by default, which will reference an entity with the 0 key, and that will violate the FK constraint.

fejesjoco
  • 11,763
  • 3
  • 35
  • 65
0

If you are going to specify your own foreign keys, the properties must be virtual.

Example:

[ForeignKey("Employee")] public virtual int EmployeeId { get; set; }

[ForeignKey("EmployeeId")] public virtual Employee Employee { get; set; }

Kirby
  • 1,739
  • 1
  • 17
  • 21