Entity Framework multiple mapping to same table DB first

Question

I have an issue with an Entity Framework from DB model.

My issue is down to the fact that one of my models has a multiple references to one table.

public partial class Customer
{
    public int Id { get; set; }

    public Nullable<int> PrimaryEngId { get; set; }
    public Nullable<int> AssignedDevloperId { get; set; }

    public virtual Engineer Engineer { get; set; }
    public virtual Engineer Engineer1 { get; set; }
}

In my model the columns are mapped respectively, however when a colleague builds the model from the same database the two are reversed.

I believe the issue is that the first mapping to in was the primaryEngId and the Db constraint is called FK_Customer_Engineer.

And the assigned developer id was added subsequently and the DB constraint is called FK_Customer_Devloper

So alphabetically Developer come before Engineer and Entity Framework now maps them the other way round.

My code references the Engineer in quite a lot of places which now won't work

Is there any way out of this?

Many thanks

Ian

Answer 1

You have to add missing ForeignKey attributes on foreign keys for those two navigation properties:

[ForeignKey("Primary")]
public int? PrimaryEngId { get; set; }

[ForeignKey("Assigned")]    
public int? AssignedDevloperId { get; set; }

public virtual Engineer Primary { get; set; }

public virtual Engineer Assigned { get; set; }

NOTE: Also don't use generic names for navigation properties with EF. In the nutshell one of the best things EF gives you is that you can say:

@myCustomer.Assigned.Name

etc in the view, and you are totally screwing it up with names like Engineer and Engineer1.

NOTE2: Keep Nullable<int> to code generation. int? is a lot more readable.

NOTE3: Use VS refactoring to rename properties Engineer and Engineer1 to what they should be ( PrimaryEngineer and AssignedEningeer etc). After that add ForeignKey attributes to your model. That should be enough. However, any future changes that you are doing has to be done in the Code and not in db.

IF on the other hand you are constantly regenerating entities and context code from database, make sure that all your foreign keys has meaningful names, as EF will use them to generate name.(ie it is not named Engineer1 out of blue) Rename those foreign keys to reflect what logical relationship is. Ie you most likely have the following foreign keys in db:

 FK_Customer_Engineer

 FK_Customer_Engineer1

You need to rename them to

 FK_Customer_PrimaryEngineer

 FK_Customer_AssignedEngineer

Update: you can have different column name and property name like so:

[Column("PrimaryEngId")]
[ForeignKey("Primary")]
public int? PrimaryID { get; set; }