当前位置： 文江博客话题详情

实体框架存储过程映射结果不是唯一实体

发布于 2024-08-23 07:38:46 字数 4984 浏览 3 评论 0原文

我遇到一个问题，我映射了一个名为 sp_getMyEntity 的存储过程，该过程接受一个名为 Id 的参数，然后将结果映射到名为 MyEntity 的自定义实体

我使用模拟数据来说明这一点。当我运行 id 为 1 的存储过程时，我从数据库中得到两个不同的结果：

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

结果：

ID - AddressId

1 - 6

1 - 3

使用 LINQ 查询 ObjectContext 时，我得到 2 个 MyEntity，但它们的 AddressId 都是 6，而不是分别是 6 和 3。这是我使用 LINQ 的调用：

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

这是 EDMX xml：

<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="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">
      <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">
        <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">
    <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>

有什么想法为什么 MyEntity 不唯一吗？

原文

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">
  <!-- 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">
      <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">
        <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">
    <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?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

GRAY°灰色天空 2024-08-30 07:38:46

因为你的 SSDL 识别为键的 SP 列不是唯一的列。您不能拥有具有重复值的“键”。

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

回复 原文
善良天后 2024-08-30 07:38:46

正如 Craig 上面所说，我的 Key 属性不是唯一的，因此我最终更改了 SQL，如下所示：

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

然后 EDMX xml 如下所示，注意新密钥是 bigint 和 Int64 ：

<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="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">
      <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">
        <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">
    <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>

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">
  <!-- 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">
      <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">
        <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">
    <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>
回复 原文
~没有更多了~

关于作者

流云如水

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

1CH1MKgiKxn9p

文章 0 评论 0

ゞ记忆︶ㄣ

文章 0 评论 0

JackDx

文章 0 评论 0

信远

文章 0 评论 0

yaoduoduo1995

文章 0 评论 0

霞映澄塘

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文