Entities and Dynamic Data Site: references between tables.

Question

I have two tables:

CREATE TABLE [dbo].[Context] (
  [Identity] int IDENTITY (1, 1) NOT NULL, 
  [Naam] nvarchar (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
  [Code] nvarchar (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
  [Omschrijving] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ; 
ALTER TABLE [dbo].[Context] 
  ADD CONSTRAINT [PK_Context] 
      PRIMARY KEY ([Identity]) ; 
ALTER TABLE [dbo].[Context] 
  ADD CONSTRAINT [IX_Context_Naam] 
      UNIQUE ([Naam]) ; 
ALTER TABLE [dbo].[Context] 
  ADD CONSTRAINT [IX_Context_Code] 
      UNIQUE ([Code]) ; 
CREATE TABLE [dbo].[Component] (
  [Identity] int IDENTITY (1, 1) NOT NULL, 
  [ContextLink] int NOT NULL, 
  [Naam] nvarchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
  [Code] nvarchar (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
  [Omschrijving] ntext COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ; 
ALTER TABLE [dbo].[Component] 
  ADD CONSTRAINT [FK_ComponentContext] 
      FOREIGN KEY ([ContextLink]) 
        REFERENCES [dbo].[Context] ([Identity]) ;

(Above script should create both.) Basically, I have a Component table that refers to the Context table.

I created a Dynamic Data Site using both tables and .NET will take care of the references for me. It's a good way to quickly provide some basic website so we can continue to work on the business logic first.

However, when I look at the Component table in the DDS, I notice that the reference shows the code field of the context, not the Name field. So, how do I force the DDS to use the Name field instead when displaying the reference link to the context table?

(Preferably by using something simple, since I'm dealing with over 60 tables that are similar to this one. Most of them simple lookup tables to make filtering easier.)

Since we're not going to work on the GUI side of this web application for a few months, it's no option to alter something in the DDS source itself. If it can be fixed in the database or Entity model, then please let me know!

---

The datamodel I use for this project is simple: every table has a primary key "Identity" which is an autoIncrement field. The Code and Naam (name) fields are to describe specific data and is used in other applications to fill up comboboxes and filters. These applications don't communicate directly with the database but they use an export XML based on the database. In this export XML, the links to "Identity" get replaced by links to "Code". This allows the user to change the code to whatever they like without the need to walk through the whole database to adjust the references. The database has only one purpose: to make it easier for several users to maintain the XML data that another application uses. We have about 5 persons who make modifications to this data about 24/7 and an export XML is generated about once per week, which is then sent to our customers. (Who mostly use this data in an offline application, on laptops with limited internet connectivity.)

Answer 1

Consider revising your data model.

I would remove the name and code column from the Component table and add the Identity column from the context table, renaming it in both tables as ContextIdentity.

As your model is now. there are 2 valid links between the tables, this causes unnessessary use of storage and validaion checking.

I suspect that it is using the code field since this is the shortest, and therefore most efficient.

Answer 2

Found it! It's nasty, though, since it involved editing the entity model itself. First, right-click the entity model and select "Open with" to open the file using the XML Editor. Then, go to the tag and look below it to search for an node where the name is that of the child table. ("Component" in my case. It looks like this:

<EntityType Name="Component">
  <Key>
    <PropertyRef Name="Identity" />
  </Key>
  <Property Name="Identity" Type="Int32" Nullable="false" />
  <Property Name="Code" Type="String" Nullable="false" />
  <Property Name="Naam" Type="String" Nullable="false" />
  <Property Name="Omschrijving" Type="String" Nullable="true" />
  <NavigationProperty Name="Context" Relationship="Content_Model.FK_Component Context" FromRole="Component" ToRole="Context" />
</EntityType>

As you notice, it puts "Code" before "Name". Switch those two lines and you'll get this:

<EntityType Name="Component">
  <Key>
    <PropertyRef Name="Identity" />
  </Key>
  <Property Name="Identity" Type="Int32" Nullable="false" />
  <Property Name="Naam" Type="String" Nullable="false" />
  <Property Name="Code" Type="String" Nullable="false" />
  <Property Name="Omschrijving" Type="String" Nullable="true" />
  <NavigationProperty Name="Context" Relationship="Content_Model.FK_Component Context" FromRole="Component" ToRole="Context" />
</EntityType>

Because both "Code" and "Name" have an index, the DDS will just select the first one it discovers in this section. Since I've just put "Name" first, it now becomes the text in the reference.

Basically, a real easy fix, although it took some time to discover...