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

edmx entity-framework entity-framework-6 foreign-key-relationship unique-key


Utilizing Schema Initial, I already have a database structure.

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

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

and following the creation of the SSDL/CSDL1, 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" />
        <Dependent Role="ExternalMaps">
          <PropertyRef Name="ext_datahash" />

which results in a mistake 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 appears to be as follows for the PK, and the UX doesn't seem to be there, other than as a straightforward scalar property:

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

How do I add this Relation using an FK and a candidate key that is not a PK? I also want to add a Navigation Property to the CSDL (once this "works").

Additionally, the association line is absent from the design surface, which I believe is a result of this mistake. I'm using Visual Studio 2013 Ultimate Update 4 and Entity Framework version 6.1.1, which is the most recent version available on Nuget.

1 The results above are from using the Huagati DBML/EDMX tooling because the normal EDMX "Update from Database" didn't seem to pick up these FK relations (which perhaps connected to this problem). The designer would mistakenly attempt to use the main key, which is not supported by any FK relation, if I tried to "Add Association" before, and did not offer options for selecting other properties.

2 Trying to include a<UniqueConstraint> The Entity Framework's "Unique Constraints" element causes the following benign XML validation error:

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

12/21/2014 11:32:46 PM

Accepted Answer

12/31/2014 6:42:23 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow