entity framework 5 .net 4 - database first self referencing entity

Question

Trying to accomplish something similiar to http://www.codeproject.com/Articles/206410/How-to-Configure-a-Self-Referencing-Entity-in-Code but in my case I'm not using code first rather db first. I'm getting this error {"Violation of PRIMARY KEY constraint 'pk_employee'. Cannot insert duplicate key in object 'dbo.Employee'.\r\nThe statement has been terminated."}.

        EmployeeEntity employeeEntity = null;
        EmployeeEntity employeeDelegate = null;

            // already EXISTS in table
            employeeDelegate = new EmployeeEntity
            {
                EMPL_UNO = 1,
                FULLNAME = "manager, name"
            };


        employeeEntity = new EmployeeEntity
        {
            EMPL_UNO = 2,
            FULLNAME = "employee, name",
            DELEGATE_EMPL_UNO = 1,
            Delegate = employeeDelegate
        };



MyContext.EmployeeEntities.Add(Employee);
    // throws error
MyContext.SaveChanges();

// table

    CREATE TABLE [dbo].[Employee](
    [EMPL_UNO] [int] NOT NULL,
    [FULLNAME] [varchar](255) NULL,
    [DELEGATE_EMPL_UNO] [int] NULL,
 CONSTRAINT [pk_employee] PRIMARY KEY CLUSTERED 
(
    [EMPL_UNO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Delegate] FOREIGN KEY([DELEGATE_EMPL_UNO])
REFERENCES [dbo].[Employee] ([EMPL_UNO])
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Delegate]
GO

// Entity

  public partial class EmployeeEntity
{
    public EmployeeEntity()
    {
        this.SubOrdinates = new HashSet<EmployeeEntity>();
    }

    public int EMPL_UNO { get; set; }
    public string FULLNAME { get; set; }
    public Nullable<int> DELEGATE_EMPL_UNO { get; set; }

    public virtual ICollection<EmployeeEntity> SubOrdinates { get; set; }
    public virtual EmployeeEntity Delegate { get; set; }

}

Answer 1

Your code fails because Add method will insert all unknown entities from the object graph and in your case both new and existing employees are unknown to EF context because you didn't inform the context about the existence of the first entity (setting the id is not enough). You can for example use:

var employeeDelegate  = new EmployeeEntity {
    EMPL_UNO = 1,
    FULLNAME = "manager, name"
};

MyContext.EmployeeEntities.Attach(employeeDelegate);

var employeeEntity = new EmployeeEntity {
    EMPL_UNO = 2,
    FULLNAME = "employee, name",
    DELEGATE_EMPL_UNO = 1,
    Delegate = employeeDelegate
};


MyContext.EmployeeEntities.Add(Employee);
MyContext.SaveChanges();

But in your particular case this should work as well:

var employeeEntity = new EmployeeEntity {
    EMPL_UNO = 2,
    FULLNAME = "employee, name",
    DELEGATE_EMPL_UNO = 1 // Just set the FK, you don't need a reference if you don't want to modify the delegate as well
};

MyContext.EmployeeEntities.Add(Employee);
MyContext.SaveChanges();