✅ Problem connecting entities on efcore

lllggghhhaaa · 2024-10-01T18:40:00.684Z

I'm new with blazor and efcore and i'm in trouble to connect 2 relation Error: `---> Npgsql.PostgresException (0x80004005): 23505: Duplicate key value violates unique constraint "PK_Blocks"` Method: ```cs public async Task AddPaletteAsync(Database.Palette palette, ApplicationUser user) { await using var context = await dbContextFactory.CreateDbContextAsync(); palette.UserId = user.Id; context.Palettes.Add(palette); await context.SaveChangesAsync(); } ```

J

Jimmacle•10/1/24, 6:40 PM

the error is saying that youre trying to insert an entity with a primary key value that another entity in the table already has

JJimmacle the error is saying that youre trying to insert an entity with a primary key val...

L

lllggghhhaaaOP•10/1/24, 6:42 PM

im trying to create a new palette on db and connect blocks from another table to the palette, but it looks the db is trying to create another blocks insteaded connect

L

lllggghhhaaaOP•10/1/24, 6:42 PM

public class DatabaseContext(DbContextOptions<DatabaseContext> options, IConfiguration config) : IdentityDbContext<ApplicationUser>(options)
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql(config["Database:ConnectionString"]);

    public DbSet<Block> Blocks { get; set; }
    public DbSet<Palette> Palettes { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Block>()
            .HasIndex(b => b.MinecraftId)
            .IsUnique();

        modelBuilder.Entity<Palette>()
            .HasMany(p => p.Blocks)
            .WithMany(b => b.Palettes)
            .UsingEntity(j => j.ToTable("PaletteBlocks"));

        modelBuilder.Entity<Palette>()
            .HasOne(p => p.PlaceholderBlock)
            .WithMany()
            .HasForeignKey(p => p.PlaceholderBlockId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<ApplicationUser>()
            .HasMany(u => u.Palettes)
            .WithOne(p => p.User)
            .HasForeignKey(p => p.UserId);
        
        modelBuilder.Entity<IdentityUserLogin<string>>(entity =>
        {
            entity.HasKey(l => new { l.LoginProvider, l.ProviderKey });
        });

        modelBuilder.Entity<IdentityUserRole<string>>(entity =>
        {
            entity.HasKey(r => new { r.UserId, r.RoleId });
        });

        modelBuilder.Entity<IdentityUserToken<string>>(entity =>
        {
            entity.HasKey(t => new { t.UserId, t.LoginProvider, t.Name });
        });
    }
}

public class DatabaseContext(DbContextOptions<DatabaseContext> options, IConfiguration config) : IdentityDbContext<ApplicationUser>(options)
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql(config["Database:ConnectionString"]);

    public DbSet<Block> Blocks { get; set; }
    public DbSet<Palette> Palettes { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Block>()
            .HasIndex(b => b.MinecraftId)
            .IsUnique();

        modelBuilder.Entity<Palette>()
            .HasMany(p => p.Blocks)
            .WithMany(b => b.Palettes)
            .UsingEntity(j => j.ToTable("PaletteBlocks"));

        modelBuilder.Entity<Palette>()
            .HasOne(p => p.PlaceholderBlock)
            .WithMany()
            .HasForeignKey(p => p.PlaceholderBlockId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<ApplicationUser>()
            .HasMany(u => u.Palettes)
            .WithOne(p => p.User)
            .HasForeignKey(p => p.UserId);
        
        modelBuilder.Entity<IdentityUserLogin<string>>(entity =>
        {
            entity.HasKey(l => new { l.LoginProvider, l.ProviderKey });
        });

        modelBuilder.Entity<IdentityUserRole<string>>(entity =>
        {
            entity.HasKey(r => new { r.UserId, r.RoleId });
        });

        modelBuilder.Entity<IdentityUserToken<string>>(entity =>
        {
            entity.HasKey(t => new { t.UserId, t.LoginProvider, t.Name });
        });
    }
}

public class DatabaseContext(DbContextOptions<DatabaseContext> options, IConfiguration config) : IdentityDbContext<ApplicationUser>(options)
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql(config["Database:ConnectionString"]);

    public DbSet<Block> Blocks { get; set; }
    public DbSet<Palette> Palettes { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Block>()
            .HasIndex(b => b.MinecraftId)
            .IsUnique();

        modelBuilder.Entity<Palette>()
            .HasMany(p => p.Blocks)
            .WithMany(b => b.Palettes)
            .UsingEntity(j => j.ToTable("PaletteBlocks"));

        modelBuilder.Entity<Palette>()
            .HasOne(p => p.PlaceholderBlock)
            .WithMany()
            .HasForeignKey(p => p.PlaceholderBlockId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<ApplicationUser>()
            .HasMany(u => u.Palettes)
            .WithOne(p => p.User)
            .HasForeignKey(p => p.UserId);
        
        modelBuilder.Entity<IdentityUserLogin<string>>(entity =>
        {
            entity.HasKey(l => new { l.LoginProvider, l.ProviderKey });
        });

        modelBuilder.Entity<IdentityUserRole<string>>(entity =>
        {
            entity.HasKey(r => new { r.UserId, r.RoleId });
        });

        modelBuilder.Entity<IdentityUserToken<string>>(entity =>
        {
            entity.HasKey(t => new { t.UserId, t.LoginProvider, t.Name });
        });
    }
}

public class DatabaseContext(DbContextOptions<DatabaseContext> options, IConfiguration config) : IdentityDbContext<ApplicationUser>(options)
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseNpgsql(config["Database:ConnectionString"]);

    public DbSet<Block> Blocks { get; set; }
    public DbSet<Palette> Palettes { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Block>()
            .HasIndex(b => b.MinecraftId)
            .IsUnique();

        modelBuilder.Entity<Palette>()
            .HasMany(p => p.Blocks)
            .WithMany(b => b.Palettes)
            .UsingEntity(j => j.ToTable("PaletteBlocks"));

        modelBuilder.Entity<Palette>()
            .HasOne(p => p.PlaceholderBlock)
            .WithMany()
            .HasForeignKey(p => p.PlaceholderBlockId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<ApplicationUser>()
            .HasMany(u => u.Palettes)
            .WithOne(p => p.User)
            .HasForeignKey(p => p.UserId);
        
        modelBuilder.Entity<IdentityUserLogin<string>>(entity =>
        {
            entity.HasKey(l => new { l.LoginProvider, l.ProviderKey });
        });

        modelBuilder.Entity<IdentityUserRole<string>>(entity =>
        {
            entity.HasKey(r => new { r.UserId, r.RoleId });
        });

        modelBuilder.Entity<IdentityUserToken<string>>(entity =>
        {
            entity.HasKey(t => new { t.UserId, t.LoginProvider, t.Name });
        });
    }
}

My DB

L

lllggghhhaaaOP•10/1/24, 6:42 PM

public class Block
{
    public Guid Id { get; set; } = Guid.CreateVersion7();
    public string MinecraftId { get; set; }
    public int MapId { get; set; }
    public string? ImageUrl { get; set; }

    public Dictionary<string, string> Properties { get; set; } = new();
    public Dictionary<string, string> GeneratorProperties { get; set; } = new();

    public List<Palette> Palettes { get; set; } = [];
}

public class Palette
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Name { get; set; }

    public Guid PlaceholderBlockId { get; set; }
    public Block PlaceholderBlock { get; set; }

    public List<Block> Blocks { get; set; } = [];

    public string UserId { get; set; }
    public ApplicationUser User { get; set; }
}

public class ApplicationUser : IdentityUser
{
    public List<Palette> Palettes { get; set; } = [];
}

public class Block
{
    public Guid Id { get; set; } = Guid.CreateVersion7();
    public string MinecraftId { get; set; }
    public int MapId { get; set; }
    public string? ImageUrl { get; set; }

    public Dictionary<string, string> Properties { get; set; } = new();
    public Dictionary<string, string> GeneratorProperties { get; set; } = new();

    public List<Palette> Palettes { get; set; } = [];
}

public class Palette
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Name { get; set; }

    public Guid PlaceholderBlockId { get; set; }
    public Block PlaceholderBlock { get; set; }

    public List<Block> Blocks { get; set; } = [];

    public string UserId { get; set; }
    public ApplicationUser User { get; set; }
}

public class ApplicationUser : IdentityUser
{
    public List<Palette> Palettes { get; set; } = [];
}

public class Block
{
    public Guid Id { get; set; } = Guid.CreateVersion7();
    public string MinecraftId { get; set; }
    public int MapId { get; set; }
    public string? ImageUrl { get; set; }

    public Dictionary<string, string> Properties { get; set; } = new();
    public Dictionary<string, string> GeneratorProperties { get; set; } = new();

    public List<Palette> Palettes { get; set; } = [];
}

public class Palette
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Name { get; set; }

    public Guid PlaceholderBlockId { get; set; }
    public Block PlaceholderBlock { get; set; }

    public List<Block> Blocks { get; set; } = [];

    public string UserId { get; set; }
    public ApplicationUser User { get; set; }
}

public class ApplicationUser : IdentityUser
{
    public List<Palette> Palettes { get; set; } = [];
}

public class Block
{
    public Guid Id { get; set; } = Guid.CreateVersion7();
    public string MinecraftId { get; set; }
    public int MapId { get; set; }
    public string? ImageUrl { get; set; }

    public Dictionary<string, string> Properties { get; set; } = new();
    public Dictionary<string, string> GeneratorProperties { get; set; } = new();

    public List<Palette> Palettes { get; set; } = [];
}

public class Palette
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string Name { get; set; }

    public Guid PlaceholderBlockId { get; set; }
    public Block PlaceholderBlock { get; set; }

    public List<Block> Blocks { get; set; } = [];

    public string UserId { get; set; }
    public ApplicationUser User { get; set; }
}

public class ApplicationUser : IdentityUser
{
    public List<Palette> Palettes { get; set; } = [];
}

L

lllggghhhaaaOP•10/1/24, 6:43 PM

palette and blocks is a many to many relation

T

TeBeCo•10/1/24, 6:43 PM

guid is fucking bad for db and id

T

TeBeCo•10/1/24, 6:43 PM

just so it's clear upfront

T

TeBeCo•10/1/24, 6:43 PM

now comes the question

T

TeBeCo•10/1/24, 6:44 PM

what's one of the biggest job of a db ?

T

TeBeCo•10/1/24, 6:44 PM

indexing storing finding

T

TeBeCo•10/1/24, 6:44 PM

based on ... id

T

TeBeCo•10/1/24, 6:44 PM

so let your db generate the id

T

TeBeCo•10/1/24, 6:44 PM

because it's like one of the most core component of it

T

TeBeCo•10/1/24, 6:45 PM

so remove your code generating id from c#

T

TeBeCo•10/1/24, 6:45 PM

let your db do it job

T

TeBeCo•10/1/24, 6:45 PM

and just use that id from the db as an id

T

TeBeCo•10/1/24, 6:46 PM

if you want to insist on "my csharp app know better"

T

TeBeCo•10/1/24, 6:46 PM

then add an attribute to tell efcore you're the one owning id generation

T

TeBeCo•10/1/24, 6:47 PM

it's honestly not always a good idea but hey, your choice

T

TeBeCo•10/1/24, 6:48 PM

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public required ????????????? Id { get; set; }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public required ????????????? Id { get; set; }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public required ????????????? Id { get; set; }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public required ????????????? Id { get; set; }

TTeBeCo ``` [Key] [DatabaseGenerated(DatabaseGeneratedOption.None)] public r...

L

lllggghhhaaaOP•10/1/24, 7:09 PM

still having issues, i think the problem is not related with keys

T

TeBeCo•10/2/24, 4:35 AM

it is

T

TeBeCo•10/2/24, 4:35 AM

T

TeBeCo•10/2/24, 4:35 AM

that's what I said ... you're NEVER supposed to do that EVER

T

TeBeCo•10/2/24, 4:35 AM

you're supposed to tell the DB to do it job and generate id for you

T

TeBeCo•10/2/24, 4:35 AM

not that

T

TeBeCo•10/2/24, 4:36 AM

Id

Id

is an automaticly detected guidance naming thing

T

TeBeCo•10/2/24, 4:36 AM

as per doc

T

TeBeCo•10/2/24, 4:36 AM

Id = automatically PK = automatically indentifier

T

TeBeCo•10/2/24, 4:36 AM

so because you kinda violated that "normal naming and override the default behavior"

T

TeBeCo•10/2/24, 4:36 AM

you need to inform EFCore

T

TeBeCo•10/2/24, 4:37 AM

as for the error

Duplicate key value violates unique constraint "PK_Blocks"

Duplicate key value violates unique constraint "PK_Blocks"

Duplicate key value violates unique constraint "PK_Blocks"

Duplicate key value violates unique constraint "PK_Blocks"

T

TeBeCo•10/2/24, 4:37 AM

well it's obvious

T

TeBeCo•10/2/24, 4:37 AM

let's split that error:

T

TeBeCo•10/2/24, 4:37 AM

constraint "PK_Blocks"

constraint "PK_Blocks"

constraint "PK_Blocks"

constraint "PK_Blocks"

T

TeBeCo•10/2/24, 4:37 AM

PK

PK

PK

PK => Primary Key =>

Id

Id

T

TeBeCo•10/2/24, 4:37 AM

Blocks

Blocks

Blocks

Blocks table

T

TeBeCo•10/2/24, 4:38 AM

Efcore ?

public required DbSet<Block> Blocks {get;set}

public required DbSet<Block> Blocks {get;set}

public required DbSet<Block> Blocks {get;set}

public required DbSet<Block> Blocks {get;set}

T

TeBeCo•10/2/24, 4:38 AM

T

TeBeCo•10/2/24, 4:39 AM

you probably tried to Add or Update 2 Block and have a bug in your code where you did set the same

Guid

Guid

Guid

Guid

T

TeBeCo•10/2/24, 4:39 AM

even if you have an

 = Guild.CreateVersion7()

= Guild.CreateVersion7()

 = Guild.CreateVersion7()

= Guild.CreateVersion7()

T

TeBeCo•10/2/24, 4:39 AM

you might have done

block.Id = <value>

block.Id = <value>

block.Id = <value>

block.Id = <value> somewhere

TTeBeCo you probably tried to Add or Update 2 Block and have a bug in your code where yo...

L

lllggghhhaaaOP•10/2/24, 11:19 AM

nope, this problem is occuring because im trying to insert the palette on another dbcontext that i get the blocks, so the blocks is no more tracked by the dbcontext and the efcore tries to create a new block insteaded connect with the palette

T

TeBeCo•10/2/24, 12:13 PM

i'm not sure what you're explaining

T

TeBeCo•10/2/24, 12:13 PM

though the error is ultra explicit

T

TeBeCo•10/2/24, 12:14 PM

unless the error changed ... the previous error is that you're trying to shove duplicated same ID on PK_Blocks

T

TeBeCo•10/2/24, 12:14 PM

PK as in ... primary key

T

TeBeCo•10/2/24, 12:14 PM

Blocks as the tables Blocks

T

TeBeCo•10/2/24, 12:14 PM

like the entity Block

T

TeBeCo•10/2/24, 12:14 PM

and the database is telling you

✅ Problem connecting entities on efcore

Similar Threads

Similar Threads

Similar Threads