John Odom John Odom - 3 years ago 89
C# Question

Primary Key Violation in Multi-threading EF6

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:

Items

Recipes

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?

Answer Source

You only need the list of items in a recipe. If you need to search which recipes have a particular item, you can do that by searching on the foreign key of Recipe (the primary key of Item).

There is a fundamental naming flaw with the code. You have a class Recipes and then a List of Recipes called Recipes. The same with Items.

You then have a foreign key [ForeignKey("Items")] for Recipes. Which Items is this? The List or the Object Items. It's so prone to errors.

Rename your classes to Recipe and Item

public class Recipe
{ 
    public Recipe()


public class Item
{
    public Item()

Also - with the duplicate Id of 0, as mentioned in the comments, it sounds like the Id is not being set.

Looking at the link to Recipes:

{
    .../...
    "ingredients": [
            { "item_id": 19684, "count": 50 },
            { "item_id": 19721, "count": 1 },
            { "item_id": 46747, "count": 10 }
    ],
    "id": 7319,
    .../...
}

Recipe should not contain a list of items, but a list of ingredients. The class structure should be:

Recipe has:
public virtual ICollection<Ingredient> Ingredients { get; set; }

Ingredient has:
public virtual ICollection<Item> Items { get; set; }

The Item class doesn't have a list of Ingredients or Recipes, these list are retrieved by querying the database Items on the Ingredient with the foreign key matching the primary key for the Item, or the database Ingredients on the Recipe foreign key matching the primary key of the Ingredient - you can then do a join to find any items of those Ingredients.

So make the following changes:

There doesn't need to be a mention of Recipe or Ingredients in the Item class.

public Item() // remove pluralisation
{
    // Remove these from the constructor,
    // Recipes = new HashSet<Recipes>();
    // Ingredients = new HashSet<Ingredient>();
}

// remove these from the class.
// public virtual ICollection<Recipes> Recipes { get; set; }
// public virtual ICollection<Ingredient> Ingredients { get; set; }

An Ingredient has many Items - ergo a collection of Items

public Ingredient()
{
    // You don't need a collection of Recipes - 
    // you need a collection of Items.
    // Recipe = new HashSet<Recipes>();
}
.../...
[ForeignKey("Item")] // change this
public Item Item // include an Item object - the PK of the
    // Item is the FK of the Ingredient


.../...
// Remove Recipes
// public virtual ICollection<Recipes> Recipe { get; set; }
public virtual ICollection<Item> Items { get; set; }

I prefer using the object name for the variable Item Item.

A Recipe has many Ingredients - ergo a collection of Ingredients

public Recipes()
{
    disciplines = new List<string>();
    ingredients = new HashSet<Ingredient>();
}
.../...
// [ForeignKey("Items")] remove this
[ForeignKey("Ingredient")] 
public Ingredient Ingredient // include as Ingredient object 
    // the PK of the Ingredient is the FK for the Recipe

.../...
public virtual ICollection<Ingredient> Ingredients { get; set; }
// The Recipe does not have an Item, the Ingredient has 
// has a collection of <Item> Items
// public virtual Items Items { get; set; }

Also I'm not sure why you are using Hashsets. I'd make them into Lists if you have no particular reason for using them.

If this doesn't fix your code, I'll review the remainder of it.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download