Create association on non-primary key fields with Entity Framework 4.1 Fluent API

Question

We are using EF 4.1 and the fluent API to get data from a legacy database (that we are not permitted to change). We are having a problem creating a relationship between two tables where the related columns are not primary and foreign keys.

With the classes below, how would we configure the one-to-many relationship between Report and RunStat such that Report.RunStats would return all of the RunStat entities where the ReportCode fields are equal?

public class Report
{
    [Key]
    public int ReportKey { get; set; }
    public string Name { get; set; }
    public int ReportCode { get; set; } // Can we associate on this field?
    public virtual ICollection<RunStat> RunStats { get; set; }
}

public class RunStat
{
    [Key]
    public int RunStatKey { get; set; }
    public int ReportCode { get; set; }
    public DateTime RunDate { get; set; }
}

Basically, I want to use the Fluent API to configure EF such that it considers Report.ReportCode to be the foreign key and RunStat.ReportCode to be the primary key.

Answer 1

It is not possible. Relations in EF follows exactly same rules as in the database. It means that principal table must have unique identifier which is referenced by dependent table. In case of database the identifier can be either primary key or unique column(s) of principal table. Otherwise it is not valid relation.

Entity framework doesn't support unique keys. If you want to have one-to-many relation between Report and RunStat your dependent table (RunStat) must contains column with value of Report.ReportKey. There is no other way to make it automatic - otherwise you must simply make it custom property and fill it from entity framework manually when you need it.

Answer 2

This capability is now possible in EF Core 1.0 (EF7) as indicated in the reference provided by @Brian on Jul 16, 2014 to a feature request posted to Microsoft's UserVoice forum.

So that this valuable information is not lost should this information disappear at the referenced page, here is the text of the feature request:

Unique Constraint (i.e. Candidate Key) Support
_{(posted by Kati Iceva on Sep 10, 2010)}

SQL Server and other databases support Unique Constraints on tables. Foreign key constraints are generally based on unique constraints on the principal side, with the Primary Key being only a special case of a unique constraint. The Entity Framework currently only supports basing referential constraints on primary keys and does not have a notion of a unique constraint. The idea is to have:

• Support for specifying a unique constraint on an Entity
• Support for specifying a foreign key associations that on the principal end specify columns(s) that comprise a unique constraint but are not the primary key.

And Microsoft's announcement of the implementation of this capability in EF Core 1.0:

Completed
_{(posted by Diego Vega (Program Manager, Microsoft Entity Framework) on Aug 9, 2016)}

Closing as support for this feature was added in EF Core 1.0 and we don’t have plans to add it in the EF6 codebase. Also, please create new ideas for specific improvements to the feature.