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?
Easiest Example:
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]
Here is the EDMX Generated
<?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>
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.