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