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:


OrderLineDatabase


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:


OrderLineDatabaseWithProductOrder 


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 (5)

  1. Anonymous says:

    Rajouter une "vue" dans le ssdl, il fallait y penser. Je pense que c’est vraiment un excellent exemple

  2. Anonymous says:

    If you have a many to many relationship in your database, the Entity Data Model wizard will flatten the

  3. Tanveer Badar says:

    Your post just made my day. 🙂

  4. Anonymous says:

    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?