Extract property from a List and insert into another table

Question

I have a table called Personel, each Personel contains a List<Eblagh>. Inside this there is a property named Vahed.

I want to find all the Vahed that are available in personels (eblaghs), and insert them into another table called School and in the that table, we must have a column to hold all related Personel.

These are my classes:

public class Personel
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    ...

    public int AreaId { get; set; }
    public Area Area { get; set; }

    public List<PersonelSchool> PersonelSchools { get; set; } = new();
    public List<Eblagh> Eblaghs { get; set; } = new();
}

public class Eblagh
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Vahed { get; set; }
    ...
    public int PersonelId { get; set; }
    public Personel Personel { get; set; }
}

public class School
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public List<PersonelSchool> PersonelSchools { get; set; } = new();
}

public class PersonelSchool
{
    public int PersonelId { get; set; }
    public Personel Personel { get; set; }

    public int SchoolId { get; set; }
    public School School { get; set; }
}

I used the following code:

var personelsWithEblagh = 
    await db.Personels
            .Where(x => x.AreaId == Area.Id)
            .Include(p => p.Eblaghs)
            .ToListAsync();

foreach (var personel in personelsWithEblagh)
{
    foreach (var eblagh in personel.Eblaghs)
    {
        var schoolName = eblagh.Vahed;

        var existingSchool = await db.Schools.FirstOrDefaultAsync(s => s.Name == schoolName);

        if (existingSchool == null)
        {
            var newSchool = new School { Name = schoolName };
            await db.Schools.AddAsync(newSchool);
            await db.SaveChangesAsync();
            existingSchool = newSchool;
        }

        personel.PersonelSchools.Add(new PersonelSchool
        {
            PersonelId = personel.Id,
            SchoolId = existingSchool.Id
        });
    }
}

await db.SaveChangesAsync();

And this is my DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<PersonelSchool>()
        .HasKey(ps => new { ps.PersonelId, ps.SchoolId });

    modelBuilder.Entity<PersonelSchool>()
        .HasOne(ps => ps.Personel)
        .WithMany(p => p.PersonelSchools)
        .HasForeignKey(ps => ps.PersonelId)
        .OnDelete(DeleteBehavior.Cascade);

    modelBuilder.Entity<PersonelSchool>()
        .HasOne(ps => ps.School)
        .WithMany(s => s.PersonelSchools)
        .HasForeignKey(ps => ps.SchoolId)
        .OnDelete(DeleteBehavior.Cascade);
}

The problem is that the operation throws an error

System.InvalidOperationException: The instance of entity type 'PersonelSchool' cannot be tracked because another instance with the same key value for {'PersonelId', 'SchoolId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.

at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap1.ThrowIdentityConflict(InternalEntityEntry entry) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap1.Add(TKey key, InternalEntityEntry entry, Boolean updateDuplicate)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.IdentityMap1.Add(TKey key, InternalEntityEntry entry) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.NullableKeyIdentityMap1.Add(InternalEntityEntry entry)

If I add .AsNoTracking(), school names will be added into table however, I get the error again and the PersonelSchool table is null

Answer 1

You could just check for the pre-existing item before you insert the association item as in the code below - i.e. include the PersonnelSchools in the personnelsWithEBlagh, and check its contents before adding to it. You would probably be best off identifying why it is inserting items you don't expect it to as well though.

var personelsWithEblagh = 
   await db.Personels
        .Where(x => x.AreaId == Area.Id)
        .Include(p => p.Eblaghs)
        .Include(p => p.PersonelSchools   // Include the existing personnelschools
        .ToListAsync();

foreach (var personel in personelsWithEblagh)
{
foreach (var eblagh in personel.Eblaghs)
{
    var schoolName = eblagh.Vahed;

    var existingSchool = await db.Schools.FirstOrDefaultAsync(s => s.Name == schoolName);

    if (existingSchool == null)
    {
        var newSchool = new School { Name = schoolName };
        await db.Schools.AddAsync(newSchool);
        await db.SaveChangesAsync();
        existingSchool = newSchool;
    }

    if(!personel.PersonelSchools.Any(x=>x.SchoolId == existingSchool.Id)) // Check for existing item in the association table
    {
       personel.PersonelSchools.Add(new PersonelSchool
       {
           PersonelId = personel.Id,
           SchoolId = existingSchool.Id
       });
    }
}
}

await db.SaveChangesAsync();