Creating entity relationship with renamed fields and non-primary key in primary table
c#, data-annotations, entity-framework, foreign-keys
Solution
EF doesn't yet support relationships where the principal's key is not the primary key but some other column with a unique key constraint. It is on the feature request list but neither implemented nor on the road map for the next release (EF 6). If it gets implemented at all (in EF 7 maybe) expect to wait a year or more until it's ready for production.
In your particular model EF doesn't recognize any relationship between `Form` and `Patient` at all because `Patient.PatientID` is marked as `[Key]`, not `Patient.PatientGUID`, and EF treats `Form.PatientGUID` as an ordinary scalar property, not as an FK to `Patient`.
In theory you could fake `Patient.PatientGUID` as the `[Key]` property in the model although it is not the primary key in the database if you don't create the model from the database or the database from a code-first model, that is, if you map between model and (existing) database manually. But I am not sure if this wouldn't cause subtle problems anywhere else.
The alternative is to write manual `join` statements in LINQ if you want to fetch `Patients` and related `Forms`. You can then join two entities using arbitrary properties, not only key properties. This is, in my opinion, the cleaner and less "tricky" approach. However, the downside is that you won't have navigation properties - references or collections - between `Patient` and `Form` and you can't use features like eager loading (`Include`), lazy loading or comfortable "dotted path syntax" (like `Form.Patient.SomePatientProperty`, etc.) in your LINQ queries.
Problem
The following are two partial tables in which I am trying to define a foreign key relationship. ``` public class Form { [Key, Column("FormID")] public System.Guid FormGUID { get; set; } [Column("PatGUID")] public Nullable<System.Guid> PatientGUID { get; set; } } public class Patient { [Column("PatGUID")] public System.Guid PatientGUID { get; set; } [Key, Column("PatID")] public int PatientID { get; set; } ``` } I've eliminated all but the relevant information, fields, navigations, etc. for this example; hopefully not too much. We have a table Form, with a FK of `PatGUID` to a Patient table with field `PatGUID`. The Patient table has a `PatID` int KEY field. We have requirements to rename our fields for our code first entity models; the relevant fields in this example needing changed is `PatGUID` being changed to `PatientGUID`. The difficulty I am having is trying to define this foreign key using either annotations or fluent. So the end result I need is: Primary Key Table: Patient, Field: `PatGUID` (renamed PatientGUID) Foreign Key Table: Form, Field: `PatGUID` (renamed PatientGUID) This doesn’t seem like it should pose a large problem but with the combination of `Patient.PatGUID` not being the primary key and the `PatGUID` fields being renamed to `PatientGUID` has not enabled the WCF Data Service to properly create a reference with a proper reference thus a proper select/join of: ``` SELECT … FROM [dbo].[Form] AS [Extent1] INNER JOIN [dbo].[Patient] AS [Extent2] ON [Extent1].[PatGUID] = [Extent2].[PatGUID] ```