I need to create a many-to-many table relationship in which the elements of one table are shared by the elements of a second table. I've read the multitude of similar posts, but I'm clearly still wrong somewhere.
I've reduced my problem to this simple example. I have two tables: Beverages and Ingredients. I want the Ingredients to be shared among the Beverages.
Is the problem how I've created the bridge table? (Can I do without a bridge table?) Or is the problem in how I'm managing the data context?
I have a GetIngredient method with is intended to retrieve a reference to the desired Ingredient. If no Ingredient exists, a new ingredient is created.
All is as expected until the ctx.SaveChanges() which completes the add of the beverages. New ingredients are created such that each Beverage has an exclusive list of Ingredients. This is contrary to my intent.
Please advise.
[Fact]
public void VerifyManyToMany()
{
using (var ctx = new CoffeeDbContext())
{
// Latte = espresso, steamed milk
// Macchiato = espresso, milk foam
Beverage beverage1 = new Beverage();
beverage1.Name = "Latte";
beverage1.Ingredients.Add( GetIngredient("espresso"));
beverage1.Ingredients.Add( GetIngredient( "steamed milk"));
ctx.Beverages.Add(beverage1);
Beverage beverage2 = new Beverage();
beverage2.Name = "Macchiato";
beverage2.Ingredients.Add(GetIngredient("espresso"));
beverage2.Ingredients.Add(GetIngredient("milk foam"));
ctx.Beverages.Add(beverage2);
// prior to this line, Ingredient table comprised of:
// {"espresso", "steamed milk", "milk foam"}
ctx.SaveChanges();
// after this line, Ingredient table comprised of:
// {"espresso", "steamed milk", "milk foam", "espresso", "espresso", "steamed milk", "milk foam"}
List<Ingredient> ingredientList = ctx.Ingredients.ToList();
Assert.True( ingredientList.Count == 2);
}
}
/// <summary>
/// Retrieve ingredient of designated name.
/// If no ingredient exists, create new ingredient.
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
private Ingredient GetIngredient(string name)
{
using (var ctx = new CoffeeDbContext())
{
Ingredient ingredient = ctx.Ingredients.SingleOrDefault(i => i.Name == name);
if (ingredient == null)
{
ingredient = new Ingredient { Name = name };
ctx.Ingredients.Add(ingredient);
ctx.SaveChanges();
}
return ingredient;
}
}
Beverage Class
public class Beverage
{
public Beverage()
{
this.Ingredients = new HashSet<Ingredient>();
}
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public virtual int BeverageId { get; set; }
public virtual string Name { get; set; }
// many-to-many relationship, every Beverage comprised meany ingredients
public virtual ICollection<Ingredient> Ingredients { get; private set; }
}
Ingredient Class
public class Ingredient
{
public Ingredient()
{
this.Beverages = new List<Beverage>();
}
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public virtual int IngredientId { get; set; }
public virtual string Name { get; set; }
// many-to-many relationship, Ingredient used by many Beverages
public ICollection<Beverage> Beverages { get; private set; }
}
DbContext
public class CoffeeDbContext : DbContext
{
public DbSet<Beverage> Beverages { get; set; }
public DbSet<Ingredient> Ingredients { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Beverage>()
.HasMany(x => x.Ingredients)
.WithMany(x => x.Beverages)
.Map(
m =>
{
m.MapLeftKey("BeverageId");
m.MapRightKey("IngredientId");
m.ToTable("BeverageIngredients");
}
);
}
}
The problem is how you are managing the data context. In your code, you are using different instances of dbcontext, which might be cause of this problem. Every time you call GetIngredient, it is creating a new one.
In addition, you should call SaveChanges only once per method. If an error occurs after the first save changes, you might have inconsistent data.
To solve this issue, you can use an "recentIngredients" list. Like in this example:
class Program
{
static void Main(string[] args)
{
using (var ctx = new CoffeeDbContext())
{
// Latte = espresso, steamed milk
// Macchiato = espresso, milk foam
Beverage beverage1 = new Beverage();
beverage1.Name = "Latte";
beverage1.Ingredients.Add(GetIngredient(ctx, "espresso"));
beverage1.Ingredients.Add(GetIngredient(ctx, "steamed milk"));
ctx.Beverages.Add(beverage1);
Beverage beverage2 = new Beverage();
beverage2.Name = "Macchiato";
beverage2.Ingredients.Add(GetIngredient(ctx, "espresso"));
beverage2.Ingredients.Add(GetIngredient(ctx, "milk foam"));
ctx.Beverages.Add(beverage2);
// prior to this line, Ingredient table comprised of:
// {"espresso", "steamed milk", "milk foam"}
// call save changes only once, it will add all new ingredients automatically
ctx.SaveChanges();
// after this line, Ingredient table comprised of:
// {"espresso", "steamed milk", "milk foam", "espresso", "espresso", "steamed milk", "milk foam"}
//see the result here!
List<Ingredient> ingredientList = ctx.Ingredients.ToList();
Console.ReadKey();
}
}
private static List<Ingredient> recentIngredients = new List<Ingredient>();
//do not create another instance of dbcontext here, use a parameter
private static Ingredient GetIngredient(CoffeeDbContext ctx, string name)
{
//first, check if it was recently added
//if it was, just bring it from the temp collection
var recentIngredient = recentIngredients.SingleOrDefault(i => i.Name == name);
if (recentIngredient != null)
return recentIngredient;
//if it was not, check in database
Ingredient ingredient = ctx.Ingredients.SingleOrDefault(i => i.Name == name);
//if it exists in database, just return
if (ingredient == null)
{
//if it does not, create a new ingredient and add it to the temp list
ingredient = new Ingredient { Name = name };
recentIngredients.Add(ingredient);
}
return ingredient;
}
}
Remember to always kill the recentIngredients list at the end of the method.
Hope it helps!