How to map a Dictionary<string, string> spanning several tables

Question

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?

Answer 1

If you are using LINQ you should be able to get away with something like this:

public Category GetCategory(int categoryId)
{ 
    var translations = from c in db.Categories
                       join wt in db.WordTranslations on c.Word equals wt.Word
                       join l in db.Languages on l.Id equals wt.Language
                       where c.Id == categoryId
                       select new { Key=l.Code, Value=wt.Word };
    return new Category { Id=categoryId, Translations=translations.ToDictionary() };
}

I didn't compile check this so you may need to work a bit with the syntax to get the right result, but conceptually it should get you what you need.

Answer 2

Have you looked at using SetResultTransformer. NHibernate provides this function for mapping result sets to classes or other structures. Using a transfrom of AliasToEntityMap translates each row to a dictionary of properties - which may be what your looking for.

See section 13.4 of this