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

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?

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.

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