Two column foreign key in entity framework

.net c# entity-framework sql visual-studio-2013

Question

I have two tables: operation and operation_category_element_relation.

The operation table has a composite primary key operation_id: bigint and date_data: nvarchar(10). The operation_category_element_relation has these columns as well. There is a relationship between the tables based on these two columns. After adding the ADO.NET Entity Data Mode I get two errors:

Error 13101: The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'operation_date_data' on entity 'operation_category_element_relation' does not match the type of property 'operation_id' on entity 'operation' in the referential constraint 'FK_operation_category_element_relation_operation'.

and

Error 13101: The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'operation_id' on entity 'operation_category_element_relation' does not match the type of property 'date_data' on entity 'operation' in the referential constraint 'FK_operation_category_element_relation_operation'.

Can you please explain what the problem is and how to get rid of it?

The content of autogenerated edmx file is:

 <?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="3.0" xmlns:edmx="http://schemas.microsoft.com/ado/2009/11/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl"     Namespace="TEMPDataModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005">
        <EntityContainer Name="TEMPDataModelTargetContainer"></EntityContainer>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema xmlns="http://schemas.microsoft.com/ado/2009/11/edm" xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" Namespace="TEMPDataModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" annotation:UseStrongSpatialTypes="false">
        <EntityContainer Name="TEMPDataModelContainer" annotation:LazyLoadingEnabled="true"></EntityContainer>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs" Space="C-S">
        <Alias Key="Model" Value="TEMPDataModel" />
        <Alias Key="Target" Value="TEMPDataModel.Store" />
        <EntityContainerMapping CdmEntityContainer="TEMPDataModelContainer" StorageEntityContainer="TEMPDataModelTargetContainer"></EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <Designer xmlns="http://schemas.microsoft.com/ado/2009/11/edmx">
    <Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </Connection>
    <Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="true" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="true" />
        <DesignerProperty Name="UseLegacyProvider" Value="false" />
        <DesignerProperty Name="CodeGenerationStrategy" Value="None" />
      </DesignerInfoPropertySet>
    </Options>
    <!-- Diagram content (shape and connector positions) -->
    <Diagrams></Diagrams>
  </Designer>
</edmx:Edmx>
1
4
3/18/2014 7:32:49 PM

Accepted Answer

Found the answer here: https://entityframework.codeplex.com/workitem/1735

This happens for composite foreign keys if order of foreign key columns is different from the order of key columns in the principal table. Sample tables that could be used to repro this:

CREATE TABLE [dbo].[Table1] (
  [Id]       INT           NOT NULL,
  [IdString] NVARCHAR (50) NOT NULL,
  PRIMARY KEY CLUSTERED ([IdString] ASC, [Id] ASC)
);

CREATE TABLE [dbo].[Table3]
(
  [TableId] INT NOT NULL PRIMARY KEY,
  [IdString] NVARCHAR (50) NULL,
  [Id]       INT           NULL, 
  CONSTRAINT [FK_Table3_ToTable] FOREIGN KEY (IdString, Id) REFERENCES [Table1](IdString, Id),
)

UPD. In my case I also had to change the order of fields according to the order of fields in the PK.

Hope this helps

5
8/18/2014 4:27:26 PM

Popular Answer

It looks like you've set the relationship with the columns flipped. Note that the first error states that the type of operation_date_data does not match operation_id. It should matching with the data not id in the related object. Check the referential constraint on your association to make sure the columns are in the same order.



Related Questions





Related

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