Can I set the primary key order of an automatically generated many to many join table through fluent-CodePudding

I have the following entities:

[Table("Agents")]
public class Agent
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [MaxLength(100)]
    public string? Name { get; set; }

    public List<Itinerary> Itineraries { get; set; };
}

[Table("Itineraries")]
public class Itinerary
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [MaxLength(50)]
    public string? Code { get; set; }

    [MaxLength(4000)]
    public string? Name { get; set; }

    public IList<Agent> Agents { get; set; }
}

along with the following configuration:

public void Configure(EntityTypeBuilder<Itinerary> builder)
{
    builder
        .HasMany(e => e.Agents)
        .WithMany(e => e.Itineraries)
        .UsingEntity(join => join.ToTable("ItineraryAgent"));
}

Creating a migration on this results in a join table automatically created with the following properties:

migrationBuilder.CreateTable(
    name: "ItineraryAgent",
    columns: table => new
    {
        AgentsId = table.Column<int>(type: "int", nullable: false),
        ItinerariesId = table.Column<int>(type: "int", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_ItineraryAgent", x => new { x.AgentsId, x.ItinerariesId });
        ...

Is there a way to change the order of the primary key through the fluent configuration without having to create an appropriate entity class for the join table, i.e. have the primary key as new { x.ItinerariesId, x.AgentsId }.

EF seems to create the primary key order alphabetically, so if the Itinerary.Agents property is renamed to Itinerary.ZAgents, the order of the primary key will be as I want it.

CodePudding user response：

EF seems to create the primary key order alphabetically

Indeed. But they have no many options here, since the order of conventional discovering the entity types is undefined.

Is there a way to change the order of the primary key through the fluent configuration without having to create an appropriate entity class for the join table?

Yes, it's possible, but you have to know the conventional shadow FK names created by EF, and then use the string[] overload of HasKey, e.g.

.UsingEntity(join =>
{   
    join.ToTable("ItineraryAgent");
    join.HasKey("ItinerariesId", "AgentsId");
});

It's really annoying that there are about ten overloads for UsingEntity, but no simple way to say which entity is "left" and which is "right" side of the relationship, since usually that controls the name of the join table and the order of the PK columns.

But it is what it is. May be some day we'll be able to do that simpler, for now this is the way.