Search code examples
entity-frameworknavigation-propertiesentity-model

Creating Entity Model with Cross Referencing Table - EntityFramework 4.1

Here is my scenario (tables):

Departments
====================
Id (int)
Name (varchar)

Employees
====================
Id (int)
Name (varchar)

EmployeesDepartmentXREFs
====================
EmployeeId (int)
DepartmentId (int)

When I import these 3 tables into EDMX model, I get 2 entities: Employee and Department. Entity Employee has navigational property Departments and entity Department has navigational property Employees.

And this is all fine. I actually like it this way.

But, problem starts when I want to add more fields in the XREF table, for example, I would like to have DateCreated, UserId fields to record when change is made and by who. If I add these two fields in XREF table, then my EDMX model will show 3 entities instead of two. It is not big issue, but since I am already done with coding, I am sort of looking for easy solution (to avoid coding).

Question: Is it possible to add new fields into XREF (cross reference) table and to keep only two entities? If yes, how would I update my datetime and userid information?

I assume that XREF entity has to be present in order to be able to update it (with date and user id), but I really like having navigational properties: Departments and Employees, versus having navigational properties EmployeesDepartmentXREFs.

Is there a middle ground for this? What is the best practice?

Thanks.

Solution

  • I am not entirely sure if this will work for model first, but you could use the NotMappedAttribute. I am especially not sure if this will fix the third entity from showing up, but it is definitely a path to look down.

    UPDATE

    After looking around, I found this MSDN forum question, which sounds like it is before code-first (which I do believe allows more than two columns in the mapping table via the fluent API) and fits your problem.

    You could try the fluent API, but again, I am not sure if this will fix anything for model-first. Here is a good stack overflow question that shows how to use the fluent api to map the columns together.

    Last, Here is a good article on your problem.

    Hopefully, this helps/clarifies things for you.