Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating entity relationship with renamed fields and non-primary key in primary table

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]
like image 734
user2144404 Avatar asked Mar 12 '13 02:03

user2144404


People also ask

Can you have an entity without a primary key?

The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose values uniquely identify instances of the entity.

Can a primary key be dependent on another primary key?

you can relate any keys you want, whether they're primary or not. just because they're primary doesn't mean they can't also be a foreign key. all it means is that you're enforcing a 1:1 relationship, since you only ever have one record with that key in either table.

Does a relationship table need a primary key?

Every table can have (but does not have to have) a primary key. The column or columns defined as the primary key ensure uniqueness in the table; no two rows can have the same key.

Can a table have both primary key and foreign key?

A table can have only one Primary Key. A table can have any number of Foreign Keys. The primary key is unique and Not Null. A foreign key can contain duplicate values also.

Can a primary key have a relationship with another column?

Columns that define primary keys in one table in a relational model can have a relationship with columns in one or more other tables. The easiest (and recommended) method of defining these relationships using the Data Diagram Editor is provided in the following instructions. Every table can have (but does not have to have) a primary key .

Can incoming data be taken as a data entity primary key?

2) Incoming data must not violate data integrity enforced by the updated data sources that a data entity is built on. In our case, we’ll start with an assumption that the Namefield (or combination of other fields) can be taken as a data entity primary key, even though it is part of the non-unique table index.

Why can't I create a new data entity for this table?

The table contains three fields and does not have a unique index for now. Generating a new data entity for it ends up with the error shown below. The error comes from the system requirement that every data entity must have a primary key defined.

What is the name of the attribute primary key for entity?

Specifies the name of the attribute primary key for the entity. The primary key for a CRM Entity is the Globally Unique Identifier ( GUID ) that CRM uses to reference everything within the CRM system. The Primary Key for account is accountid. The Primary Field for account is name.


1 Answers

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.

like image 168
Slauma Avatar answered Sep 21 '22 12:09

Slauma