c# - ASP.NET MVC 3.0 2 Foreign Keys from the same table exception -

i have 2 tables , models corresponding each table: employee , employeeeducation in employeeeducation have 2 foreign keys table employee: id consultant , id actual employee owns educations. each education can have different consultant.

[required(errormessage = "contact admin")] [display(name = "consultant")] public int? consultantid { get; set; } *emphasized text* [required(errormessage = "contact admin")] public int? employeeid { get; set; } 

for each id ı have these objects reach objects

[foreignkey("employeeid")] public virtual employee employee { get; set; }  [foreignkey("consultantid")] public virtual employee consultant { get; set; } 

when run code , try enter education employee consultant gives me following exception inner exception.

entitycommandexecutionexception  {"an error occurred while executing command definition. see inner exception details."}  inner exception: sqlceexception {"the column name not valid. [ node name (if any) = extent1,column name = employee_id ]"} 

but when remove consultant object not give exception. how can solve problem have access both consultant , employee itself?

the exception happens in detailseducation.cshtml:

@{ if (model.educationlist == null || !model.educationlist.any())     {  

here how educationlist populated:

public actionresult detailseducation(int id)  {    employee employee = _work.employeerepository.getset()     .include(a => a.educationlist)     .include(a => a.educationlist.select(c => c.university))     .include(a => a.educationlist.select(c => c.department))     .firstordefault(a => a.id == id);    return partialview("_detailseducation", employee); } 

column name = employee_id

when entity framework creates sql query foreign key has (unexpected) underscore in column name indicator ef infers relationship convention , 1 relationship have defined annotations or fluent api.

this foreign key cannot origin employeeeducation.employee , employeeeducation.consultant navigation properties because have defined foreign key name data annotations [foreignkey("employeeid")] , [foreignkey("consultantid")].

now, how ef detect relationships? inspects navigation properties in model classes. know employeeeducation.employee , employeeeducation.consultant cannot problem, there must third navigation property somewhere. relationship belongs navigation property must have association end in employeeeducation because ef apparently infers need of additional foreign key employee_id in employeeeducation table.

because of name - employee _id - navigation property in class employee. looking @ include(a => a.educationlist) seem have collection property in employee:

public somecollectiontype<employeeeducation> educationlist { get; set; } 

this collection causing third foreign key. if had 1 navigation property in employeeeducation, example employeeeducation.employee, problem not occur because ef infer in case employee.educationlist , employeeeducation.employee pair of navigation properties of single relationship.

if have two navigation properties both refering employee ef cannot decide of 2 collection in employee belongs to. instead of choosing 1 whatever rule chooses none of them , assumes collection belongs third relationship.

to solve problem must ef give hint of 2 references in employeeeducation want relate collection to, example using [inverseproperty] attribute on 1 of properties (but not both):

[foreignkey("employeeid"), inverseproperty("educationlist")] public virtual employee employee { get; set; }  [foreignkey("consultantid")] public virtual employee consultant { get; set; } 

attention: educationlist contain employeeeducations given employee employee for, not consultant. need second collection property in employee [inverseproperty] annotation on consultant time. cannot associate one navigation collection in 1 entity two navigation references in other entity. choice either 2 collections or no collection @ all. (in latter case problem disappear way, wouldn't have navigation property anymore "include".)