EF Core - duplicate entry in table index when adding record to table with composite keys

Question

I have a class with a composite key. When I try to save a record where a part of that key is contained in another record's key, I am getting an exception although the composite keys as a whole are unique.

Data type with navigation:

public class ProxyInfo
{
    [Key, Column(Order = 0, TypeName = "varchar")]
    [StringLength(80)]
    public string AccountID { get; set; } = string.Empty;

    [Key, Column(Order = 1, TypeName = "varchar")]
    [StringLength(80)]
    public string ProxyID { get; set; } = string.Empty;

    [ForeignKey(nameof(ProxyID))]
    public virtual UserInfo? Proxy { get; set; }
    // a few more properties
}

OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<ProxyInfo>()
    .HasKey(e => new { e.AccountID, e.ProxyID })
    .HasName("PK_ProxyInfo");

modelBuilder.Entity<ProxyInfo>()
    .HasOne(p => p.Proxy)
    .WithOne()
    .OnDelete(DeleteBehavior.NoAction);
// more code follows
}

Here's the relevant controller code:

[Route(Routes.Base + "[controller]")]
[ApiController]
public class ProxyInfoApiController : ControllerBase
{
    private readonly DS2DbContext _context;

    public ProxyInfoApiController(DS2DbContext context)
    {
        _context = context;
    }

[HttpPost("Add")]
[AllowAnonymous]
    public async Task<IActionResult> Add([FromBody] ProxyInfo proxyInfo)
    {
        try
        {
            _context.ProxyInfo.Add(proxyInfo);
            await _context.SaveChangesAsync();
        }
        catch (Exception e)
        {
            ServerGlobals.Logger?.Error($"ProxyInfoController.Add: Error '{e.Message}' occurred.");
        }
        return Ok(); // Created(proxyInfo.ID.ToString(), proxyInfo);
    }
}

The error message reads:

A row with a duplicate key cannot be inserted in the dbo.ProxyInfo
object with the unique IX_ProxyInfo_ProxyID-Index. The duplicate
key value is "X".

The complex key I tried to insert was {"B","X"}. The only other record in the ProxyInfo table has key {"A", "X"}; So there should be two different ProxyInfo records referencing the same UserInfo record.

The problem seems to be that an index is being tried to be updated with a value it already contains. However, the indices of both records can be identical, as multiple ProxyInfos can reference the same UserInfo. So actually, no duplicate entry is created. It's just that a 2nd ProxyInfo record uses the same user as the 1st one.

I just found out that the relevant index is created during initial migration with a unique:true attribute. The question is whether I can make EF Core skip updating the index when it already contains an index that it is trying to add again.

Answer 1

I found the problem. It was this statement:

modelBuilder.Entity<ProxyInfo>()
    .HasOne(p => p.Proxy)
    .WithOne()
    .OnDelete(DeleteBehavior.NoAction);

which should have been

modelBuilder.Entity<ProxyInfo>()
    .HasOne(p => p.Proxy)
    .WithMany()
    .OnDelete(DeleteBehavior.NoAction);

What I also didn't know is that when I make changes like this one, I need to create and execute a migration. Once I did that, the index got changed into a non unique one.

I am only doing EF Core/Blazor for a year and I am dealing more with application development than with the framework around it, so this is all new to me and it took me a while to figure it out.