Entity Framework DbModel: How to map one-to-many relationships using a join table?

Question

I am trying to match through DbModel this relation existing in the database.

CREATE TABLE core.Institutes 
(
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(128) NOT NULL,
    OldID INT NULL
)
GO

CREATE TABLE core.InstitutePlaces
(
    FKInstituteID INT NOT NULL PRIMARY KEY REFERENCES core.Institutes(ID),
    FKPlaceID INT NOT NULL REFERENCES core.Places(ID)
)
GO

CREATE TABLE core.Places
(
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(128) NOT NULL,
    FKParentID INT NULL REFERENCES core.Places(ID),
    OldID INT NULL
)
GO

on this model

public class Place
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
    public Place Parent { get; set; }
}

public class Institute
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Place Place { get; set; }
}

we use something like this to display

modelBuilder.Entity<Institutes.Institute>().HasOptional(i => i.Place);

but it does not work :(

This script is perfectly controlled by the EDML file, so the problem is only with the display.

Answer 1

Something like this will give you the (almost) desired scheme with one caveat: Code First does not create a 1: 1 relationship in entity splitting scenarios that your desired scheme (creating a 1: * association using a connection table) is a special case.

public class Place
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
    public Place Parent { get; set; }
}

public class Institute
{
    [DatabaseGenerated(DatabaseGenerationOption.None)]
    public int Id { get; set; }
    public string Name { get; set; }

    public int? PlaceId { get; set; }
    public Place Place { get; set; }
}

public class Context : DbContext
{
    public DbSet<Place> Places { get; set; }
    public DbSet<Institute> Institutes { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Institute>().Map(mc =>
        {
            mc.Properties(p => new { p.Id, p.Name });
            mc.ToTable("Institutes");
        })
        .Map(mc =>
        {
            mc.Properties(p => new { p.Id, p.PlaceId });
            mc.ToTable("InstitutePlaces");
        });

        modelBuilder.Entity<Place>()
                    .HasOptional(p => p.Parent)
                    .WithMany()
                    .HasForeignKey(p => p.ParentId);
    }
}

- , .