EF6 mapping many-to-many using same column name

Question

In an existing sqlite database I have 3 tables.
First a table with `events`, it uses a composite primary key of `id` and `licence_key`.
The Second table holds `codes`, this too uses a composite key of `session_code` and `licence_key`.
The final table is an associative table from the following sql:

CREATE TABLE `event_code` (
    `event_id`  INTEGER NOT NULL,
    `session_code`      TEXT NOT NULL,
    `licence_key`   TEXT NOT NULL,
    CONSTRAINT `event_code$event_id_session_code_licence_key_PK` PRIMARY KEY(`event_id` ASC,`session_code` ASC,`licence_key` ASC),
    CONSTRAINT `event_code$event_id_licence_key_FK` FOREIGN KEY(`event_id`, `licence_key`) REFERENCES `event`(`id`, `licence_key`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `event_code$session_code_licence_key_FK` FOREIGN KEY(`session_code`, `licence_key`) REFERENCES `code`(`session_code`, `licence_key`) ON UPDATE CASCADE ON DELETE CASCADE
);

My program uses entity framework and fluent mapping to load and store objects in these tables.
In EF6 I believe the relevant part of the mapping should look like this:

modelBuilder.Entity<EF6EventInformation>().HasMany(eventInfo => eventInfo.InternalSessionCodes).WithMany().Map(mapping =>
            {
                mapping.ToTable("event_code");
                mapping.MapLeftKey("event_id", "licence_key");
                mapping.MapRightKey("session_code", "licence_key");
            });

However, this throws an exception:

System.Data.Entity.ModelConfiguration.ModelValidationException: One or more validation errors were detected during model generation:

licence_key: Name: Each property name in a type must be unique. Property name 'licence_key' is already defined.

It seems I can not re-use the same column.
Of course I could change the database design and store the licence_key for both entities in their own separate columns, but because the value for each of those licence_keys would always have to match the other that does not seem particularly useful.

Is there any way to set this mapping up correctly without having to change my database design?

Answer 1

This seems to be a limitation of the EF6 mapping on many-to-many via implicit join table.

It's possible to map the relationship without changing the database structure, but changing the entity model by adding explicit join entity and mapping many-to-many as two many-to-one.

So you'd need an entity like this:

public class EventCode
{
    public int event_id { get; set; }
    public int session_code { get; set; }
    public int license_key { get; set; }
    public Event Event { get; set; }
    public Code Code { get; set; }
}

then change the existing collection navigation property to something like this:

public ICollection<EventCode> EventCodes { get; set; }

and use fluent configuration like this:

modelBuilder.Entity<EventCode>()
    .ToTable("event_code");
modelBuilder.Entity<EventCode>()
    .HasKey(e => new { e.event_id, e.session_code, e.license_key });
modelBuilder.Entity<EventCode>()
    .HasRequired(e => e.Event)
    .WithMany(e => e.EventCodes)
    .HasForeignKey(e => new { e.event_id, e.license_key });
modelBuilder.Entity<EventCode>()
    .HasRequired(e => e.Code)
    .WithMany()
    .HasForeignKey(e => new { e.session_code, e.license_key });

Of course you can create better C# conventional property names

public class EventCode
{
    public int EventId { get; set; }
    public int SessionCode { get; set; }
    public int LicenseKey { get; set; }
    public Event Event { get; set; }
    public Code Code { get; set; }
}

and map them to the existing table column names

but that doesn't change fundamentally the relationship mapping solution.

modelBuilder.Entity<EventCode>()
    .ToTable("Event_Code");
modelBuilder.Entity<EventCode>()
    .HasKey(e => new { e.EventId, e.SessionCode, e.LicenseKey });
modelBuilder.Entity<EventCode>().Property(e => e.EventId)
    .HasColumnName("event_id");
modelBuilder.Entity<EventCode>().Property(e => e.SessionCode)
    .HasColumnName("session_code");
modelBuilder.Entity<EventCode>().Property(e => e.LicenseKey)
    .HasColumnName("license_key");
modelBuilder.Entity<EventCode>()
    .HasRequired(e => e.Event)
    .WithMany(e => e.EventCodes)
    .HasForeignKey(e => new { e.EventId, e.LicenseKey });
modelBuilder.Entity<EventCode>()
    .HasRequired(e => e.Code)
    .WithMany()
    .HasForeignKey(e => new { e.SessionCode, e.LicenseKey });

Both ways of many-to-many mapping have pros and cons, but here there is simply no choice.