Associations with Payloads - closing the loop

I'm assuming you've read part 1 and part 2, if not why not? Just kidding, they will make this post a little easier to understand though.

I'm going to show you how to create a read-only association with a Payload. 

Okay so imagine you have this model:

And you want to have be able to do this:

var products = from o in ctx.Orders
from p in o.Products
where o.ID == 3435
select p;

To do that you need a READONLY association that uses the OrderLine table as a Many 2 Many table.

This is how you do it:

1) Create a Entity in the SSDL that represents a ProductOrder association something like this:

<EntityType Name="ProductOrder">
  <Key>
      <PropertyRef Name="ProductID" />
      <PropertyRef Name="OrderID" />
    </Key>
    <Property Name="ProductID" Type="int" Nullable="false" />
    <Property Name="OrderID" Type="int" Nullable="false" />  </EntityType>

2) Now create an EntitySet to act as a virtual table using a DefiningQuery (you could do this with a view in the database too):

<EntitySet Name="ProductOrders" EntityType="OrdersDatabaseModel.Store.ProductOrder">
      <DefiningQuery>
              SELECT ProductID, OrderID FROM OrderLines
      </DefiningQuery>
</EntitySet>

Notice we just select what we want, we ignore the rest of the columns, we can do this because we've said that the Association is going to be read only.

3) Now put a Many 2 Many Association in the CSDL:

<Association Name="ProductOrder">
    <End Role="Products" Type="OrdersDatabaseModel.Product" Multiplicity="*" />
    <End Role="Orders" Type="OrdersDatabaseModel.Order" Multiplicity="*" />
</Association>

4) An AssociationSet in the CSDL:

<AssociationSet Name="ProductOrders" Association="OrdersDatabaseModel.ProductOrder">
    <End Role="Products" EntitySet="Products" />
    <End Role="Orders" EntitySet="Orders" />
</AssociationSet>

5) A mapping from the Store EntitySet to the Conceptual Models AssociationSet:

<AssociationSetMapping Name="ProductOrders" TypeName="OrdersDatabaseModel.ProductOrder" StoreEntitySet="ProductOrders">
      <EndProperty Name="Products">
        <ScalarProperty Name="ID" ColumnName="ProductID" />
      </EndProperty>
      <EndProperty Name="Orders">
        <ScalarProperty Name="ID" ColumnName="OrderID" />
      </EndProperty>
</AssociationSetMapping>

6) Add a Products navigation property to Order:

<NavigationProperty Name="Products" Relationship="OrdersDatabaseModel.ProductOrder" FromRole="Orders" ToRole="Products"/>

7) And finally add an Orders navigation property to Product:

<NavigationProperty Name="Orders" Relationship="OrdersDatabaseModel.ProductOrder" FromRole="Products" ToRole="Orders"/>

And then we are done...

If you re-open the EDMX file using the default viewer you will now see something like this:

 

And you are ready to start coding!

Note: The only caveat is that Entity Framework doesn't understand the idea of a Read Only association today, so the API will allow you to try to add a Product to an Order's Products and visa versa, but it will fail when you try to do a SaveChanges().

AssociationWithPayloadDatabase.sql

Comments

• Anonymous
  February 25, 2008
  PingBack from http://msdnrss.thecoderblogs.com/2008/02/25/associations-with-payloads-closing-the-loop/
• Anonymous
  February 25, 2008
  Rajouter une "vue" dans le ssdl, il fallait y penser. Je pense que c'est vraiment un excellent exemple
• Anonymous
  February 28, 2008
  If you have a many to many relationship in your database, the Entity Data Model wizard will flatten the
• Anonymous
  September 29, 2008
  Your post just made my day. :)
• Anonymous
  November 16, 2010
  Very cool work around, thanks.  But I'll stick with treating the payload association as an entity until there's better support in the designer.  I prefer not to modify the .edmx by hand.  Isn't that what cavemen used to do?