I've been trying for hours now but I can't get my seed data to work on my Entity Framework Database.
I keep getting the following error:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Appointment_dbo.Employee_EmployeeID". The conflict occurred in database "BezoekersDBContext", table "dbo.Employee", column 'ID'.
Bigger part of the stack trace:
System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Appointment_dbo.Employee_EmployeeID". The conflict occurred in database "BezoekersDBContext", table "dbo.Employee", column 'ID'.
Another annoyance:
Everytime I run update-databasein the Package Manager Console, it keeps adding the first part of the seed data, the Employee table data. That's why the employee table has about 6 entries now but the Primary keys of the Employee Table are 7,8,9,10,11 and 12. This is maybe/probably a part of the problem. I'd like to just drop the database to reset these keys and re-add it by using update-database but when I try to delete the database, I get an error saying the database is in use.
I am at the end of my wits here. I'll drop my database diagram, the models, Dbcontext, SQL server table data and the seed data. I hope you guys can help me out. If you see any mistakes in my model, please tell me as well! Help a fellow programmer out!
I've read this SO question(It was a question that already could have an answer) and a user said that my foreign keys should be nullable. Mine can't be nullable, they're all required. Another user says something about making my navigation properties virtual, but I've removed that to get rid of this weird error that caused my web api to be unable to retrieve data from the database back to the caller:
If you have navigation properties make them non virtual. Mapping will still work but it prevents the creation of Dynamic Proxy entities which cannot be serialized. Not having lazy loading is fine in a WebApi as you don't have a persistent connection and you ran a .ToList() anyway.
Diagram(Created with EF Power Tools):
MODELS
Visitor
public class Visitor
{
[Key]
public int ID
{ get; set; }
[Required]
public string FirstName
{ get; set; }
[Required]
public string LastName
{ get; set; }
//[Key] I have to configure this later
[Required]
[EmailAddress]
public string Email
{ get; set; }
public string Company
{ get; set; }
public string Image
{ get; set; }
public string PhoneNumber
{ get; set; }
public ICollection<Appointment> Appointments
{ get; set; }
}
Appointment
public class Appointment
{
[Key]
public int ID
{ get; set; }
[Required]
public int EmployeeID
{ get; set; }
[Required]
public string Location
{ get; set; }
public string Comment
{ get; set; }
[Required]
public DateTime Date
{ get; set; }
public ICollection<Visitor> Visitors
{ get; set; }
public ICollection<Collector> Collectors
{ get; set; }
[ForeignKey("EmployeeID")]
public Employee Employee
{ get; set; }
}
Collector
public class Collector
{
[Key]
public int ID
{ get; set; }
[Required]
public int AppointmentID
{ get; set; }
[Required]
public int EmployeeID
{ get; set; }
[Required]
public int Preference
{ get; set; }
[ForeignKey("EmployeeID")]
public Employee Employee
{ get; set; }
[ForeignKey("AppointmentID")]
public Appointment Appointment
{ get; set; }
}
Employee
public class Employee
{
[Key]
public int ID
{ get; set; }
//[Key] I have to configure this later
[Required]
public int SAP_ID
{ get; set; }
[Required]
public string FirstName
{ get; set; }
[Required]
public string LastName
{ get; set; }
[Required]
[EmailAddress]
public string Email
{ get; set; }
public string Image
{ get; set; }
[Required]
public string PhoneNumber
{ get; set; }
public ICollection<Appointment> Appointments
{ get; set; }
}
DbContext
public class BezoekersDBContext : DbContext
{
public BezoekersDBContext() : base("BezoekersDBContext")
{
}
public DbSet<Appointment> Appointments
{ get; set; }
public DbSet<Employee> Employees
{ get; set; }
public DbSet<Collector> Collectors
{ get; set; }
public DbSet<Visitor> Visitors
{ get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
modelBuilder.Entity<Collector>().HasRequired(d => d.Employee).WithMany().WillCascadeOnDelete(false);
}
WillCascadeOnDelete is on because a Collector is an Employee but an Employee isn't always a Collector. (A collector is someone who pick up the visitor and bring him to the place of the appointment. He's an employee as well).
Not having this option enabled gave me errors about Cascade loops and paths and I've found a lot of SO answers telling me to use this option to resolve that issue.
SQL code of tables
Visitor
CREATE TABLE [dbo].[Visitor] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (MAX) NOT NULL,
[LastName] NVARCHAR (MAX) NOT NULL,
[Email] NVARCHAR (128) NOT NULL,
[Company] NVARCHAR (MAX) NULL,
[Image] NVARCHAR (MAX) NULL,
[PhoneNumber] NVARCHAR (MAX) NULL,
CONSTRAINT [PK_dbo.Visitor] PRIMARY KEY CLUSTERED ([ID] ASC)
);
Appointment
CREATE TABLE [dbo].[Appointment] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[EmployeeID] INT NOT NULL,
[Location] NVARCHAR (MAX) NOT NULL,
[Comment] NVARCHAR (MAX) NULL,
[Date] DATETIME NOT NULL,
CONSTRAINT [PK_dbo.Appointment] PRIMARY KEY CLUSTERED ([ID] ASC),
CONSTRAINT [FK_dbo.Appointment_dbo.Employee_EmployeeID] FOREIGN KEY ([EmployeeID]) REFERENCES [dbo].[Employee] ([ID]) ON DELETE CASCADE
);
Collector
CREATE TABLE [dbo].[Collector] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[AppointmentID] INT NOT NULL,
[EmployeeID] INT NOT NULL,
[Preference] INT NOT NULL,
CONSTRAINT [PK_dbo.Collector] PRIMARY KEY CLUSTERED ([ID] ASC),
CONSTRAINT [FK_dbo.Collector_dbo.Employee_EmployeeID] FOREIGN KEY ([EmployeeID]) REFERENCES [dbo].[Employee] ([ID]),
CONSTRAINT [FK_dbo.Collector_dbo.Appointment_AppointmentID] FOREIGN KEY ([AppointmentID]) REFERENCES [dbo].[Appointment] ([ID]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_AppointmentID]
ON [dbo].[Collector]([AppointmentID] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_EmployeeID]
ON [dbo].[Collector]([EmployeeID] ASC);
Employee
CREATE TABLE [dbo].[Employee] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[SAP_ID] INT NOT NULL,
[FirstName] NVARCHAR (MAX) NOT NULL,
[LastName] NVARCHAR (MAX) NOT NULL,
[Email] NVARCHAR (MAX) NOT NULL,
[Image] NVARCHAR (MAX) NULL,
[PhoneNumber] NVARCHAR (MAX) NOT NULL,
CONSTRAINT [PK_dbo.Employee] PRIMARY KEY CLUSTERED ([ID] ASC)
);
Seed data
Right now, my Employee table has rows in it with the ID's starting at 7 because I've dropped the database and used Update-Database a lot out of frustration. I've changed the upcoming code example to match those primary keys.
If someone has a better solution than using .Single(x => x.ID == NUMBERHERE); because I might have to change that if I get more errors and increasing auto increments, please tell me! I've googled a lot of solutions but I couldn't find anything!
internal sealed class Configuration : DbMigrationsConfiguration<BezoekersRegistratie_BackEnd2.DAL.BezoekersDBContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
}
protected override void Seed(BezoekersDBContext context)
{
var employees = new List<Employee>
{
new Employee { FirstName="Jan", LastName="Scholten", Email="JanScholten2948@outlook.com", Image="http://lorempixel.com/400/400", PhoneNumber="0675876573", SAP_ID= 20149203, Appointments = new List<Appointment>()},
new Employee { FirstName="Bart", LastName="de Vries", Email="BartDeVries23@outlook.com", Image="http://lorempixel.com/400/400", PhoneNumber="0655544422", SAP_ID=20148394, Appointments = new List<Appointment>()},
new Employee { FirstName="Denke", LastName="Eekhoorn", Email="DenkeEekhoorn39@gmail.com", Image="http://lorempixel.com/400/400", PhoneNumber="0674738383", SAP_ID=2039388, Appointments = new List<Appointment>() }
};
employees.ForEach(e => context.Employees.AddOrUpdate(p => p.ID, e));
context.SaveChanges();
var appointments = new List<Appointment>
{
new Appointment { Employee = employees.FirstOrDefault(e => e.ID == 7),Location = "Apeldoorn", Comment="Rolstoeltoegang is nodig!", Date = new DateTime(2015, 11, 12, 13, 14, 0), Visitors = new List<Visitor>(), Collectors = new List<Collector>() },
new Appointment { Employee = employees.FirstOrDefault(e => e.ID == 8),Location = "Zwolle", Comment="Kan laat zijn.", Date = new DateTime(2016,2,11,14,12,0), Visitors = new List<Visitor>(), Collectors = new List<Collector>() },
new Appointment { Employee = employees.FirstOrDefault(e => e.ID == 9),Location="Amsterdam", Comment="Geen speciale opmerking", Date = new DateTime(2015,12,18,15,15,0), Visitors = new List<Visitor>(), Collectors = new List<Collector>() }
};
appointments.ForEach(a => context.Appointments.AddOrUpdate(p => p.ID, a));
context.SaveChanges();
employees[0].Appointments.Add(appointments[0]);
employees[1].Appointments.Add(appointments[1]);
employees[2].Appointments.Add(appointments[2]);
context.SaveChanges();
var visitors = new List<Visitor>
{
new Visitor { FirstName="Sander", LastName="ten Brinke", Email="sander.ten.brinke@xs4all.nl", Company="BelastingDienst", Image = "http://lorempixel.com/400/400", PhoneNumber="0620556193", Appointments = new List<Appointment>() },
new Visitor { FirstName="Reinold", LastName="Boeve", Email= "reinold.boeve@gmail.com", Company="Harvy", Image= "http://lorempixel.com/400/400", PhoneNumber="0657373660", Appointments= new List<Appointment>() },
new Visitor { FirstName="Eleina", LastName="Nieborg", Email="eleinanieborg@outlook.com", Company="Windesheim", Image="http://lorempixel.com/400/400", PhoneNumber="0641401035", Appointments = new List<Appointment>() },
new Visitor { FirstName="Roy", LastName="Stobbelaar", Email="roystobbelaar@hotmail.com", Company="BelastingDienst", Image="http://lorempixel.com/400/400", PhoneNumber="0637628563", Appointments = new List<Appointment>() },
new Visitor { FirstName="Michel", LastName="Vaassen", Email="maf.vaassen@belastingdienst.nl", Company="BelastingDienst", Image="http://lorempixel.com/400/400", PhoneNumber="0638572843", Appointments= new List<Appointment>() }
};
visitors.ForEach(v => context.Visitors.AddOrUpdate(s => s.Email, v));
context.SaveChanges();
visitors[0].Appointments.Add(appointments[0]);
visitors[1].Appointments.Add(appointments[0]);
visitors[3].Appointments.Add(appointments[0]);
visitors[3].Appointments.Add(appointments[1]);
visitors[2].Appointments.Add(appointments[1]);
visitors[0].Appointments.Add(appointments[2]);
visitors[1].Appointments.Add(appointments[2]);
visitors[4].Appointments.Add(appointments[2]);
context.SaveChanges();
var collectors = new List<Collector>
{
new Collector { Appointment = appointments.FirstOrDefault(x => x.ID == 1), Employee = employees.FirstOrDefault(x => x.ID == 7), Preference = 1}, //employeeID = signle).id
new Collector { Appointment = appointments.FirstOrDefault(x => x.ID == 1), Employee = employees.FirstOrDefault(x => x.ID == 8), Preference = 2},
new Collector { Appointment = appointments.FirstOrDefault(x => x.ID == 1), Employee = employees.FirstOrDefault(x => x.ID == 9), Preference = 3},
new Collector { Appointment = appointments.FirstOrDefault(x => x.ID == 2), Employee = employees.FirstOrDefault(x => x.ID == 8), Preference = 1},
new Collector { Appointment = appointments.FirstOrDefault(x => x.ID == 3), Employee = employees.FirstOrDefault(x => x.ID == 9), Preference = 1},
new Collector { Appointment = appointments.FirstOrDefault(x => x.ID == 3), Employee = employees.FirstOrDefault(x => x.ID == 7), Preference = 2},
};
collectors.ForEach(c => context.Collectors.AddOrUpdate(p => p.ID, c));
context.SaveChanges();
appointments[0].Collectors.Add(collectors[0]);
appointments[0].Collectors.Add(collectors[1]);
appointments[0].Collectors.Add(collectors[2]);
appointments[1].Collectors.Add(collectors[3]);
appointments[2].Collectors.Add(collectors[4]);
appointments[2].Collectors.Add(collectors[5]);
context.SaveChanges();
appointments[0].Visitors.Add(visitors[0]);
appointments[0].Visitors.Add(visitors[1]);
appointments[0].Visitors.Add(visitors[3]);
appointments[1].Visitors.Add(visitors[3]);
appointments[1].Visitors.Add(visitors[2]);
appointments[2].Visitors.Add(visitors[0]);
appointments[2].Visitors.Add(visitors[1]);
appointments[2].Visitors.Add(visitors[4]);
context.SaveChanges();
}
}
I'm sorry about the amount of code, but all of the code that led up this problem is in there, so I hope someone can help me out!
Please tell me what I need to do to get rid of this problem forever. I'll be forever in your debt!
Edit-While-Writing:
I was browsing through the questions that might have an answer before I posted this answer and I found This SO post.
The following is mentioned:
What is happening is your objects all have the default int value (0) for their primary key. When you add them to the context, EF detects this and throws an error (two objects of the same type cannot have the same key, in this case, 0. I assume your primary key fields in the database are set as IDENTITY columns and will auto increment +1 on insert. This may sound odd, but you need to give your objects placeholder IDs which will be replaced on insert with the IDENTITY values.
I changed my first part of the seed code to the following:
protected override void Seed(BezoekersDBContext context)
{
var employees = new List<Employee>
{
new Employee { ID = 1,FirstName="Jan", LastName="Scholten", Email="JanScholten2948@outlook.com", Image="http://lorempixel.com/400/400", PhoneNumber="0675876573", SAP_ID= 20149203, Appointments = new List<Appointment>()},
new Employee { ID = 2,FirstName="Bart", LastName="de Vries", Email="BartDeVries23@outlook.com", Image="http://lorempixel.com/400/400", PhoneNumber="0655544422", SAP_ID=20148394, Appointments = new List<Appointment>()},
new Employee { ID = 3,FirstName="Denke", LastName="Eekhoorn", Email="DenkeEekhoorn39@gmail.com", Image="http://lorempixel.com/400/400", PhoneNumber="0674738383", SAP_ID=2039388, Appointments = new List<Appointment>() }
};
employees.ForEach(e => context.Employees.AddOrUpdate(p => p.SAP_ID, e));
context.SaveChanges();
var appointments = new List<Appointment>
{
new Appointment { Employee = employees.FirstOrDefault(e => e.ID == 7),Location = "Apeldoorn", Comment="Rolstoeltoegang is nodig!", Date = new DateTime(2015, 11, 12, 13, 14, 0), Visitors = new List<Visitor>(), Collectors = new List<Collector>() },
new Appointment { Employee = employees.FirstOrDefault(e => e.ID == 8),Location = "Zwolle", Comment="Kan laat zijn.", Date = new DateTime(2016,2,11,14,12,0), Visitors = new List<Visitor>(), Collectors = new List<Collector>() },
new Appointment { Employee = employees.FirstOrDefault(e => e.ID == 9),Location="Amsterdam", Comment="Geen speciale opmerking", Date = new DateTime(2015,12,18,15,15,0), Visitors = new List<Visitor>(), Collectors = new List<Collector>() }
};
//Other code here
This gives me the following error:
Sequence contains more than one element
Bigger part of the stack trace:
System.InvalidOperationException: Sequence contains more than one element
at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__2[TResult](IEnumerable`1 sequence)
at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
at System.Data.Entity.Migrations.DbSetMigrationsExtensions.AddOrUpdate[TEntity](DbSet`1 set, IEnumerable`1 identifyingProperties, InternalSet`1 internalSet, TEntity[] entities)
at System.Data.Entity.Migrations.DbSetMigrationsExtensions.AddOrUpdate[TEntity](IDbSet`1 set, Expression`1 identifierExpression, TEntity[] entities)
at BezoekersRegistratie_BackEnd2.Migrations.Configuration.<>c__DisplayClass1_0.<Seed>b__0(Employee e) in C:\BezoekersRegistratie\Stagiairs\Reinold Boeve\BezoekersRegistratie_BackEnd2\BezoekersRegistratie_BackEnd2\Migrations\Configuration.cs:line 26
at System.Collections.Generic.List`1.ForEach(Action`1 action)
at BezoekersRegistratie_BackEnd2.Migrations.Configuration.Seed(BezoekersDBContext context) in C:\BezoekersRegistratie\Stagiairs\Reinold Boeve\BezoekersRegistratie_BackEnd2\BezoekersRegistratie_BackEnd2\Migrations\Configuration.cs:line 26
I feel like I'm a bit closer. Could someone help me out and solve this?
