How to add an EF6 Association to a Candidate Key / Unique Key which is not the Primary Key?

Question

Using Schema First, I have a database structure, as so

ExternalDataItems
---
edataitem_id   PK -- surrogate auto-increment - NOT for FK relation here
datahash       UX -- Candidate Key / Unique Index (binary(20))

ExternalMaps
---
emap_id        PK
ext_datahash   FK on ExternalDataItems.datahash - NOT referencing the surrogate PK

and after generating the SSDL/CSDL¹ has this

    <Association Name="FK_ExtMaps_ExtDataItems">
      <End Multiplicity="1" Role="ExternalDataItems" Type="Store.ExternalDataItems" />
      <End Multiplicity="*" Role="ExternalMaps" Type="Store.ExternalMaps" />
      <ReferentialConstraint> <!-- error on this element -->
        <Principal Role="ExternalDataItems">
          <PropertyRef Name="datahash" />
        </Principal>
        <Dependent Role="ExternalMaps">
          <PropertyRef Name="ext_datahash" />
        </Dependent>
      </ReferentialConstraint>
    </Association>

which generates an error on the <ReferentialConstraint> element

Running transformation: Properties referred by the Principal Role ExternalDataItems must be exactly identical to the key of the EntityType ExternalDataItem referred to by the Principal Role in the relationship constraint for Relationship FK_ExtMaps_ExtDataItems. Make sure all the key properties are specified in the Principal Role.

The "Principal Role" (?) for ExternalDataItems SSDL looks like the following, for the PK, and the UX does not appear to be present², except as a simple scalar property:

  <EntityType Name="ExternalDataItems">
      <Key>
        <PropertyRef Name="edataitem_id" />
      </Key>
      ..
      <Property Name="datahash" Type="binary" MaxLength="20" Nullable="false" />
  </EntityType>

How can I add this Relation - using a FK to a non-PK Candidate Key? (After this "works" I'll also want to also have a Navigation Property to the CSDL.)

Furthermore, the association line does not appear in the design surface - which I suspect is just fallout from this error. I am using Entity Framework version 6.1.1 (latest published on nuget) and Visual Studio 2013 Ultimate Update 4.

---

¹The standard EDMX "Update from Database" didn't appear to pick up these FK relations (which may be related to this bug) and the results above are after using the Huagati DBML/EDMX tooling. If I tried to "Add Association" prior the designer would incorrectly try and use the primary key - which is not supported by any FK relation - and did not provide options for choosing alternative properties.

---

²Attempting to add a <UniqueConstraint> element as described in "Unique Constraints in the Entity Framework" results in the friendly XML validation error:

The element 'ElementType' .. has an invalid child element 'UniqueConstraint'.

Seow

Answer 1

Looks like "missing important feature"

.. The Entity Framework currently only supports basing referential constraints on primary keys and does not have a notion of a unique constraint.

You can remove foreign key in DB scheme and use LINQ to join tables:

from item in ExternalDataItems
join map in ExternalMaps on item.datahash = map.ext_datahash
select new { item.edataitem_id, map.emap_id };

Also you can create the VIEW with these joined tables and use the one.

Goldsberry

This is true as of EF6.1 and plagues all parts of EF wishing to deal with a non "Code First" model of any serious complexity. I'm currently using the manual join approach and it "works". – Seow