Entity Framework 4.1 - Relationships between non-key columns

Question

I have 2 entities that are related, but the legacy sql schema essentially has 2 key columns for the same table (not a 2-column key: see below). I need to create a relationship back to the 'faux key' column. Is there a way to do this declaratively in Entity Framework 4.1?

Public Class Client
    Inherits ModelBase

    <Key(), Required()>
    Public Property ClientID As Decimal

    <Required(), StringLength(50)>
    Public Property ClientCode As String

    ........


Public Class ClientLocation
    Inherits ModelBase

    ........

    <Required(), StringLength(50)>
    Public Property ClientCode As String

    ........

    <ForeignKey("ClientCode")>
    Public Overridable Property Client As Clients.Client

And the error I am getting is:

*One or more validation errors were detected during model generation: System.Data.Edm.EdmAssociationConstraint: : The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'ClientCode' on entity 'ClientLocation' does not match the type of property 'ClientID' on entity 'Client' in the referential constraint 'ClientLocation_Client'.*

Because it thinks I'm trying to map ClientLocation.ClientCode > Client.ClientID, when I am really trying to map ClientLocation.ClientCode > Client.ClientCode...

Any thoughts?

Thanks!

Cd

"...the legacy sql schema essentially has 2 key columns for the same table...": Do you mean that Client.ClientCode is a column with a unique index in the database? Or what are then "2 key columns...but not composite key"? And you want to map ClientLocation.Client somehow to this unique column Client.ClientCode? – Ebby

The table has 2 effective keys, but the second one is not identified as a key, and has no index. For instance, ClientID could be 4, and ClientCode could be "FOGCREEK". The two aren't related or dependant, they just happen to both be unique. And yes, I need to map back to the original table using the Client.ClientCode, even though it's not marked as a key in my entity. – Cd

Ah, I see, then ClientCode is an ordinary column. Uniqueness is just accidentally ensured by business logic. I am afraid that Ladilav's answer is the final word. – Ebby

Answer 1

Entity framework demands that relation is built between whole primary key in the principal table and corresponding columns (foreign key) it the dependent table.

Anselme

So by your understanding, there is no way to "Map" the relationship the same way we can "Map" the table and column names? – Cd

You must map the relation same way as you would do in the database. If your database doesn't have tables correctly set up, EF is not able to fix it. – Anselme

Even though this answer is probably right, I'm not willing to accept it just yet without more consensus. Can anyone else verify that there is no method akin to the Data Annotations <Table("oldTableName")> and <Column("oldColumnName")> to help override the relationship fields? – Cd

Answer 2

Even if it's not possible you could still join the two tables using a LINQ query like so (C#) (see also this question).

var result = from a in ctx.Client
             join b in ctx.ClientLocation
             on a.ClientCode equals b.ClientCode
             select new { Client = a, Location = b };

You're only missing the navigational property Client.ClientLocation and ClientLocation.Client. It's a little more cumbersome to do it this way, but it's possible nevertheless.

If you're willing to extend the SQL scheme you could add another table like ClientLocationClient that acts as a M:N table with foreign keys to both Client and ClientLocation and the two as composite key. You could then navigate like so (C#)

var client = myClientLocation.ClientLocationClients.First().Client; // there's only one

This line will however fail as soon as you have a ClientLocation that has no corresponding Client AND of course you would need to define a trigger in Client to sync your extension table and configure the delete to be cascading AND the ClientLocation need to be inserted before the Client, otherwise the trigger will fail... Overall I just want to speak out a warning that this can be quite a dangerous route.

Prolongate

Answer 3

(This is only an appendix to Ladislav's answer, accept and bounty must not go to my answer.)

I would be surprised if such a feature will ever be implemented in EF. Why? Because you even cannot create such a relationship in a relational database. A foreign key relationship in a relational DB (at least SQL Server and probably most or all other) demands that the principal side is a column which is either primary key or has a unique key constraint. Which makes sense because a foreign key is supposed to refer to one unique row in the principal table.

Now, EF doesn't even support yet relationships to unique key columns, only to primary key columns. This is something which might possibly be supported in future. But supporting foreign key relationships to non-unique and non-primary-key columns doesn't even seem to make sense to me.

What do you expect to happen if the value in the target column of the principal table is not unique? Do you want an exception when you - for example - try to eager load ClientLocation.Client - "Cannot load navigation property 'Client' because the foreign key does not refer to a unique target" or a warning "Did load a Client but there is another one, cannot ensure that I loaded the one you wanted" or something like that?

If you want to do yourself a favour I would give up the idea, remove the navigation property and think in the direction to work a lot with Join in your LINQ queries.

Ebby

Answer 4

Probably Association attribute is your answer, using association you can specify which keys to be used in each side of the relation.

Some code like this:

[ForeignKey()]
[Association("SomeNameForAssociation","TheKeyInThisEntity","TheKeyOnTheAssociationTargetEntity")]
public virtual Examination Examination { get; set; }

And if I understand your question, you code should change to:

Public Class Client   
    Inherits ModelBase   

    <Key(), Required()>   
    Public Property ClientID As Decimal   

    <Required(), StringLength(50)>   
    Public Property ClientCode As String   

    ........   


Public Class ClientLocation   
    Inherits ModelBase   

    ........   

    <Required(), StringLength(50)>   
    Public Property ClientCode As String   

    ........   

    <ForeignKey("ClientCode")> 
    <Association("ClientClientCodes","ClientCode","ClientCode")>
    Public Overridable Property Client As Clients.Client  

First "ClientCode" : name of key column in ClientCode. Second "ClientCode" : name of key column in ClientCode which you want to use.

Note: I have not used this attribute yet, but its documentation and its name and its argument names sugesst it should satisfy your needs.

Story

I doubt that the [Association] attribute has any effect in Entity Framework. It's only a mapping attribute for LINQ to SQL. – Ebby