I am working on a C# console application that downloads data from the Guild Wars 2 API and inputs it into my database with Entity Framework 6. I'm trying to use multi-threading so that I can speed up the process of inputting large amounts of data into my database.
The problem is when the code runs into my DBContext.SaveChanges() call in my AddRecipes method, the following error was returned:
Violation of PRIMARY KEY constraint 'PK_dbo.Items'. Cannot insert duplicate key in object 'dbo.Items'. The duplicate key value is (0).
Here is the portion of code that is related to my issue:
class Program
{
private static ManualResetEvent resetEvent;
private static int nIncompleteThreads = 0;
//Call this function to add to the dbo.Items table
private static void AddItems(object response)
{
string strResponse = (string)response;
using (GWDBContext ctx = new GWDBContext())
{
IEnumerable<Items> itemResponse = JsonConvert.DeserializeObject<IEnumerable<Items>>(strResponse);
ctx.Items.AddRange(itemResponse);
ctx.SaveChanges();
}
if (Interlocked.Decrement(ref nIncompleteThreads) == 0)
{
resetEvent.Set();
}
}
//Call this function to add to the dbo.Recipes table
private static void AddRecipes(object response)
{
string strResponse = (string)response;
using (GWDBContext ctx = new GWDBContext())
{
IEnumerable<Recipes> recipeResponse = JsonConvert.DeserializeObject<IEnumerable<Recipes>>(strResponse);
ctx.Recipes.AddRange(recipeResponse);
foreach(Recipes recipe in recipeResponse)
{
ctx.Ingredients.AddRange(recipe.ingredients);
}
ctx.SaveChanges(); //This is where the error is thrown
}
if (Interlocked.Decrement(ref nIncompleteThreads) == 0)
{
resetEvent.Set();
}
}
static void GetResponse(string strLink, string type)
{
//This method calls the GW2 API through HTTPWebRequest
//and store the responses in a List<string> responseList variable.
GWHelper.GetAllResponses(strLink);
resetEvent = new ManualResetEvent(false);
nIncompleteThreads = GWHelper.responseList.Count();
//ThreadPool.QueueUserWorkItem creates threads for multi-threading
switch (type)
{
case "I":
{
foreach (string strResponse in GWHelper.responseList)
{
ThreadPool.QueueUserWorkItem(new WaitCallback(AddItems), strResponse);
}
break;
}
case "R":
{
foreach (string strResponse in GWHelper.responseList)
{
ThreadPool.QueueUserWorkItem(new WaitCallback(AddRecipes), strResponse);
}
break;
}
}
//Waiting then resetting event and clearing the responseList
resetEvent.WaitOne();
GWHelper.responseList.Clear();
resetEvent.Dispose();
}
static void Main(string[] args)
{
string strItemsLink = "items";
string strRecipesLink = "recipes";
GetResponse(strItemsLink, "I");
GetResponse(strRecipesLink, "R");
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
Here is my DBContext class:
public class GWDBContext : DbContext
{
public GWDBContext() : base("name=XenoGWDBConnectionString") { }
public DbSet<Items> Items { get; set; }
public DbSet<Recipes> Recipes { get; set; }
public DbSet<Ingredient> Ingredients { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
}
}
Here is also my table classes (I know the names are confusing, I'm working on re-writing them):
public class Items
{
public Items()
{
Recipes = new HashSet<Recipes>();
Ingredients = new HashSet<Ingredient>();
}
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)] //This attribute makes sure that the id column is not an identity column since the api is sending that).
public int id { get; set; }
.../...
public virtual ICollection<Recipes> Recipes { get; set; }
public virtual ICollection<Ingredient> Ingredients { get; set; }
}
public class Recipes
{
public Recipes()
{
disciplines = new List<string>();
ingredients = new HashSet<Ingredient>();
}
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)] //This attribute makes sure that the id column is not an identity column since the api is sending that).
public int id { get; set; }
public string type { get; set; }
[ForeignKey("Items")] //This attribute points the output_item_id column to the Items table.
.../...
private List<string> _disciplines { get; set; }
public List<string> disciplines
{
get { return _disciplines; }
set { _disciplines = value; }
}
[Required]
public string DisciplineAsString
{
//get; set;
get { return string.Join(",", _disciplines); }
set { _disciplines = value.Split(',').ToList(); }
}
public string chat_link { get; set; }
public virtual ICollection<Ingredient> ingredients { get; set; }
public virtual Items Items { get; set; }
}
public class Ingredient
{
public Ingredient()
{
Recipe = new HashSet<Recipes>();
}
[Key]
public int ingredientID { get; set; }
[ForeignKey("Items")] //This attribute points the item_id column to the Items table.
public int item_id { get; set; }
public int count { get; set; }
public virtual ICollection<Recipes> Recipe { get; set; }
public virtual Items Items { get; set; }
}
Here are links that explains what is returned for the Items/Recipes class:
I noticed that after removing the foreign key constraints and the public virtual Items Items { get; set; } code the data will be saved correctly. I believe my error have something to do with having public virtual Items Items in the Recipes class. But from my understanding I needed to have that virtual variable in the class so Entity Framework can know the relationship between the classes. So why would having that virtual variable in my class cause the primary key violation to be thrown?