Entities in X participate in the Y relationship. 0 related Target were found. 1 Target is expected

Question

I am having an issue with a 1:0..1 relationship. I get the error: Entities in 'TestContext.Environments' participate in the 'PortalEnvironment_BaEnvironment' relationship. 0 related 'PortalEnvironment_BaEnvironment_Target' were found. 1 'PortalEnvironment_BaEnvironment_Target' is expected.

I have 3 tables, implementing Table-Per-Type.

Below is the simplified version of the SQL / C# code I am using for my test:

CREATE TABLE dbo.EnvironmentBase(
    EnvironmentId int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
);

CREATE TABLE dbo.BAEnvironment(
    EnvironmentId int NOT NULL PRIMARY KEY CLUSTERED,
    CONSTRAINT FK_BAEnvironment_EnvironmentBase FOREIGN KEY(EnvironmentId) 
        REFERENCES dbo.EnvironmentBase (EnvironmentId) ON DELETE CASCADE
);

CREATE TABLE dbo.PortalEnvironment(
    EnvironmentId int NOT NULL PRIMARY KEY CLUSTERED,
    BAEnvironmentId int NOT NULL,
    CONSTRAINT FK_PortalEnvironment_BAEnvironment FOREIGN KEY(BAEnvironmentId) 
        REFERENCES dbo.BAEnvironment (EnvironmentId) ,
    CONSTRAINT FK_PortalEnvironment_EnvironmentBase FOREIGN KEY(EnvironmentId) 
        REFERENCES dbo.EnvironmentBase (EnvironmentId) ON DELETE CASCADE
);

While working through my issues, I used used the following article to help out One to Zero/One Relationship in EF utilizing Option2Mimic (independent association) as that seems to be the most appropriate for my situation. However, I get the error shown above.

POCOs:

public abstract partial class Environment
{
    public int Id { get; set; } // EnvironmentId (Primary key)

    public Environment()
    {
        InitializePartial();
    }

    partial void InitializePartial();
}

public partial class BaEnvironment : Environment
{
    // MAY have a portal environment
    public virtual PortalEnvironment PortalEnvironment { get; set; } // PortalEnvironment.FK_PortalEnvironment_BAEnvironment

    public BaEnvironment()
    {
        InitializePartial();
    }

    partial void InitializePartial();
}

public partial class PortalEnvironment : Environment
{
    // MUST have a BAMS environment
    public virtual BaEnvironment BaEnvironment { get; set; } // PortalEnvironment.FK_PortalEnvironment_BAEnvironment

    public PortalEnvironment()
    {
        InitializePartial();
    }

    partial void InitializePartial();
}

Configurations:

public partial class EnvironmentMap : EntityTypeConfiguration<Environment>
{
    public EnvironmentMap() : this("dbo")
    {
    }

    public EnvironmentMap(string schema)
    {
        ToTable("EnvironmentBase", schema);
        HasKey(x => x.Id);

        Property(x => x.Id).HasColumnName(@"EnvironmentId")
            .HasColumnType("int").IsRequired()
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(x => x.Ordinal).HasColumnName(@"Ordinal")
            .HasColumnType("tinyint").IsRequired();

        InitializePartial();
    }
    partial void InitializePartial();
}

public partial class BaEnvironmentMap : EntityTypeConfiguration<BaEnvironment>
{
    public BaEnvironmentMap() : this("dbo")
    {
    }

    public BaEnvironmentMap(string schema)
    {
        ToTable("BAEnvironment", schema);

        InitializePartial();
    }

    partial void InitializePartial();
}

public partial class PortalEnvironmentMap : EntityTypeConfiguration<PortalEnvironment>
{
    public PortalEnvironmentMap() : this("dbo")
    {
    }

    public PortalEnvironmentMap(string schema)
    {
        ToTable("PortalEnvironment", schema);
        //Property(x => x.BaEnvironmentId).HasColumnName(@"BAEnvironmentId")
            .HasColumnType("int").IsRequired()
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        // Foreign keys
        HasRequired(a => a.BaEnvironment).WithOptional(b => b.PortalEnvironment)
            .Map(c => c.MapKey(@"BAEnvironmentId"));

        InitializePartial();
    }
    partial void InitializePartial();
}

I don't seem to be having any problem with the TPT, just with the relationship between PortalEnvironment and BAEnvironment. The relationship between BAEnvironment and PortalEnvironment is 1:0..1, therefore a BAEnviroment MAY have an associated PortalEnvironment, but ALL PortalEnvironments MUST have an associated BAEnvironment.

My problem seems very simple, but I have been going back and forth and just not hitting the right combinations of fluent api to solve it.

It's worth noting that I am a DBA, not an app developer. Just being stuck with this because nobody else will do it.

Answer 1

EF6 does not support alternate keys, and so requires that the a foreign key column be the Entity Key in a 1..1 relationship. You can mark the FK as being unique, but in the EF model you will still have a 1..many relationship between BAEnvironment and Portal Environment.

Eg

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System.Linq;

namespace Ef6Test
{

    public abstract partial class Environment
    {
        public int Id { get; set; } // EnvironmentId (Primary key)

        public Environment()
        {
            InitializePartial();
        }

        partial void InitializePartial();
    }

    public partial class BaEnvironment : Environment
    {
        // MAY have a portal environment
        [InverseProperty("BaEnvironment")]
        public virtual ICollection<PortalEnvironment> PortalEnvironments { get; } = new HashSet<PortalEnvironment>();

        public BaEnvironment()
        {
            InitializePartial();
        }

        partial void InitializePartial();
    }

    public partial class PortalEnvironment : Environment
    {
        // MUST have a BAMS environment
        [ForeignKey("BaEnvironmentID")]
        public virtual BaEnvironment BaEnvironment { get; set; } // PortalEnvironment.FK_PortalEnvironment_BAEnvironment

        [Required(), Index(IsUnique = true)]
        public int BaEnvironmentID { get; set; }

        public PortalEnvironment()
        {
            InitializePartial();
        }

        partial void InitializePartial();
    }
    class Db : DbContext
    {
        public DbSet<Environment> Environment { get; set; }
        public DbSet<BaEnvironment> BaEnvironment { get; set; }
        public DbSet<PortalEnvironment> PortalEnvironment { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Environment>().ToTable("Environment");
            modelBuilder.Entity<BaEnvironment>().ToTable("BaEnvironment");
            modelBuilder.Entity<PortalEnvironment>().ToTable("PortalEnvironment");

            modelBuilder.Entity<PortalEnvironment>().HasRequired<BaEnvironment>(e => e.BaEnvironment).WithMany();
            base.OnModelCreating(modelBuilder);
        }
    }




    class Program
    {
        static void Main(string[] args)
        {

            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {

                db.Database.Log = m => Console.WriteLine(m);
                db.Database.Initialize(true);


            }

            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();
        }
    }
}