EF4：映射到“额外字段”桌子

Question

我有两个表：

TICKET

• GUID (PK)
• 还有与此问题无关的其他列

TICKET_PROPERTIES

• GUID (partial PK, FK to TICKET.GUID)
• FIELDNAME (partial PK)
• FIELDVALUE

如果我有这些票证记录：

• { GUID = "my_ticket" }
• { GUID = "my_ticket_2" }

那么我可能有一些 TICKET_PROPERTIES 记录，例如：

• { GUID = "my_ticket", FIELDNAME = "FieldA", FIELDVALUE = "value a" }
• { GUID = "my_ticket", FIELDNAME = "FieldB" , FIELDVALUE = "foo" }
• { GUID = "my_ticket", FIELDNAME = "FieldC", FIELDVALUE = "bar" }
• { GUID = "my_ticket_2", FIELDNAME = "FieldC", FIELDVALUE = "blah" }
• { GUID = "my_ticket_2" ", FIELDNAME = "FieldD", FIELDVALUE = "data" }

如您所见，此 TICKET_PROPERTIES 表充当 TICKET 记录可以选择具有的字段的“额外字段”表。 TICKET 可能并不总是具有与 FIELDNAME="FieldA" 关联的 TICKET_PROPERTIES 记录，但有时确实如此。

如果我想向此 EDMX 文件中的 TICKET 类添加 FieldA 属性，我该怎么做？ 如果这样的属性可以在由如下内容生成的查询中构建一个 where 条件，那就太好了：

MINE_EF_TestContext ctx = new MINE_EF_TestContext();
var someTickets = ctx.TICKET.Where(t => t.FieldA == "value a");

我希望能够添加多个这样的属性。例如，我可能还希望能够添加 FieldB 属性。

我的 EDMX：

<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
<edmx:Runtime>
<edmx:StorageModels>
<Schema Namespace="MINE_EF_Test.Store" Alias="Self" Provider="EFOracleProvider" ProviderManifestToken="11g" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
  <EntityContainer Name="MINE_EF_TestStoreContainer">
    <EntitySet Name="TICKET" EntityType="MINE_EF_Test.Store.TICKET" store:Type="Tables" />
    <EntitySet Name="TICKET_PROPERTIES" EntityType="MINE_EF_Test.Store.TICKET_PROPERTIES" store:Type="Tables" />
    <AssociationSet Name="TICKET_PROPERTIES_TICKET_FK" Association="MINE_EF_Test.Store.TICKET_PROPERTIES_TICKET_FK">
      <End Role="TICKET" EntitySet="TICKET" />
      <End Role="TICKET_PROPERTIES" EntitySet="TICKET_PROPERTIES" />
    </AssociationSet>
  </EntityContainer>
  <EntityType Name="TICKET">
    <Key>
      <PropertyRef Name="GUID" />
    </Key>
    <Property Name="GUID" Type="varchar2" Nullable="false" MaxLength="36" />
  </EntityType>
  <EntityType Name="TICKET_PROPERTIES">
    <Key>
      <PropertyRef Name="FIELDNAME" />
      <PropertyRef Name="GUID" />
    </Key>
    <Property Name="FIELDNAME" Type="varchar2" Nullable="false" MaxLength="40" />
    <Property Name="FIELDVALUE" Type="varchar2" Nullable="false" MaxLength="1000" />
    <Property Name="GUID" Type="varchar2" Nullable="false" MaxLength="36" />
  </EntityType>
  <Association Name="TICKET_PROPERTIES_TICKET_FK">
    <End Role="TICKET" Type="MINE_EF_Test.Store.TICKET" Multiplicity="1" />
    <End Role="TICKET_PROPERTIES" Type="MINE_EF_Test.Store.TICKET_PROPERTIES" Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="TICKET">
        <PropertyRef Name="GUID" />
      </Principal>
      <Dependent Role="TICKET_PROPERTIES">
        <PropertyRef Name="GUID" />
      </Dependent>
    </ReferentialConstraint>
  </Association>
  <Association Name="TICKET_PROPERTIES_TICKET_FK_POC">
    <End Role="TICKET" Type="MINE_EF_Test.Store.TICKET" Multiplicity="0..1" />
    <End Role="TICKET_PROPERTIES" Type="MINE_EF_Test.Store.TICKET_PROPERTIES" Multiplicity="1" />
    <ReferentialConstraint>
      <Principal Role="TICKET_PROPERTIES">
        <PropertyRef Name="GUID" />
      </Principal>
      <Dependent Role="TICKET">
        <PropertyRef Name="GUID" />
      </Dependent>
    </ReferentialConstraint>
  </Association>
</Schema>
</edmx:StorageModels>
<edmx:ConceptualModels>
<Schema Namespace="MINE_EF_Test" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
  <EntityContainer Name="MINE_EF_TestContext" annotation:LazyLoadingEnabled="true">
    <EntitySet Name="TICKET" EntityType="MINE_EF_Test.TICKET" />
    <EntitySet Name="TICKET_PROPERTIES" EntityType="MINE_EF_Test.TICKET_PROPERTIES" />
    <AssociationSet Name="TICKET_PROPERTIES_TICKET_FK" Association="MINE_EF_Test.TICKET_PROPERTIES_TICKET_FK">
      <End Role="TICKET" EntitySet="TICKET" />
      <End Role="TICKET_PROPERTIES" EntitySet="TICKET_PROPERTIES" />
    </AssociationSet>
  </EntityContainer>
  <EntityType Name="TICKET">
    <Key>
      <PropertyRef Name="GUID" />
    </Key>
    <Property Name="GUID" Type="String" Nullable="false" MaxLength="36" Unicode="false" FixedLength="false" />
    <NavigationProperty Name="TICKET_PROPERTIES" Relationship="MINE_EF_Test.TICKET_PROPERTIES_TICKET_FK" FromRole="TICKET" ToRole="TICKET_PROPERTIES" />
  </EntityType>
  <EntityType Name="TICKET_PROPERTIES">
    <Key>
      <PropertyRef Name="FIELDNAME" />
      <PropertyRef Name="GUID" />
    </Key>
    <Property Name="FIELDNAME" Type="String" Nullable="false" MaxLength="40" Unicode="false" FixedLength="false" />
    <Property Name="FIELDVALUE" Type="String" Nullable="false" MaxLength="1000" Unicode="false" FixedLength="false" />
    <Property Name="GUID" Type="String" Nullable="false" MaxLength="36" Unicode="false" FixedLength="false" />
    <NavigationProperty Name="TICKET" Relationship="MINE_EF_Test.TICKET_PROPERTIES_TICKET_FK" FromRole="TICKET_PROPERTIES" ToRole="TICKET" />
  </EntityType>
  <Association Name="TICKET_PROPERTIES_TICKET_FK">
    <End Role="TICKET" Type="MINE_EF_Test.TICKET" Multiplicity="1" />
    <End Role="TICKET_PROPERTIES" Type="MINE_EF_Test.TICKET_PROPERTIES" Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="TICKET">
        <PropertyRef Name="GUID" />
      </Principal>
      <Dependent Role="TICKET_PROPERTIES">
        <PropertyRef Name="GUID" />
      </Dependent>
    </ReferentialConstraint>
  </Association>
</Schema>
</edmx:ConceptualModels>
<edmx:Mappings>
<Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
  <EntityContainerMapping StorageEntityContainer="MINE_EF_TestStoreContainer" CdmEntityContainer="MINE_EF_TestContext">
    <EntitySetMapping Name="TICKET">
      <EntityTypeMapping TypeName="MINE_EF_Test.TICKET">
        <MappingFragment StoreEntitySet="TICKET">
          <ScalarProperty Name="GUID" ColumnName="GUID" />
          </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <EntitySetMapping Name="TICKET_PROPERTIES">
      <EntityTypeMapping TypeName="MINE_EF_Test.TICKET_PROPERTIES">
        <MappingFragment StoreEntitySet="TICKET_PROPERTIES">
          <ScalarProperty Name="FIELDNAME" ColumnName="FIELDNAME" />
          <ScalarProperty Name="FIELDVALUE" ColumnName="FIELDVALUE" />
          <ScalarProperty Name="GUID" ColumnName="GUID" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    </EntityContainerMapping>
</Mapping>
</edmx:Mappings>
</edmx:Runtime>
<edmx:Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
<Connection><DesignerInfoPropertySet><DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" /></DesignerInfoPropertySet></Connection>
<edmx:Options />
<edmx:Diagrams >
      <Diagram Name="MINE_EF_Test" ZoomLevel="96">
        <EntityTypeShape EntityType="MINE_EF_Test.TICKET" Width="1.5" PointX="0.75" PointY="1.125" Height="1.4033821614583335" IsExpanded="true" />
        <EntityTypeShape EntityType="MINE_EF_Test.TICKET_PROPERTIES" Width="1.5" PointX="2.75" PointY="1.25" Height="1.787985026041667" IsExpanded="true" />
        <AssociationConnector Association="MINE_EF_Test.TICKET_PROPERTIES_TICKET_FK" ManuallyRouted="false">
          <ConnectorPoint PointX="2.25" PointY="1.8891910807291668" />
          <ConnectorPoint PointX="2.75" PointY="1.8891910807291668" />
        </AssociationConnector>
      </Diagram>
    </edmx:Diagrams>
</edmx:Designer></edmx:Edmx>

Answer 1

当前 EF 版本不提供从相关表到父实体属性的映射记录。此外，这种情况需要动态属性，因为您可以拥有不同数量、不同名称的相关记录。

您必须使用这种方法：

MINE_EF_TestContext ctx = new MINE_EF_TestContext();
var someTickets = from t in ctx.Tickets
                  join p in ctx.Properties on t.Guid equals p.Guid
                  where p.FieldName == "FieldA" && p.FieldValue == "value a"
                  select t;