C#•4mo ago

✅ EntityFramework Core trying to add Navigation Property that already exists

I'm getting this fault:

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'UNIQUE constraint failed: AspNetUsers.NormalizedUserName'.

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'UNIQUE constraint failed: AspNetUsers.NormalizedUserName'.

Entity class:

public class PlanEntity : ISortable {
    public int Id { get; set; }
    public int SortPriority { get; set; }
    public string Label { get; set; }
    public List<TaskSetEntity> TaskSets { get; set; } = [];

    public DateTime? StartDate { get; set; }
    public List<ApplicationUser> Owners { get; set; } = [];
    public string[] Sites { get; set; } = [];
}

public class PlanEntity : ISortable {
    public int Id { get; set; }
    public int SortPriority { get; set; }
    public string Label { get; set; }
    public List<TaskSetEntity> TaskSets { get; set; } = [];

    public DateTime? StartDate { get; set; }
    public List<ApplicationUser> Owners { get; set; } = [];
    public string[] Sites { get; set; } = [];
}

The owners property linked:

public class ApplicationUser : IdentityUser {
    [Required, MaxLength(55)]
    public string Name { get; set; }

}

public class ApplicationUser : IdentityUser {
    [Required, MaxLength(55)]
    public string Name { get; set; }

}

The model builder:

public static partial class ModelBuilderExtensions {
    public static ModelBuilder AddPlanEntities(this ModelBuilder modelBuilder) {
        modelBuilder.Entity<PlanEntity>()
            .ToTable("RefactoredPlans")
            .HasMany(x => x.TaskSets)
            .WithOne()
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<PlanEntity>()
            .ToTable("RefactoredPlans")
            .HasMany(x => x.Owners)
            .WithMany();

        return modelBuilder;
    }
}

public static partial class ModelBuilderExtensions {
    public static ModelBuilder AddPlanEntities(this ModelBuilder modelBuilder) {
        modelBuilder.Entity<PlanEntity>()
            .ToTable("RefactoredPlans")
            .HasMany(x => x.TaskSets)
            .WithOne()
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<PlanEntity>()
            .ToTable("RefactoredPlans")
            .HasMany(x => x.Owners)
            .WithMany();

        return modelBuilder;
    }
}

15 Replies

SaiyanslayerOP•4mo ago

The code that triggers the error:

        var principal = _authStateProvider.GetAuthenticationStateAsync().Result.User;
        if (principal is null) { return null; }

        var user = _userManager.GetUserAsync(principal);
        var newPlan = await query
            .Include(x => x.OrderedSets)
            .ThenInclude(x => x.Child)
            .ThenInclude(x => x.Checks)
            .Include(x => x.OrderedSets)
            .ThenInclude (x => x.Child)
            .Select(template => new PlanEntity {
                Label = template.Label,
                SortPriority = patient.Plans.Count, //add to the last of the list
                TaskSets = template
                    .OrderedSets
                    .Select(x => x.Child.ToTaskSetEntity(x.SortPriority))
                    .ToList(),
                Owners = new List<ApplicationUser> { user }})
            .SingleOrDefaultAsync();

        var principal = _authStateProvider.GetAuthenticationStateAsync().Result.User;
        if (principal is null) { return null; }

        var user = _userManager.GetUserAsync(principal);
        var newPlan = await query
            .Include(x => x.OrderedSets)
            .ThenInclude(x => x.Child)
            .ThenInclude(x => x.Checks)
            .Include(x => x.OrderedSets)
            .ThenInclude (x => x.Child)
            .Select(template => new PlanEntity {
                Label = template.Label,
                SortPriority = patient.Plans.Count, //add to the last of the list
                TaskSets = template
                    .OrderedSets
                    .Select(x => x.Child.ToTaskSetEntity(x.SortPriority))
                    .ToList(),
                Owners = new List<ApplicationUser> { user }})
            .SingleOrDefaultAsync();

I suspect the Owners = new List<ApplicationUser> { user } is causing it by trying to add a new entry into the AspNetUsers table instead of adding the Id into navigation table.

Core•4mo ago

I suspect that is exactly what's happening too

SaiyanslayerOP•4mo ago

How do i force it to only focus on linking the ids?

Core•4mo ago

Is EF tracking disabled? The code seems to be correct, because you are using the existing user, queried by _userManager.GetUserAsync(principal). This should be already attached to the context, which means that it should not be treated as a new entry (e.g. Owners = new List<ApplicationUser> { user }}))

SaiyanslayerOP•4mo ago

that call is actually within another function. I added it for clairity. I'll try integrating it ya no luck same issue and i dont think tracking is disabled. I used AsNoTracking only for the plans data ok, I added:

var owners = await context
    .Users
    .Where(x => x.Id == user.Id)
    .ToListAsync();

var owners = await context
    .Users
    .Where(x => x.Id == user.Id)
    .ToListAsync();

and that worked. Users is the DbSet<ApplicationUser> property heavy-handed, but it worked

Core•4mo ago

Great you figured it out, I would still like to know why the initial solution didn't work

SaiyanslayerOP•4mo ago

ya, same maybe using the UserManager doesn't cause the value to be tracked?

Core•4mo ago

try to add the following after you select the user with the userManager: _dbContext.Database.Attach(user)

SaiyanslayerOP•4mo ago

Core•4mo ago

this is how you manually attach untracked entities to the context it should throw an exception if the entity is already tracked

SaiyanslayerOP•4mo ago

ohhh, I was calling the user outside of the context here's the whole method:

    public async Task<Result<Plan>> Handle(Request request) {

        using (var context = await _contextFactory.CreateDbContextAsync()) {
            var user = await GetCurrentUser();

            if (user is null) {
                return new Error("AddPlanToPatient", "current user not found");
            }

            var query = context
                .PlanTemplates
                .AsNoTracking()
                .Where(x => x.Id == request.planId);

            var exists = await query.SingleOrDefaultAsync();
            if (exists is null) { return new Error("AddPlanToPatient", $"Plan Id:{request.planId} does not exist"); }

            var patient = await context
                .PatientsRefactor
                .Where(x => x.Id == request.patientId)
                .Include(x => x.Plans)
                .ThenInclude(x => x.TaskSets)
                .ThenInclude(x => x.Checks)
                .SingleOrDefaultAsync();

            if (patient is null) { return new Error("AddPlanToPatient", $"Patient Id:{request.patientId} does not exist"); }

    public async Task<Result<Plan>> Handle(Request request) {

        using (var context = await _contextFactory.CreateDbContextAsync()) {
            var user = await GetCurrentUser();

            if (user is null) {
                return new Error("AddPlanToPatient", "current user not found");
            }

            var query = context
                .PlanTemplates
                .AsNoTracking()
                .Where(x => x.Id == request.planId);

            var exists = await query.SingleOrDefaultAsync();
            if (exists is null) { return new Error("AddPlanToPatient", $"Plan Id:{request.planId} does not exist"); }

            var patient = await context
                .PatientsRefactor
                .Where(x => x.Id == request.patientId)
                .Include(x => x.Plans)
                .ThenInclude(x => x.TaskSets)
                .ThenInclude(x => x.Checks)
                .SingleOrDefaultAsync();

            if (patient is null) { return new Error("AddPlanToPatient", $"Patient Id:{request.patientId} does not exist"); }

            var owners = await context
                .Users
                .Where(x => x.Id == user.Id)
                .ToListAsync();

            var newPlan = await query
                .Include(x => x.OrderedSets)
                .ThenInclude(x => x.Child)
                .ThenInclude(x => x.Checks)
                .Include(x => x.OrderedSets)
                .ThenInclude (x => x.Child)
                .Select(template => new PlanEntity {
                    Label = template.Label,
                    SortPriority = patient.Plans.Count, //add to the last of the list
                    TaskSets = template
                        .OrderedSets
                        .Select(x => x.Child.ToTaskSetEntity(x.SortPriority))
                        .ToList(),
                    Owners = new List<ApplicationUser> { user }
                })
                .SingleOrDefaultAsync();

            patient.Plans.Add(newPlan);
            await context.SaveChangesAsync();

            return newPlan.Id > 0
                ? newPlan.ToPlan()
                : new Error("AddPlanToPatient", $"plan did not save");

        }
    }

            var owners = await context
                .Users
                .Where(x => x.Id == user.Id)
                .ToListAsync();

            var newPlan = await query
                .Include(x => x.OrderedSets)
                .ThenInclude(x => x.Child)
                .ThenInclude(x => x.Checks)
                .Include(x => x.OrderedSets)
                .ThenInclude (x => x.Child)
                .Select(template => new PlanEntity {
                    Label = template.Label,
                    SortPriority = patient.Plans.Count, //add to the last of the list
                    TaskSets = template
                        .OrderedSets
                        .Select(x => x.Child.ToTaskSetEntity(x.SortPriority))
                        .ToList(),
                    Owners = new List<ApplicationUser> { user }
                })
                .SingleOrDefaultAsync();

            patient.Plans.Add(newPlan);
            await context.SaveChangesAsync();

            return newPlan.Id > 0
                ? newPlan.ToPlan()
                : new Error("AddPlanToPatient", $"plan did not save");

        }
    }

this is what it was:

    public async Task<Result<Plan>> Handle(Request request) {
        var user = await GetCurrentUser();

        if (user is null) {
            return new Error("AddPlanToPatient", "current user not found");
        }

        using (var context = await _contextFactory.CreateDbContextAsync()) {
            var query = context
                .PlanTemplates
                .AsNoTracking()
                .Where(x => x.Id == request.planId);

    public async Task<Result<Plan>> Handle(Request request) {
        var user = await GetCurrentUser();

        if (user is null) {
            return new Error("AddPlanToPatient", "current user not found");
        }

        using (var context = await _contextFactory.CreateDbContextAsync()) {
            var query = context
                .PlanTemplates
                .AsNoTracking()
                .Where(x => x.Id == request.planId);

so i was looking for the user outside of establishing the context with the using one sec, i have to reform the code to test nvm, issue remains if i move the user call within the using

 DatabaseFacade does not contain a definition for 'Attach'

 DatabaseFacade does not contain a definition for 'Attach'

ah, it's context.Attach(user); ok, that worked.

    public async Task<Result<Plan>> Handle(Request request) {

       using (var context = await _contextFactory.CreateDbContextAsync()) {
        var user = await GetCurrentUser();

        if (user is null) {
            return new Error("AddPlanToPatient", "current user not found");
        }

           context.Attach(user);

        var query = context
               .PlanTemplates
               .AsNoTracking()
               .Where(x => x.Id == request.planId);

           var exists = await query.SingleOrDefaultAsync();
           if (exists is null) { return new Error("AddPlanToPatient", $"Plan Id:{request.planId} does not exist"); }

           var patient = await context
               .PatientsRefactor
               .Where(x => x.Id == request.patientId)
               .Include(x => x.Plans)
               .ThenInclude(x => x.TaskSets)
               .ThenInclude(x => x.Checks)
               .SingleOrDefaultAsync();

    public async Task<Result<Plan>> Handle(Request request) {

       using (var context = await _contextFactory.CreateDbContextAsync()) {
        var user = await GetCurrentUser();

        if (user is null) {
            return new Error("AddPlanToPatient", "current user not found");
        }

           context.Attach(user);

        var query = context
               .PlanTemplates
               .AsNoTracking()
               .Where(x => x.Id == request.planId);

           var exists = await query.SingleOrDefaultAsync();
           if (exists is null) { return new Error("AddPlanToPatient", $"Plan Id:{request.planId} does not exist"); }

           var patient = await context
               .PatientsRefactor
               .Where(x => x.Id == request.patientId)
               .Include(x => x.Plans)
               .ThenInclude(x => x.TaskSets)
               .ThenInclude(x => x.Checks)
               .SingleOrDefaultAsync();

           if (patient is null) { return new Error("AddPlanToPatient", $"Patient Id:{request.patientId} does not exist"); }

        var newPlan = await query
               .Include(x => x.OrderedSets)
               .ThenInclude(x => x.Child)
               .ThenInclude(x => x.Checks)
               .Include(x => x.OrderedSets)
               .ThenInclude (x => x.Child)
               .Select(template => new PlanEntity {
                   Label = template.Label,
                   SortPriority = patient.Plans.Count, //add to the last of the list
                   TaskSets = template
                       .OrderedSets
                       .Select(x => x.Child.ToTaskSetEntity(x.SortPriority))
                       .ToList(),
                   Owners = new List<ApplicationUser> { user }
               })
               .SingleOrDefaultAsync();

           patient.Plans.Add(newPlan);
           await context.SaveChangesAsync();

           return newPlan.Id > 0
               ? newPlan.ToPlan()
               : new Error("AddPlanToPatient", $"plan did not save");

       }
   }

           if (patient is null) { return new Error("AddPlanToPatient", $"Patient Id:{request.patientId} does not exist"); }

        var newPlan = await query
               .Include(x => x.OrderedSets)
               .ThenInclude(x => x.Child)
               .ThenInclude(x => x.Checks)
               .Include(x => x.OrderedSets)
               .ThenInclude (x => x.Child)
               .Select(template => new PlanEntity {
                   Label = template.Label,
                   SortPriority = patient.Plans.Count, //add to the last of the list
                   TaskSets = template
                       .OrderedSets
                       .Select(x => x.Child.ToTaskSetEntity(x.SortPriority))
                       .ToList(),
                   Owners = new List<ApplicationUser> { user }
               })
               .SingleOrDefaultAsync();

           patient.Plans.Add(newPlan);
           await context.SaveChangesAsync();

           return newPlan.Id > 0
               ? newPlan.ToPlan()
               : new Error("AddPlanToPatient", $"plan did not save");

       }
   }

Core•4mo ago

Great, it turns out the user had not been tracked by EF

SaiyanslayerOP•4mo ago

ya, thansk a bunch. Guess UserManager doesn't track at all

Core•4mo ago

$close

MODiX•4mo ago

If you have no further questions, please use /close to mark the forum thread as answered

Gaming

Programming

✅ EntityFramework Core trying to add Navigation Property that already exists