Entity Framework: 0..1-to-many foreign key constraint not recognized?

Question

I am using EF4 to map a DB schema to an object model; I initially generated the EDMX from the database but have been editing the XML directly (trying to leave the SSDL the same while changing the CSDL/MSL to approximate the object model I want). The database contains, among many other things, a couple tables with a (0..)1-to-many constraint via foreign key like so:

go
create table Options (
    KitNodeID int primary key foreign key references KitNodes (KitNodeID),
    [SKUID] int null foreign key (SKUID) references SKUs (SKUID)
)
go
create table Upgrades (
    UpgradeID int identity (1, 1) primary key not null,
    [Name] nvarchar(50) not null,
    DefaultOptionID int null references Options (KitNodeID)
)

The relevant sections in the EDMX look like this:

[...]

<!-- SSDL content -->
        <edmx:StorageModels>
        <Schema Namespace="DModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
            <EntityContainer Name="DModelStoreContainer">

      <AssociationSet Name="FK__Upgrades__Defaul__70DDC3D8" Association="DModel.Store.FK__Upgrades__Defaul__70DDC3D8">
        <End Role="Options" EntitySet="Options" />
        <End Role="Upgrades" EntitySet="Upgrades" />
      </AssociationSet>
</EntityContainer>
    <EntityType Name="Upgrades">
      <Key>
        <PropertyRef Name="UpgradeID" />
      </Key>
      <Property Name="UpgradeID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />
      <Property Name="DefaultOptionID" Type="int" />
    </EntityType>
<Association Name="FK__Upgrades__Defaul__70DDC3D8">
          <End Role="Options" Type="DModel.Store.Options" Multiplicity="0..1" />
          <End Role="Upgrades" Type="DModel.Store.Upgrades" Multiplicity="*" />
          <ReferentialConstraint>
            <Principal Role="Options">
              <PropertyRef Name="KitNodeID" />
            </Principal>
            <Dependent Role="Upgrades">
              <PropertyRef Name="DefaultOptionID" />
            </Dependent>
          </ReferentialConstraint>
        </Association>
</Schema></edmx:StorageModels>

<!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="DModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
        <EntityContainer Name="DEntities" annotation:LazyLoadingEnabled="true">
<EntitySet Name="Upgrades" EntityType="DModel.Upgrade" />
          <AssociationSet Name="Upgrade_DefaultOption" Association="DModel.Upgrade_DefaultOption">
            <End Role="Options" EntitySet="Options" />
            <End Role="Upgrades" EntitySet="Upgrades" />
          </AssociationSet>
</EntityContainer>
  <Association Name="Upgrade_DefaultOption">
          <End Role="Options" Type="DModel.Option" Multiplicity="0..1" />
          <End Role="Upgrades" Type="DModel.Upgrade" Multiplicity="*" />
        </Association>
<EntityType Name="Upgrade">
          <Key>
            <PropertyRef Name="UpgradeID" />
          </Key>
          <Property Name="UpgradeID" Nullable="false" annotation:StoreGeneratedPattern="Identity" Type="Int32" />
          <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
          <NavigationProperty Name="DefaultOption" Relationship="DModel.Upgrade_DefaultOption" FromRole="Upgrades" ToRole="Options" />
          <NavigationProperty Name="OptInOptions" Relationship="DModel.OptInOptions" FromRole="Upgrades" ToRole="Options" />
        </EntityType>
     </Schema>
    </edmx:ConceptualModels>

   <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="DModelStoreContainer" CdmEntityContainer="DEntities">
          <EntitySetMapping Name="Upgrades"><EntityTypeMapping TypeName="DModel.Upgrade"><MappingFragment StoreEntitySet="Upgrades">
            <ScalarProperty Name="UpgradeID" ColumnName="UpgradeID" />
            <ScalarProperty Name="Name" ColumnName="Name" />
          </MappingFragment></EntityTypeMapping></EntitySetMapping>
 <AssociationSetMapping Name="Upgrade_DefaultOption" TypeName="DModel.Upgrade_DefaultOption" StoreEntitySet="FK__Upgrades__Defaul__70DDC3D8">
            <EndProperty Name="Upgrades">
              <ScalarProperty Name="UpgradeID" ColumnName="UpgradeID"/>
            </EndProperty>
            <EndProperty Name="Options">
              <ScalarProperty Name="KitNodeID" ColumnName="DefaultOptionID"/>
            </EndProperty>
          </AssociationSetMapping>
 </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>

It will generate code, but when I try to use it I get an "Error 2007: The Table 'FK_Upgrades_Defaul__70DDC3D8' specified as part of this MSL does not exist in MetadataWorkspace." It looks like it can't find the underlying foreign key constraint that the SSDL is supposed to use, but I do see one with that name in the database.

Really, I feel I don't have a very good understanding of how this kind of mapping is supposed to work in general - is a foreign key constraint in the database really treated as an "associationset"? - but this is the closest I can work out. I don't know if anyone could diagnose the problem from the info I've given, but any pointers on where to look? I've tried various things like changing the StoreEntitySet to point to Options, etc. but they just result in different errors.

Answer 1

Try changing how you define your foreign key, see: How do I create a foreign key in SQL Server?

You will probably have to delete and recreate you model.