Entity Framework Code first mapping without foreign key

Question

I have two tables:

Requirement

• ID (int) PK
• ClientID (int)
• JobNumber (int)

Comment

• ID (int) PK
• Job_ID (int)
• Comment (varchar)

The tables don't have foreign keys and there's no possibility of adding any. I'm trying to map them in EF. I have classes for each and I'm trying to define the relationship in fluent code to map the Comment.Job_ID to the Requirement.JobNumber. A requirement can have many comments. Requirement has a list of Comments and Comment has a Requirement property.

I have this mapping setup:

modelBuilder.Entity<Comment>().HasRequired(c => c.Requirement)
                    .WithMany(s => s.Comments)
                    .HasForeignKey(f => f.Job_ID);

I'm stuck trying to get Comment.Job_ID to map to Requirement.JobNumber.

Any help appreciated.

Answer 1

It's not possible. With Entity Framework the entity that the Comment.Requirement navigation property is refering to is generally identified by the (primary) key property in Requirement, i.e. by ID. There is no mapping option to define that the target property is anything else than the key property - like JobNumber or another non-key property.

I could only imagine that you could "fake" the primary key property in the model to be JobNumber instead of ID (given that JobNumber is unique in the Requirement table):

modelBuilder.Entity<Requirement>().HasKey(r => r.JobNumber);

I don't know if that could have other unwished side effects. (For sure it doesn't work if JobNumber is not unique because EF wouldn't allow to have more than one entity with the same key attached to a context and updates/deletes and so on wouldn't find the correct record in the database.) It feels wrong and hacky to me. I honestly wouldn't even try that, live with the fact that you don't have a real foreign key relationship in the database, forget the navigation properties Requirement.Comments and Comment.Requirement and use manual joins in LINQ to relate the table data/entities as I need them in a given situation.