实体框架：在关联中使用非主要唯一键的替代解决方案 [英] 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.TypeCode到Person.TypeCode

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和Person之间的导航属性，但是没有成功。我只是想到我可以手动创建一个关联。任何帮助将不胜感激。

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.TypeCode ）。因为在EF（3.5和4.0）中，FK必须指向主键。



根据 的.com / b / alexj / =nofollow noreferrer>

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屋！