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?

It's a one-to-one relationship. Each Review has one PictureInfo, and each PictureInfo belongs to one Review. I separated them in code, but I want them to go into the same table. — Andrew, Jun 27 '14 at 14:34

Andrew · Accepted Answer · 2014-06-28T10:58:49.633

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; }
}

score 8 · Answer 2 · answered Jun 27 '14 at 20:19

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.

Thank you, your answer helped me find the problem. The PictureInfoId was useless indeed, and I needed a [Required] attribute to really make the relationship 1-to-1. I posted the data-annotation-based solution, as I did not want to unnecessarily mix things with Fluent API. — Andrew, Jun 28 '14 at 10:53

score 1 · Answer 3 · answered Nov 27 '15 at 20:39

1

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

answered Nov 27 '15 at 20:39

Greg Gum

33,478
39
162
233

Mark Rucker · Answer 4 · 2016-05-07T04:37:41.437

(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; }
}

Using the working code example, I get `Invalid data encountered. A required relationship is missing. Examine StateEntries to determine the source of the constraint violation.` (EF 6.2.0) — Savage, Aug 06 '19 at 17:46
Solved it by adding the `[Required]` attribute on the line `public PictureInfo PictureInfo { get; set; }` — Savage, Aug 07 '19 at 10:44

score 0 · Answer 5 · answered Dec 09 '16 at 17:43

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; }
    } 
}

Your code looks different than OP provided. Please also add some detail about your solution. — mmushtaq, Dec 09 '16 at 18:13

score 0 · Answer 6 · answered Jul 25 '22 at 19:08

0

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.

answered Jul 25 '22 at 19:08

birwin

2,524
2
21
41

score -2 · Answer 7 · answered Sep 16 '15 at 14:09

-2

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 { ... }

answered Sep 16 '15 at 14:09

Pétrick Passos

7

1

The OP wanted to use the same table, not two different tables as your answer shows. – Greg Gum Nov 27 '15 at 20:40
+1 Thanks for your answer - helped save me from my idiocy. That will teach my to copy/paste the attribute. – Behr Feb 26 '16 at 22:52

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

7 Answers7

First Attempt

Second Attempt

Third Attempt

Working Code

Linked