-1

In my application there is a many-to-many relationship between two entities - Bill and User. Thus a user can have many bills and a bill can apply to many users.

User{
  public string Id{get; set;}
  public ICollection<Bill> Bills {get; set;}
}

Bill{
  public int Id{get; set;}
  public ICollection<User> Users {get; set;}
}

Using EF Core 5, I learnt I should be able to ignore the join table of UserBill that contains the IDs of both Entities and a navigational properties to each of the other entities. However, I wish to make sure that no bill occurs twice for a given User.

UserBill{
  public string UserId{get; set;}
  public User Payer{get; set;}

  public int BillId{get; set;}
  public Bill Bill {get; set;}
}

So I wish to modify the join table as follows:

  1. Add unique Constraint to the join table but since the join table is automatically generated by EF Core, I am looking for a way to add this unique constraint to the join table

  2. Add other properties to the join table. For instance, I need to add PaymentStatus to the join table to indicate whether the bill has been paid and other properties where necessary

    UserBill{
public int Id {get; set;}

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

public int BillId{get; set;}
public Bill Bill {get; set;}
public PaymentStatus Status {get; set;}
}

Here I now need to add unique constraint to ensure that there cannot be a multiple occurrence of

UserId,BillId

Any assistance to get this done will be appreciated. Thank you.

Josh
  • 1,660
  • 5
  • 33
  • 55
  • it looks like at first that you just need to create the unique constraint but then you seem to want to add one `ID` column to the joining table. So it's very confusing. The problem is interesting at first but then turns out very simple because if you have a declared joining entity type, just configure it directly. In case you don't want that joining entity & no need for `ID` column (which is unusual for a joining entity type), you can take a look at the `UsingEntity` method as answered here https://stackoverflow.com/questions/64919574/change-name-of-generated-join-table-many-to-many-ef-core-5 – King King Mar 10 '21 at 19:29
  • the configuration for the joining entity type with `UsingEntity` is not strongly-typed, so here in this case you may need to use the `HasIndex` accepting an array of strings (column names). You need to guess the column names by convention (or explicitly configure the FK to know the exact names), e.g: `join.HasIndex(new[]{ "UserId", "BillId" }).IsUnique()` – King King Mar 10 '21 at 19:31

2 Answers2

0

HasIndex with anonymous type selector allows to create a composite index:

modelBuilder.Entity<UserBill>()
    .HasIndex(p => new { p.UserId, p.BillId})
    .IsUnique();
Guru Stron
  • 102,774
  • 10
  • 95
  • 132
0

Alternative is using HasKey and remove Id from UserBill

modelBuilder.Entity<UserBill>()
            .HasKey(p => new {p.UserId, p.BillId});
Genusatplay
  • 761
  • 1
  • 4
  • 15