Entity Framework Table Splitting: not in the same type hierarchy / do not have a valid one to one foreign key relationship

Question

I'm using Entity Framework 6 with a Code-First approach, and I want two entities to be put in the same table. What am I doing wrong?

[Table("Review")]
public class Review
{
    public int Id { get; set; }
    public PictureInfo PictureInfo { get; set; }
    public int PictureInfoId { get; set; }
}

[Table("Review")]
public class PictureInfo
{
    [Key, ForeignKey("Review")]
    public int ReviewId { get; set; }
    public Review Review { get; set; }
}

The error I get: The entity types 'PictureInfo' and 'Review' cannot share table 'Review' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them.

What am I doing wrong?

Answer 1

I wanted to add a note here for people who get this error, but did not mean to have both entities point to the same table...

Make sure you did not accidentally place an incorrect Table name in your "Table" attribute.

Answer 2

using System.Linq;
using System.Data.Entity;
namespace Sample
{

---

    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EmployeeDBContext())
            {
                var result = context.Set<Employee>().Include(x => x.Department).ToArray();
            }
        }
    }

---

    public class EmployeeDBContext : DbContext
    {
        public EmployeeDBContext() : base("EmployeeDB") { }

        public DbSet<Employee> Employee { get; set; }
        public DbSet<Department> Department { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Department>().ToTable("Departments").HasKey(x => x.DepartmentId);
            modelBuilder.Entity<Employee>().ToTable("Employees").HasKey(x => x.Id);
            //ForeignKey mapping 
            modelBuilder.Entity<Employee>().HasRequired(x => x.Department).WithMany().HasForeignKey(x => x.DepartmentId);
            base.OnModelCreating(modelBuilder);
        }
    }

---

    //Domain Entity
    public class Employee
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public Department Department { get; set; }
        public int DepartmentId { get; set; }
    }

    //Domain Entity
    public class Department
    {
        public int DepartmentId { get; set; }
        public string DepartmentName { get; set; }
    } 
}

Answer 3

(These tests and errors were written against EF 6.1.3)

First Attempt

[Table("Review")]
public class Review
{
    [Key]
    public int Id { get; set; }
    public PictureInfo PictureInfo { get; set; }
}

[Table("Review")]
public class PictureInfo
{
    [Key]
    public int Id { get; set; }
    public Review Review { get; set; }
}

With the above entities I received this error:

Unable to determine the principal end of an association between the types. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

Second Attempt

[Table("Review")]
public class Review
{
    [Key]
    public int Id { get; set; }

    [Required]
    public PictureInfo PictureInfo { get; set; }
}

[Table("Review")]
public class PictureInfo
{
    [Key]
    public int Id { get; set; }

    [Required]
    public Review Review { get; set; }
}

The entity types 'Review' and 'PictureInfo' cannot share table 'Review' because they are not in the same type hierarchy or do not have a valid one to one foreign key relationship with matching primary keys between them.

Third Attempt

[Table("Review")]
public class Review
{
    [Key]
    public int Id { get; set; }

    [Required, ForeignKey("Id")]
    public PictureInfo PictureInfo { get; set; }
}

[Table("Review")]
public class PictureInfo
{
    [Key]
    public int Id { get; set; }

    [Required, ForeignKey("Id")]
    public Review Review { get; set; }
}

Unable to determine the principal end of an association between the types. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

Working Code

[Table("Review")]
public class Review
{
    [Key, ForeignKey("PictureInfo")]
    public int Id { get; set; }

    public PictureInfo PictureInfo { get; set; }
}

[Table("Review")]
public class PictureInfo
{
    [Key, ForeignKey("Review")]
    public int Id { get; set; }

    public Review Review { get; set; }
}

Answer 4

The other way to resolve this issue is to create a view with just the desired fields. Then map the entity to the view.

Answer 5

The error is caused because the definition of the table is duplicated in the directive of Table "PictureInfo". You need only edit that's directive.

[Table("Review")] public class Review { ... }

[Table("Review")] public class PictureInfo { ... }

for

[Table("Review")] public class Review { ... }

[Table("PictureInfo")] public class PictureInfo { ... }

Answer 6

Seems like the problem was that the relationship was interpreted as one-to-0..1 instead of one-to-one.

The foreign key int PictureInfoId on the Review end was unneeded/ignored, so its non-nullability did not make the Review end of the relationship required. Removing this unneeded key and adding the [Required] attribute to the PictureInfo navigational property solved it.

Here's the corrected Review class.

[Table("Review")]
public class Review
{
    public int Id { get; set; }
    [Required]
    public PictureInfo PictureInfo { get; set; }
}

Answer 7

I've managed to achieve what you wanted with fluent api. Fluent api offers much richer options for configuration than data-annotations. I've changed a bit your entity classes:

public class Review
{
    public int Id { get; set; }
    public PictureInfo PictureInfo { get; set; }
}

PictureInfoId property is not necessary as foreign key relationship will be done on primary keys of both entities.

public class PictureInfo
{
    public int Id { get; set; }
    public Review Review { get; set; }
}

Because Review and PictureInfo will be mapped to the same table they need to share the same primary key column so for PictureInfo and Review this column should have the same name. If you would like to keep in PictureInfo primary key property named ReviewId you can do this but you would need then to map its name to "Id". Finaly the DbContext:

public class MyDbContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Review>().HasKey( e => e.Id );
        modelBuilder.Entity<Review>()
            .HasRequired(e => e.PictureInfo)
            .WithRequiredDependent(e => e.Review);
        modelBuilder.Entity<Review>().Map(m => m.ToTable("Review"));
        modelBuilder.Entity<PictureInfo>().Map(m => m.ToTable("Review"));
        modelBuilder.Entity<PictureInfo>().HasKey(e => e.Id);

        base.OnModelCreating(modelBuilder);
    }

    public DbSet<Review> Reviews { get; set; }
    public DbSet<PictureInfo> PictureInfos { get; set; }
}

OnModelCreating holds fluent api mapping definition. All you have to do is to define primary keys on both entities with the same name, bind those 2 entities with 1-1 relation and then map them to the same table.