Unable to determine the principal end of an association between the types - Entity Framework error, class relationship between the same class

Question

Have the class below, and try to look for records in the database returns error. Using C #, MVC 4, Entity Framework 4 and SQL Server 2012 database.

Error

Unable to determine the principal end of an association between the types 'FlexApp.Models.Model.Usuario' and 'FlexApp.Models.Model.Usuario'. 

The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

Class

public class Usuario
{
    [Key]
    public int UsuarioID { get; set; }
    public string Nome { get; set; }
    public int UsuCad { get; set; }        
    public int UsuAlt { get; set; }

    [ForeignKey("UsuCad")]
    public virtual Usuario UsuarioCad { get; set; }
    [ForeignKey("UsuAlt")]
    public virtual Usuario UsuarioAlt { get; set; }
}

DataBase FK

alter table USUARIO add constraint USUARIO_fk01 foreign KEY(UsuCad) REFERENCES USUARIO(UsuarioID);
alter table USUARIO add constraint USUARIO_fk02 foreign KEY(UsuAlt) REFERENCES USUARIO(UsuarioID);

Answer 1

reply

After searching a lot, I found a tip about using InverseProperty Then the code looks like this. This property ForeignKey It is to mount the link and property InverseProperty for to inform that the field depends on this other camp, reversing the foreign key.

Thanks for the help

public class Usuario
{
    [Key]
    public int UsuarioID { get; set; }
    public string Nome { get; set; }
    public int UsuCad { get; set; }        
    public int UsuAlt { get; set; }

    [ForeignKey("UsuCad")]
    [InverseProperty("UsuarioID")]
    public virtual Usuario UsuarioCad { get; set; }
    [ForeignKey("UsuAlt")]
    [InverseProperty("UsuarioID")]
    public virtual Usuario UsuarioAlt { get; set; }
}

Answer 2

I am not 100% sure what kind of db schema you are trying to achieve here. My assumption is that you want to have foreign key to the same table. In your comment, you said you want a one to one relation ship. Technically this is not possible. Because when you try to insert the first record, You need to provide a value for your foreign key column. The value should be an Id of an existing record. Wait... We don't have any records yet !

Since 1-1 is not really practical here, You should be doing a one to zero/one relationship. That means your foreign key column should be nullable so that you can insert your first record with NULL value in your foreign key column.

I am having a little hard time understanding your model & property names. So i am going to use a general class/table which everyone can understand, but with your specific requirement ( Self reference foreign key)

I am not using data annotations, I am using Fluent API

The silly business requirement/assumption is that :

• One Person may or may not have a Parent
• One Person may or may not have a Kid

So our entity class will look like this

public class Person
{
    public int Id { set; get; }
    public string Name { set; get; }
    public virtual Person Parent { set; get; }
    public virtual Person Kid { set; get; }    
}

Now to use fluent API to control the relationships, We need to go to our DBContext class and override the OnModelCreating method

public class MyDbContext : DbContext
{
    public MyDbContext() : base("MyConnectionStringName") { }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {    
        modelBuilder.Entity<Person>().HasKey(d => d.Id)
            .HasOptional(m => m.Kid).WithOptionalPrincipal(d=>d.Parent);

        base.OnModelCreating(modelBuilder);
    }   

    public DbSet<Person> Persons { set; get; }
}

This will create the table with 3 Columns, ID, Name and Parent_Id (NULLABLE, Foreign Key relationship to ID of same table)

I can insert data like this

var db = new MyDbContext();

var myMom = new Person {Name = "Indira"};
var me = new Person {Name = "Shyju", Parent = myMom};
var myDaughter = new Person { Name = "Gauri", Parent = me};

db.Persons.Add(myMom);
db.Persons.Add(me);
db.Persons.Add(myDaughter);

db.SaveChanges();

And you will have your data with ParentId column having a foreign key to the ID column of the same table.