实体框架：备用解决方案，以使用非主唯一键的关联 [英] Entity Framework: Alternate solution to using non primary unique keys in an association

问题描述

我知道实体框架的工作不允许你生成使用非主唯一键作为一个外键关联的数据库模型。我可以手动修改EDMX？如果是这样，有人可以给我一个例子或参考？如果没有，是否有任何其他的可能性？

I know the entity frame work does not allow you to generate a model from a database using non primary unique keys as a Foreign Key association. Can I modify the EDMX manually? If so, can someone provide me an example or reference? If not, are there any other possibilities?

最简单的例子：

下面是DDL的表。你会发现我有PersonType.Type $ C $的外键C至Person.Type code

Here is the DDL for the tables. You will notice I have a foreign Key from PersonType.TypeCode to Person.TypeCode

CREATE TABLE [dbo].[PersonType](
    [PersonTypeId] [int] NOT NULL,
    [TypeCode] [varchar](10) NOT NULL,
    [TypeDesc] [varchar](max) NULL,
 CONSTRAINT [PK_PersonType] PRIMARY KEY CLUSTERED 
 ([PersonTypeId] ASC)
 CONSTRAINT [UK_PersonType] UNIQUE NONCLUSTERED 
 ([TypeCode] ASC)
)

CREATE TABLE [dbo].[Person](
    [PersonId] [int] NOT NULL,
    [TypeCode] [varchar](10) NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
 ([PersonId] ASC)
)

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_PersonType] FOREIGN KEY([TypeCode])
REFERENCES [dbo].[PersonType] ([TypeCode])

ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_PersonType]

这里是EDMX生成

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="testModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
        <EntityContainer Name="testModelStoreContainer">
          <EntitySet Name="Person" EntityType="testModel.Store.Person" store:Type="Tables" Schema="dbo" />
          <EntitySet Name="PersonType" EntityType="testModel.Store.PersonType" store:Type="Tables" Schema="dbo" />
        </EntityContainer>
        <EntityType Name="Person">
          <Key>
            <PropertyRef Name="PersonId" />
          </Key>
          <Property Name="PersonId" Type="int" Nullable="false" />
          <Property Name="TypeCode" Type="varchar" Nullable="false" MaxLength="10" />
        </EntityType>
        <!--Errors Found During Generation:
      warning 6035: The relationship 'FK_Person_PersonType' has columns that are not part of the key of the table on the primary side of the relationship. The relationship was excluded.
      -->
        <EntityType Name="PersonType">
          <Key>
            <PropertyRef Name="PersonTypeId" />
          </Key>
          <Property Name="PersonTypeId" Type="int" Nullable="false" />
          <Property Name="TypeCode" Type="varchar" Nullable="false" MaxLength="10" />
          <Property Name="TypeDesc" Type="varchar(max)" />
        </EntityType>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="testModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
        <EntityContainer Name="testEntities">
          <EntitySet Name="People" EntityType="testModel.Person" />
          <EntitySet Name="PersonTypes" EntityType="testModel.PersonType" />
        </EntityContainer>
        <EntityType Name="Person">
          <Key>
            <PropertyRef Name="PersonId" />
          </Key>
          <Property Name="PersonId" Type="Int32" Nullable="false" />
          <Property Name="TypeCode" Type="String" Nullable="false" MaxLength="10" Unicode="false" FixedLength="false" />
        </EntityType>
        <EntityType Name="PersonType">
          <Key>
            <PropertyRef Name="PersonTypeId" />
          </Key>
          <Property Name="PersonTypeId" Type="Int32" Nullable="false" />
          <Property Name="TypeCode" Type="String" Nullable="false" MaxLength="10" Unicode="false" FixedLength="false" />
          <Property Name="TypeDesc" Type="String" MaxLength="Max" Unicode="false" FixedLength="false" />
        </EntityType>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
        <EntityContainerMapping StorageEntityContainer="testModelStoreContainer" CdmEntityContainer="testEntities">
          <EntitySetMapping Name="People"><EntityTypeMapping TypeName="testModel.Person"><MappingFragment StoreEntitySet="Person">
            <ScalarProperty Name="PersonId" ColumnName="PersonId" />
            <ScalarProperty Name="TypeCode" ColumnName="TypeCode" />
          </MappingFragment></EntityTypeMapping></EntitySetMapping>
          <EntitySetMapping Name="PersonTypes"><EntityTypeMapping TypeName="testModel.PersonType"><MappingFragment StoreEntitySet="PersonType">
            <ScalarProperty Name="PersonTypeId" ColumnName="PersonTypeId" />
            <ScalarProperty Name="TypeCode" ColumnName="TypeCode" />
            <ScalarProperty Name="TypeDesc" ColumnName="TypeDesc" />
          </MappingFragment></EntityTypeMapping></EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>

我试图修改EDMX创建personType和人之间的导航属性格式，但没有成功。我只是想我可以手动创建的关联一些如何。任何帮助将是AP preciated。

I have tried to modify the EDMX to create the navigation propery between personType and Person but have been unsuccessful. I just figured I could create the association manually some how. Any help would be appreciated.

推荐答案

不幸的是，截至目前没有办法确定一个候选键的关联（即 PersonType.Type code 的） 。因为在EF（3.5和4.0）FKS必须指向主键。

据的亚历克斯·詹姆斯的从<一个href="http://stackoverflow.com/questions/3714334/having-serious-problems-with-entity-framework-foreign-keys">his在这里发布的，这是后话了EF团队正在考虑的下一个版本。

Unfortunately as of now there is no way to define an association on a candidate key (i.e. PersonType.TypeCode). because In EF (3.5 and 4.0) FKs MUST point to Primary Keys.

According to Alex James from his post here, this is something the EF team are considering for the next version.

这篇关于实体框架：备用解决方案，以使用非主唯一键的关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！