EntityFramework mappings - what is wrong with this mapping?

Question

I'm kind of stuck working out where I'm going wrong with the below entity framework mapping. What I've done is:

a) created a Sqlite database (see below, noting Sqlite doesn't allow FK constraints) b) created a blank Entity Data Mode & then created the model from the database c) issue is then trying to add the Model association so it picks up and uses the "ProcessNameId" column I've created in the database in the USAGES table. I've been trying to use the GUI Table Mapping pane to do this but it doesn't seem too intuitive.

Issue - I'm getting an: Error 3 Error 3021: Problem in mapping fragments starting at line 85:Each of the following columns in table Usages is mapped to multiple conceptual side properties: Usages.ProcessNameId is mapped to Error 4 Error 3025: Problem in mapping fragments starting at line 85:Must specify mapping for all key properties (Usages.Id) of table Usages.

Question - So the question is what changes to the XML do I need to make to have the association work correctly?

SQL

CREATE TABLE "ProcessNames" (
    "Id" INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL , 
    "Name" VARCHAR NOT NULL  UNIQUE
)

CREATE TABLE "Usages" (
    "Id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , 
    "ProcessNameId" INTEGER NOT NULL , 
    "Amount" INTEGER NOT NULL , 
    "Datetime" DATETIME NOT NULL 
)

Model Config

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx"&gt;
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
    <Schema Namespace="Model1.Store" Alias="Self" Provider="System.Data.SQLite" ProviderManifestToken="ISO8601" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl"&gt;
        <EntityContainer Name="Model1StoreContainer">
          <EntitySet Name="ProcessNames" EntityType="Model1.Store.ProcessNames" store:Type="Tables" />
          <EntitySet Name="Usages" EntityType="Model1.Store.Usages" store:Type="Tables" />
        </EntityContainer>
        <EntityType Name="ProcessNames">
          <Key>
            <PropertyRef Name="Id" />
          </Key>
          <Property Name="Id" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="Name" Type="nvarchar" Nullable="false" />
        </EntityType>
        <EntityType Name="Usages">
          <Key>
            <PropertyRef Name="Id" />
          </Key>
          <Property Name="Id" Type="integer" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="ProcessNameId" Type="integer" Nullable="false" />
          <Property Name="Amount" Type="integer" Nullable="false" />
          <Property Name="Datetime" Type="datetime" Nullable="false" />
        </EntityType>
      </Schema></edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema xmlns="http://schemas.microsoft.com/ado/2008/09/edm" xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" Namespace="Model1" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation"&gt;
        <EntityContainer Name="Model1Container" annotation:LazyLoadingEnabled="true">
          <EntitySet Name="ProcessNames" EntityType="Model1.ProcessName" />
          <EntitySet Name="Usages" EntityType="Model1.Usage" />
          <AssociationSet Name="ProcessNameUsage" Association="Model1.ProcessNameUsage">
            <End Role="ProcessName" EntitySet="ProcessNames" />
            <End Role="Usage" EntitySet="Usages" />
          </AssociationSet>
        </EntityContainer>
        <EntityType Name="ProcessName">
          <Key>
            <PropertyRef Name="Id" />
          </Key>
          <Property Type="Int64" Name="Id" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
          <Property Type="String" Name="Name" Nullable="false" MaxLength="2147483647" FixedLength="false" Unicode="true" />
          <NavigationProperty Name="Usages" Relationship="Model1.ProcessNameUsage" FromRole="ProcessName" ToRole="Usage" />
        </EntityType>
        <EntityType Name="Usage">
          <Key>
            <PropertyRef Name="Id" />
          </Key>
          <Property Type="Int64" Name="Id" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
          <Property Type="Int64" Name="ProcessNameId" Nullable="false" />
          <Property Type="Int64" Name="Amount" Nullable="false" />
          <Property Type="DateTime" Name="Datetime" Nullable="false" />
          <NavigationProperty Name="ProcessName" Relationship="Model1.ProcessNameUsage" FromRole="Usage" ToRole="ProcessName" />
        </EntityType>
        <Association Name="ProcessNameUsage">
          <End Type="Model1.ProcessName" Role="ProcessName" Multiplicity="1" />
          <End Type="Model1.Usage" Role="Usage" Multiplicity="*" />
        </Association>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
    <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs"&gt;
  <EntityContainerMapping StorageEntityContainer="Model1StoreContainer" CdmEntityContainer="Model1Container">
          <EntitySetMapping Name="ProcessNames">
            <EntityTypeMapping TypeName="Model1.ProcessName">
              <MappingFragment StoreEntitySet="ProcessNames">
                <ScalarProperty Name="Name" ColumnName="Name" />
                <ScalarProperty Name="Id" ColumnName="Id" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <EntitySetMapping Name="Usages">
            <EntityTypeMapping TypeName="Model1.Usage">
              <MappingFragment StoreEntitySet="Usages">
                <ScalarProperty Name="Datetime" ColumnName="Datetime" />
                <ScalarProperty Name="Amount" ColumnName="Amount" />
                <ScalarProperty Name="ProcessNameId" ColumnName="ProcessNameId" />
                <ScalarProperty Name="Id" ColumnName="Id" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <AssociationSetMapping Name="ProcessNameUsage" TypeName="Model1.ProcessNameUsage" StoreEntitySet="Usages">
            <EndProperty Name="ProcessName">
              <ScalarProperty Name="Id" ColumnName="ProcessNameId" />
            </EndProperty>
            <EndProperty Name="Usage">
              <ScalarProperty Name="Id" ColumnName="ProcessNameId" />
            </EndProperty>
          </AssociationSetMapping>
        </EntityContainerMapping>
</Mapping></edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx"&gt;
    <edmx:Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </edmx:Connection>
    <edmx:Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="True" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="False" />
      </DesignerInfoPropertySet>
    </edmx:Options>
    <!-- Diagram content (shape and connector positions) -->
    <edmx:Diagrams>
      <Diagram Name="Model1" >
        <EntityTypeShape EntityType="Model1.ProcessName" Width="1.5" PointX="0.75" PointY="0.75" Height="1.2636116536458317" />
        <EntityTypeShape EntityType="Model1.Usage" Width="1.5" PointX="3" PointY="0.75" Height="1.7566536458333339" />
        <AssociationConnector Association="Model1.ProcessNameUsage">
          <ConnectorPoint PointX="2.25" PointY="1.4639794921875002" />
          <ConnectorPoint PointX="3" PointY="1.4639794921875002" />
        </AssociationConnector>
      </Diagram>
    </edmx:Diagrams>
  </edmx:Designer>
</edmx:Edmx>

thanks

Answer 1

Have solved it. So to get it working after importing the model from the sqlite database I did the following:

a) deleted the the imported "ProcessNameId" column

b) then after this added the association between the two entities - it automatically used "ProcessNameId" as the FK id which was good as it matched that which I already had in the database (else I would have had to chase this up)

Things worked fine after this.

Answer 2

If you want to expose the foreign key ID and also have the EF manage the relationship, you can use Foreign Key Associations, but these are only available in EF 4.0