Entity framework stored procedure mapping results not unique entities

Question

I have a problem where I've mapped a stored procedure named sp_getMyEntity, which takes in one parameter called Id and then maps the results to a custom entity called MyEntity

I'm using mock data to illustrate the point. When I run the stored procedure with an id of 1, I get two distinct results back from the database:

exec [dbo].[sp_getMyEntity] @Id=1

Results:

ID - AddressId

1 - 6

1 - 3

When querying the ObjectContext using LINQ, I get 2 MyEntity's back but the AddressId for both of them is 6, not 6 and 3 respectively. Here is my call using LINQ:

Entities context = new Entities();
var entities = from s in context.GetMyEntity(1)
               select s;
return entities.ToList();

Here is the EDMX xml:

<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"&gt;
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="MyProjectModel.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"&gt;
      <EntityContainer Name="MyProjectModelStoreContainer">
        <EntitySet Name="MyEntitySet" EntityType="MyProjectModel.Store.MyEntity" />
      </EntityContainer>
      <Function Name="sp_getMyEntity" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
        <Parameter Name="Id" Type="int" Mode="In" />
      </Function>
      <EntityType Name="MyEntity">
        <Key>
          <PropertyRef Name="Id" />
        </Key>
        <Property Name="Id" Type="int" Nullable="false" />
        <Property Name="AddressId" Type="int" Nullable="true" />
      </EntityType>     
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="MyProjectModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm"&gt;
        <EntityContainer Name="MyProjectViewEntities" >
          <EntitySet Name="MyEntitySet" EntityType="MyProjectModel.MyEntity" />
          <FunctionImport Name="GetMyEntity" EntitySet="MyEntitySet" ReturnType="Collection(MyProjectModel.MyEntity)">
            <Parameter Name="Id" Mode="In" Type="Int32" />
          </FunctionImport>
        </EntityContainer>
        <EntityType Name="MyEntity">
          <Key>
            <PropertyRef Name="Id" />
          </Key>
          <Property Name="Id" Type="Int32" Nullable="false" />
          <Property Name="AddressId" Type="Int32" Nullable="true" />
        </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="MyProjectModelStoreContainer" CdmEntityContainer="MyProjectViewEntities" >
          <FunctionImportMapping FunctionImportName="GetMyEntity" FunctionName="MyProjectModel.Store.sp_getMyEntity" />
          <EntitySetMapping Name="MyEntitySet">
            <EntityTypeMapping TypeName="IsTypeOf(MyProjectModel.MyEntity)">
              <MappingFragment StoreEntitySet="MyEntitySet">
                <ScalarProperty Name="AddressId" ColumnName="AddressId" />
                <ScalarProperty Name="Id" ColumnName="Id" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2007/06/edmx"&gt;
    <edmx:Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </edmx:Connection>
    <edmx:Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
      </DesignerInfoPropertySet>
    </edmx:Options>
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram Name="MyProjectModel" ZoomLevel="100" >
        <EntityTypeShape EntityType="MyProjectModel.MyEntity" Width="1.5" PointX="5.25" PointY="0.5" Height="7.8375048828125" />
        <EntityTypeShape EntityType="MyProjectModel.MyEntity" Width="1.5" PointX="5.5" PointY="1.375" Height="1.2636116536458335" /></Diagram></edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

Any ideas why the MyEntity's are not unique?

Answer 1

Because the SP column which your SSDL identifies as the key is not a unique column. You cannot have a "key" with duplicate values.

Answer 2

As Craig stated above my Key property was not unique, so I ended up changing the SQL to look at follows:

SELECT 
    ROW_NUMBER() OVER(ORDER BY Id) AS KeyId
  , Id
  , AddressId
FROM
  MyTable
WHERE
  Id = @Id

Then the EDMX xml looks as follows, notice the new key is a bigint and Int64:

<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"&gt;
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="MyProjectModel.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"&gt;
      <EntityContainer Name="MyProjectModelStoreContainer">
        <EntitySet Name="MyEntitySet" EntityType="MyProjectModel.Store.MyEntity" />
      </EntityContainer>
      <Function Name="sp_getMyEntity" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
        <Parameter Name="Id" Type="int" Mode="In" />
      </Function>
      <EntityType Name="MyEntity">
        <Key>
          <PropertyRef Name="KeyId" />
        </Key>
        <Property Name="KeyId" Type="bigint" Nullable="false" />
        <Property Name="Id" Type="int" Nullable="false" />
        <Property Name="AddressId" Type="int" Nullable="true" />
      </EntityType>     
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="MyProjectModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm"&gt;
        <EntityContainer Name="MyProjectViewEntities" >
          <EntitySet Name="MyEntitySet" EntityType="MyProjectModel.MyEntity" />
          <FunctionImport Name="GetMyEntity" EntitySet="MyEntitySet" ReturnType="Collection(MyProjectModel.MyEntity)">
            <Parameter Name="Id" Mode="In" Type="Int32" />
          </FunctionImport>
        </EntityContainer>
        <EntityType Name="MyEntity">
          <Key>
            <PropertyRef Name="KeyId" />
          </Key>
          <Property Name="KeyId" Type="Int64" Nullable="false" />
          <Property Name="Id" Type="Int32" Nullable="false" />
          <Property Name="AddressId" Type="Int32" Nullable="true" />
        </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="MyProjectModelStoreContainer" CdmEntityContainer="MyProjectViewEntities" >
          <FunctionImportMapping FunctionImportName="GetMyEntity" FunctionName="MyProjectModel.Store.sp_getMyEntity" />
          <EntitySetMapping Name="MyEntitySet">
            <EntityTypeMapping TypeName="IsTypeOf(MyProjectModel.MyEntity)">
              <MappingFragment StoreEntitySet="MyEntitySet">
                <ScalarProperty Name="AddressId" ColumnName="AddressId" />
                <ScalarProperty Name="Id" ColumnName="Id" />
                <ScalarProperty Name="KeyId" ColumnName="KeyId" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2007/06/edmx"&gt;
    <edmx:Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </edmx:Connection>
    <edmx:Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
      </DesignerInfoPropertySet>
    </edmx:Options>
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram Name="MyProjectModel" ZoomLevel="100" >
        <EntityTypeShape EntityType="MyProjectModel.MyEntity" Width="1.5" PointX="5.25" PointY="0.5" Height="7.8375048828125" />
        <EntityTypeShape EntityType="MyProjectModel.MyEntity" Width="1.5" PointX="5.5" PointY="1.375" Height="1.2636116536458335" /></Diagram></edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>