Relations with a view

I have two tables: Master and Detail. Both use a primary key called [Identity]. Detail also has a reference to Master called开发者_StackOverflow社区 [MasterID]. And unfortunately, both tables also have a few huge fields (TEXT, Binary, stuff like that) which I don't want to see in my website. So to improve performance, I created two views (ViewMaster and ViewDetail) which both just select a subset of both tables.

Now the problem: I use a Dynamic Data Site to just display the contents of these views. That works. But I want them to link to each other, so I could filter the detail records on their related master record. With tables, this is easy: just add a foreign key. But views don't have foreign keys!

So, what is the simplest way to automatically have the DDS add the links between these views? Or are there other options to display a Master-Detail overview with just DDS?

No, I don't use MVC. I know what it can do but these are just two of the 75+ tables that I need to work with. DDS allows me to generate overviews quickly for all tables, even allowing data entry on the most important ones.

Dynamic data site is build from Entity framework model. You can trick the site so it thinks that there is a relation between those views but it means you must add that relation manually to EDMX. It is pretty hard because you must:

• Create association in EDMX designer.
• Delete FK property from related entity in EDMX
• Open EDMX as XML and manually add relation to SSDL part = create association set and association
• Open back in EDMX and map association

Association in SSDL should look like:

<EntityContainer>
  ...
  <AssociationSet Name="MyDummyFK" Association="....MyDummyFK">
    <End Role="FirstView" EntitySet="FirstView" />
    <End Role="SecondView" EntitySet="SecondView" />
  </AssociationSet>
</EntityContainer>
<Association Name="MyDummyFK">
  <End Role="FirstView" Type="....FirstView" Multiplicity="0..1" />
  <End Role="SecondView" Type="....SecondView" Multiplicity="*" />
  <ReferentialConstraint>
    <Principal Role="FirstView">
      <PropertyRef Name="Id" />
    </Principal>
    <Dependent Role="SecondView">
      <PropertyRef Name="FirstViewId" />
    </Dependent>
  </ReferentialConstraint>
</Association>

Another approach is create temporary tables in DB with that relation and let EF generate everything for you. Then you will manully modify EDMX and rename everything to point ot your views instead of tables. After that you can remove tables.

But I think you can use your tables directly. Simply modify EDMX (SSDL must be again modified manually) so it does not include those TEXT and BINARY columns and your site will not query them.

Once you do any of these changes you can't update your model from database any more or it will delete your changes.