none
How to create a simple one-to-many association from views without FKs ??? RRS feed

  • Question

  • Hello out there! Maybe sbdy can help me. I am absolutely stuck.

    I just want to create a simple one to many association on two views. It seems to be impossible....

    In my model I've got the two entities calls and customers that where created from views.

    The Customer has got an ID field. The call also has an ID field that points to the customer id. Now I would like to have a navigation property in customers that shows me all calls from the customer. I tried it the obvious way but this is only creating errors 302x.

    So, is there a way to do this??? And if yes, how can I do it?

     

    below is the edmx file that was created out of the database

     

    <?xml version="1.0" encoding="utf-8"?>
    <edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
      <!-- EF Runtime content -->
      <edmx:Runtime>
        <!-- SSDL content -->
        <edmx:StorageModels>
        <Schema Namespace="AdressModel.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/2009/02/edm/ssdl">
            <EntityContainer Name="AdressModelStoreContainer">
              <EntitySet Name="NET_Call" EntityType="AdressModel.Store.NET_Call" store:Type="Views" store:Schema="dbo" store:Name="NET_Call">
                <DefiningQuery>SELECT
          [NET_Call].[ID] AS [ID],
          [NET_Call].[RID] AS [RID],
          [NET_Call].[TICKETID] AS [TICKETID],
          [NET_Call].[DTRCVD] AS [DTRCVD],
          [NET_Call].[SDESC] AS [SDESC],
          [NET_Call].[QUEUE] AS [QUEUE],
          [NET_Call].[ASSIGNEDTO] AS [ASSIGNEDTO],
          [NET_Call].[STATUS] AS [STATUS]
          FROM [dbo].[NET_Call] AS [NET_Call]</DefiningQuery>
              </EntitySet>
              <EntitySet Name="NET_Customer" EntityType="AdressModel.Store.NET_Customer" store:Type="Views" store:Schema="dbo" store:Name="NET_Customer">
                <DefiningQuery>SELECT
          [NET_Customer].[ID] AS [ID],
          [NET_Customer].[Name] AS [Name],
          [NET_Customer].[Strasse] AS [Strasse],
          [NET_Customer].[Land] AS [Land],
          [NET_Customer].[PLZ] AS [PLZ],
          [NET_Customer].[Ort] AS [Ort],
          [NET_Customer].[Branch] AS [Branch],
          [NET_Customer].[Beschreibung] AS [Beschreibung],
          [NET_Customer].[Quality] AS [Quality],
          [NET_Customer].[Vertreter] AS [Vertreter],
          [NET_Customer].[COMPANYID] AS [COMPANYID],
          [NET_Customer].[OpenCalls] AS [OpenCalls]
          FROM [dbo].[NET_Customer] AS [NET_Customer]</DefiningQuery>
              </EntitySet>
            </EntityContainer>
            <!--Während der Generierung sind Fehler aufgetreten:
          Warnung 6002: Für Tabelle/Sicht 'Adress.dbo.NET_Call' ist kein Primärschlüssel definiert. Der Schlüssel wurde abgeleitet, und die Definition wurde als schreibgeschützte Tabelle/Sicht erstellt.
          -->
            <EntityType Name="NET_Call">
              <Key>
                <PropertyRef Name="ID" />
                <PropertyRef Name="RID" />
              </Key>
              <Property Name="ID" Type="int" Nullable="false" />
              <Property Name="RID" Type="int" Nullable="false" />
              <Property Name="TICKETID" Type="nvarchar" MaxLength="20" />
              <Property Name="DTRCVD" Type="datetime" />
              <Property Name="SDESC" Type="nvarchar" MaxLength="255" />
              <Property Name="QUEUE" Type="nchar" MaxLength="3" />
              <Property Name="ASSIGNEDTO" Type="nvarchar" MaxLength="4" />
              <Property Name="STATUS" Type="int" />
            </EntityType>
            <!--Während der Generierung sind Fehler aufgetreten:
          Warnung 6002: Für Tabelle/Sicht 'Adress.dbo.NET_Customer' ist kein Primärschlüssel definiert. Der Schlüssel wurde abgeleitet, und die Definition wurde als schreibgeschützte Tabelle/Sicht erstellt.
          -->
            <EntityType Name="NET_Customer">
              <Key>
                <PropertyRef Name="ID" />
              </Key>
              <Property Name="ID" Type="int" Nullable="false" />
              <Property Name="Name" Type="nvarchar" MaxLength="100" />
              <Property Name="Strasse" Type="nvarchar" MaxLength="48" />
              <Property Name="Land" Type="nvarchar" MaxLength="4" />
              <Property Name="PLZ" Type="nvarchar" MaxLength="10" />
              <Property Name="Ort" Type="nvarchar" MaxLength="48" />
              <Property Name="Branch" Type="nvarchar" MaxLength="48" />
              <Property Name="Beschreibung" Type="nvarchar" MaxLength="48" />
              <Property Name="Quality" Type="nvarchar" MaxLength="2" />
              <Property Name="Vertreter" Type="nvarchar" MaxLength="16" />
              <Property Name="COMPANYID" Type="int" />
              <Property Name="OpenCalls" Type="int" />
            </EntityType>
            <Function Name="CountOpenCalls" ReturnType="int" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
              <Parameter Name="ID" Type="int" Mode="In" />
            </Function>
          </Schema></edmx:StorageModels>
        <!-- CSDL content -->
        <edmx:ConceptualModels>
          <Schema Namespace="AdressModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
            <EntityContainer Name="AdressEntities" annotation:LazyLoadingEnabled="true">
              <EntitySet Name="NET_Call" EntityType="AdressModel.NET_Call" />
              <EntitySet Name="NET_Customer" EntityType="AdressModel.NET_Customer" />
            </EntityContainer>
            <EntityType Name="NET_Call">
              <Key>
                <PropertyRef Name="ID" />
                <PropertyRef Name="RID" />
              </Key>
              <Property Type="Int32" Name="ID" Nullable="false" />
              <Property Type="Int32" Name="RID" Nullable="false" />
              <Property Type="String" Name="TICKETID" MaxLength="20" FixedLength="false" Unicode="true" />
              <Property Type="DateTime" Name="DTRCVD" />
              <Property Type="String" Name="SDESC" MaxLength="255" FixedLength="false" Unicode="true" />
              <Property Type="String" Name="QUEUE" MaxLength="3" FixedLength="true" Unicode="true" />
              <Property Type="String" Name="ASSIGNEDTO" MaxLength="4" FixedLength="false" Unicode="true" />
              <Property Type="Int32" Name="STATUS" />
            </EntityType>
            <EntityType Name="NET_Customer">
              <Key>
                <PropertyRef Name="ID" />
              </Key>
              <Property Type="Int32" Name="ID" Nullable="false" />
              <Property Type="String" Name="Name" MaxLength="100" FixedLength="false" Unicode="true" />
              <Property Type="String" Name="Strasse" MaxLength="48" FixedLength="false" Unicode="true" />
              <Property Type="String" Name="Land" MaxLength="4" FixedLength="false" Unicode="true" />
              <Property Type="String" Name="PLZ" MaxLength="10" FixedLength="false" Unicode="true" />
              <Property Type="String" Name="Ort" MaxLength="48" FixedLength="false" Unicode="true" />
              <Property Type="String" Name="Branch" MaxLength="48" FixedLength="false" Unicode="true" />
              <Property Type="String" Name="Beschreibung" MaxLength="48" FixedLength="false" Unicode="true" />
              <Property Type="String" Name="Quality" MaxLength="2" FixedLength="false" Unicode="true" />
              <Property Type="String" Name="Vertreter" MaxLength="16" FixedLength="false" Unicode="true" />
              <Property Type="Int32" Name="COMPANYID" />
              <Property Type="Int32" Name="OpenCalls" />
            </EntityType>
          </Schema>
        </edmx:ConceptualModels>
        <!-- C-S mapping content -->
        <edmx:Mappings>
          <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
            <EntityContainerMapping StorageEntityContainer="AdressModelStoreContainer" CdmEntityContainer="AdressEntities">
              <EntitySetMapping Name="NET_Call">
                <EntityTypeMapping TypeName="AdressModel.NET_Call">
                  <MappingFragment StoreEntitySet="NET_Call">
                    <ScalarProperty Name="STATUS" ColumnName="STATUS" />
                    <ScalarProperty Name="ASSIGNEDTO" ColumnName="ASSIGNEDTO" />
                    <ScalarProperty Name="QUEUE" ColumnName="QUEUE" />
                    <ScalarProperty Name="SDESC" ColumnName="SDESC" />
                    <ScalarProperty Name="DTRCVD" ColumnName="DTRCVD" />
                    <ScalarProperty Name="TICKETID" ColumnName="TICKETID" />
                    <ScalarProperty Name="RID" ColumnName="RID" />
                    <ScalarProperty Name="ID" ColumnName="ID" />
                  </MappingFragment>
                </EntityTypeMapping>
              </EntitySetMapping>
              <EntitySetMapping Name="NET_Customer">
                <EntityTypeMapping TypeName="AdressModel.NET_Customer">
                  <MappingFragment StoreEntitySet="NET_Customer">
                    <ScalarProperty Name="OpenCalls" ColumnName="OpenCalls" />
                    <ScalarProperty Name="COMPANYID" ColumnName="COMPANYID" />
                    <ScalarProperty Name="Vertreter" ColumnName="Vertreter" />
                    <ScalarProperty Name="Quality" ColumnName="Quality" />
                    <ScalarProperty Name="Beschreibung" ColumnName="Beschreibung" />
                    <ScalarProperty Name="Branch" ColumnName="Branch" />
                    <ScalarProperty Name="Ort" ColumnName="Ort" />
                    <ScalarProperty Name="PLZ" ColumnName="PLZ" />
                    <ScalarProperty Name="Land" ColumnName="Land" />
                    <ScalarProperty Name="Strasse" ColumnName="Strasse" />
                    <ScalarProperty Name="Name" ColumnName="Name" />
                    <ScalarProperty Name="ID" ColumnName="ID" />
                  </MappingFragment>
                </EntityTypeMapping>
              </EntitySetMapping>
            </EntityContainerMapping>
          </Mapping>
        </edmx:Mappings>
      </edmx:Runtime>
      <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
      <Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
        <Connection>
          <DesignerInfoPropertySet>
            <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
          </DesignerInfoPropertySet>
        </Connection>
        <Options>
          <DesignerInfoPropertySet>
            <DesignerProperty Name="ValidateOnBuild" Value="true" />
            <DesignerProperty Name="EnablePluralization" Value="False" />
            <DesignerProperty Name="IncludeForeignKeysInModel" Value="True" />
          </DesignerInfoPropertySet>
        </Options>
        <!-- Diagram content (shape and connector positions) -->
        <Diagrams>
          <Diagram Name="Address">
            <EntityTypeShape EntityType="AdressModel.NET_Call" Width="1.5" PointX="3.125" PointY="0.75" Height="2.5571907552083335" />
            <EntityTypeShape EntityType="AdressModel.NET_Customer" Width="1.5" PointX="1.25" PointY="0.75" Height="3.3263964843749996" />
          </Diagram>
        </Diagrams>
      </Designer>
    </edmx:Edmx>


    Thursday, November 3, 2011 10:31 AM

Answers

  • Ok, I found the solution myself. It is very important to delete ID from NET_Calls (which points to NET_Customer) and to set NET_Calls as the entity for the mapping of the association. If I go like this, it works!

    thanks and regards.

    Sven

    Thursday, November 3, 2011 4:41 PM

All replies

  • Ok, I found the solution myself. It is very important to delete ID from NET_Calls (which points to NET_Customer) and to set NET_Calls as the entity for the mapping of the association. If I go like this, it works!

    thanks and regards.

    Sven

    Thursday, November 3, 2011 4:41 PM
  • Hi conanthebarber,

    Thanks for sharing your experience here.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, November 7, 2011 8:34 AM
    Moderator