How to map a relation between a View and a Table in Entity Framework

Imagine creating a view in your database with some data you need aggregated from different tables.  This data could be associated to another table in your database.  While you might not want to make any changes in your database you may want the conceptual diagram in your application to link up these entities so it makes it easier for you to develop with them.

The first thing you need to do is create you edmx and add the wanted tables and views.  Make sure that you have a primary key defined for your view, entity framework will by default take all non nullable columns (… go figure).  Then when you have the desired view and table on your entity diagram you create a new association.

In the ‘add association ‘ window you can then select the two entities you want to have a relation between.  Deselect the ‘Add foreign key properties to the ‘xxxx’ Entity’, as it will add a new property to the associated table that will act as a foreign key.  In this case it won’t be necessary because I already have my foreign keys mapped.

Once you’ve done this you should receive the following error:

No mapping specified for the following EntitySet/AssociationSet – PersonFunctie

So this is where the entity designer falls short.  We defined the association between the two entities but entity framework cannot find the association in the storage model so it cannot figure out which properties have to be used.  To fix this open the edmx in an xml editor.  Then browse to the line that the error indicated and you’ll find this:

<Association Name="PersonFunctie">

  <End Type="eOpvolgingModel.Person" Role="Person" Multiplicity="1" />

  <End Type="eOpvolgingModel.Functie" Role="Functie" Multiplicity="*" />

</Association>

Now you just need to say to the conceptual model which properties define this association:

<Association Name="PersonFunctie">

  <End Type="eOpvolgingModel.Person" Role="Person" Multiplicity="1" />

  <End Type="eOpvolgingModel.Functie" Role="Functie" Multiplicity="*" />

  <ReferentialConstraint>

    <Principal Role="Person">

      <PropertyRef Name="PersonId" />

    </Principal>

    <Dependent Role="Functie">

      <PropertyRef Name="Persoon_Id" />

    </Dependent>

  </ReferentialConstraint>

</Association>

Save it, build it, run it, and voila.  Everything should work now.

Until next post!