How to map a Dictionary<string, string> spanning several tables
- by Kim Johansson
I have four tables:
CREATE TABLE [Languages]
(
[Id] INTEGER IDENTITY(1,1) NOT NULL,
[Code] NVARCHAR(10) NOT NULL,
PRIMARY KEY ([Id]),
UNIQUE INDEX ([Code])
);
CREATE TABLE [Words]
(
[Id] INTEGER IDENTITY(1,1) NOT NULL,
PRIMARY KEY ([Id])
);
CREATE TABLE [WordTranslations]
(
[Id] INTEGER IDENTITY(1,1) NOT NULL,
[Value] NVARCHAR(100) NOT NULL,
[Word] INTEGER NOT NULL,
[Language] INTEGER NOT NULL,
PRIMARY KEY ([Id]),
FOREIGN KEY ([Word]) REFERENCES [Words] ([Id]),
FOREIGN KEY ([Language]) REFERENCES [Languages] ([Id])
);
CREATE TABLE [Categories]
(
[Id] INTEGER IDENTITY(1,1) NOT NULL,
[Word] INTEGER NOT NULL,
PRIMARY KEY ([Id]),
FOREIGN KEY ([Word]) REFERENCES [Words] ([Id])
);
So you get the name of a Category via the Word - WordTranslation - Language relations.
Like this:
SELECT TOP 1 wt.Value
FROM [Categories] AS c
LEFT JOIN [WordTranslations] AS wt ON c.Word = wt.Word
WHERE wt.Language = (
SELECT TOP 1 l.Id
FROM [Languages]
WHERE l.[Code] = N'en-US'
)
AND c.Id = 1;
That would return the en-US translation of the Category with Id = 1.
My question is how to map this using the following class:
public class Category
{
public virtual int Id { get; set; }
public virtual IDictionary<string, string> Translations { get; set; }
}
Getting the same as the SQL query above would be:
Category category = session.Get<Category>(1);
string name = category.Translations["en-US"];
And "name" would now contain the Category's name in en-US.
Category is mapped against the Categories table.
How would you do this and is it even possible?
