在查找中显示两个字段

Question

先介绍，最后提问。请仔细阅读！

---

I have a master-detail relation between two tables:

CREATE TABLE [dbo].[LookupAttributes] (
    [Id] int IDENTITY (1, 1) NOT NULL, 
    [Name] nvarchar (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ;
ALTER TABLE [dbo].[LookupAttributes] ADD CONSTRAINT [PK_LookupAttributes] PRIMARY KEY ([Identity]) ; 

CREATE TABLE [dbo].[Lookup] (
    [Id] int IDENTITY (1, 1) NOT NULL, 
    [LookupAttributesLink] int NOT NULL, 
    [Code] nvarchar (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    [Value] nvarchar (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ;
ALTER TABLE [dbo].[Lookup] ADD CONSTRAINT [IX_Lookup] UNIQUE ([LookupAttributenLink], [Code]) ; 

（两个表中都有更多字段和索引，但这些才是重要的......）

我正在从事的项目旨在维护 50 多个表中的数据，并且每周都会将这些数据导出到 XML，以供某些桌面应用程序用作源数据。虽然我想让它成为一个漂亮的应用程序，但它只需要快速完成，因此我使用动态数据站点，以便可以维护数据。它工作得很好，除了这张表......

事实证明，有 600 条不同的查找记录共享相同的代码，但具有不同的属性。 DDS 在查找记录列表中正确显示属性和代码，因此不会对某人正在编辑的查找记录产生任何混淆。而且这个已经使用了两年多了。

---

Now the problem: A new table "Lookup-Override" has been added which links to the [Id] field of the Lookup table. Each record in this new table thus displays the [Code] field, but since [Code] isn't unique, it's unclear which Override record belongs to which Lookup record.
To solve this, I need to display more information from the Lookup record. Since the only unique set of fields is the attribute plus code, I need to display both. But displaying [LookupAttributesLink]+[Code] isn't an option either, since [LookupAttributesLink] is just a number.
I need the DDS to display [Attributes].[LookupAttributesLink]+[Lookup].[Code] in a single column. Question is: how?
I've considered adding a calculated field to the Lookup table, but I cannot get the attribute name that way.
I could create a special page to maintain this table but I don't like that solution either, since it "breaks" the DDS principle in my opinion. I'm trying to avoid such pages.
So, any other possibilities to get the site display both attribute name and lookup code in the override table?

---

The most interesting solution would be by using a calculated field which could retrieve the attribute name. How to do that?

---

自己解决了！请参阅下面的答案，效果很好。

Answer 1

找到了！我必须做一些事情：

CREATE FUNCTION LookupName (
  @Attr int,
  @Code nvarchar(255)
) RETURNS nvarchar(1000)
AS
BEGIN
 DECLARE @Name nvarchar(1000)
 SELECT @Name = Name
 FROM [dbo].[LookupAttributes]
 WHERE [Id]=@Attr;
 RETURN @Name + '/' + @Code;
END
GO
ALTER TABLE [dbo].[lookup] ADD [Name] AS [dbo].[LookupName]([LookupAttributesLink], [Code])
GO

这将向表中添加一个额外的计算字段，该字段使用函数来计算正确的名称。然后，我必须为查找表添加一些元数据：

[MetadataType(typeof(LookupMetadata))]
public partial class Lookup { }
[DisplayColumn("Name", "Name")]
[DisplayName("Lookup")]
public class LookupMetadata
{
    [ScaffoldColumn(false)]
    public int Id;
    [ScaffoldColumn(false)]
    public object Name;
}

这将从查找表本身中隐藏“名称”列，但使其对覆盖表可见。 （它将用于显示正确的值。
这样做了，问题就解决了！实际上，很容易。 :-)