Closest Similar Question with Answer I Found - Reference: Question

Hello, Junior .NET Developer here.

<TLDR;>

InnerException: MySqlException: Duplicate entry 'vegan' for key 'Tags.PRIMARY'

Question: The CreateMealAsync is clearly trying to create new Tags from meal.Tags and then sees, but those tags already exist in the database and returns the error...how can I change this behavior to simply update the MealTag table and create a meal with tags appropriately?

</TLDR;>

I have a simple object with Tags situation. A meal can have many tags, a tag can have many meals. I made use of EF Core 6 here to create the database giving it only the following two classes (Summarized):

public class Meal
{
   [Key]
   public Guid Id { get; set; }
   public ICollection<Tag>? Tags { get; set; }
}

public class Tag
{
    [Key]
    public string? TagName { get; set; }
    public ICollection<Meal>? Meals { get; set; }
}

EF Core 6 handled all the many-to-many relationship stuff like join table (MealTag) etc.

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<Meal>()
            .HasMany(m => m.Tags)
            .WithMany(t => t.Meals);

        modelBuilder
            .Entity<Tag>()
            .HasMany(t => t.Meals)
            .WithMany(m => m.Tags);
    }

I receive my "Duplicate Entry" error when calling SaveChangesAsync on create a new meal in the MealService:

public async Task<bool> CreateMealAsync(Meal meal)
    {
        try
        {
            await _context.Meals.AddAsync(meal);
            await _context.SaveChangesAsync();    // ERROR OCCURS HERE

            return true;
        }
        catch (Exception ex)
        {
            // I log things here
            return false;
        }
    }

I add the required tags as such on the Create A Meal Razor Component:

// Current Version
var tagList = new List<Tag>();

foreach (var item in selected)
{
      var tag = await data.GetTagByNameAsync(item.Text);
      tagList.Add(tag);
}

meal.Tags = tagList;

var result = await data.CreateMealAsync(meal);

// Older Version
meal.Tags = new List<Tags>();

foreach (var item in selected)
{         
     meal.Tags.Add(new Tag() { TagName = item.Text } );
}

var result = await data.CreateMealAsync(meal);

In the older version above I assumed it might be because I am trying to create a new Tag with a similar ID so I am now going to fetch the actual tag in the database and adding that to the collection however in theory it's the same object.

I did notice if I delete all the tags in the meal from the Tags-table in the database before creating it, it creates it no problem, creates new tags for each tag inside it and does the mapping in the join table perfectly. However obviously this behavior is unacceptable.

The CreateMealAsync is clearly trying to create new Tags from meal.Tags and then sees but those tags already exist in the database, but I have no idea how change this behavior.

I might be to blame for using too much of EF Core's code first magic but here is to hoping I missed something else where.

Post-Answer Edit

Both of tailoxyn's methods worked perfectly. See the new CreateMealAsync below (I tried both methods and the attach is definitely the sexier of the two:

public async Task<bool> CreateMealAsync(Meal meal)
    {
        try
        {
            // Retrieve from context method
            /*
            var tempListOfTags = meal.Tags;
            meal.Tags = new List<Tag>();

            foreach (var mealTag in tempListOfTags)
            {
                var tag = await _context.Tags
                    .AsTracking()
                    .FirstOrDefaultAsync(x => x == mealTag);

                meal.Tags.Add(tag);
            }
            */
            
            // Attach Method
            foreach (var tag in meal.Tags)
                _context.Attach(tag);

            await _context.Meals.AddAsync(meal);
            await _context.SaveChangesAsync();

            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }

NOTE: this issue likely only came up as I have a DbContext level AsNoTracking enabled.

CodePudding user response：

EF Core tries to create a Meal but also a Tag entry in the database. If the tag was already used before, it is already present in the DB. Therefore, you recieve the error.

To fix this, you need to attach the tag to the DbContext if it is already in the DB OR use the corresponding tracked Tag.

Simplest way is to retrieve the tag if it exists:

var tag = context.Tags.FirstOrDefault(x => x.TagName == "tagName");
if(tag is null)
    tag = new Tag("tagName");
meal.Tags.Add(tag);
context.Add(meal)
await context.SaveChangesAsync();

Instead of retrieving the tag, you may also create a new one and attach it:

context.Attach(tag);