how to flatten a model with NHibernate

Question

Say I have three tables Products, Languages and ProductTranslations that look like the following:

(1) ProductId
(1) StandardName
(1) Price

(2) LanguageId
(2) LanguageName

and

(3) ProductTranslationId
(3) ProductId
(3) LanguageId
(3) LocalName
(3) LocalDescription

In my object model, I need a single object (single class map) that contains the following

(3) ProductTranslationId
(2) Language
(3) LocalName
(3) LocalDescription
(1) Price

This object is only used for display, so read-only (immutable) is fine.

ProductTranslationId uniquely identifies a row.

If necessary, this object can include the LanguageID and ProductID values as well. the important feature is that this needs to include the Language and Price values from the ONE side of this one to many relationship.

I can see how to do this in 3 separate classes using Many-to-One, but how can I do this with one class, where ProductTranslationId is the <id> value?

Is there an alternative that does not require creating a view in the database?

Firo · Answer 1 · 2014-05-26T08:44:29.993

Option 1

// FluentNHibernate Mapping
public class ProductTranslationDtoMap : ClassMap<ProductTranslationDto>
{
    public ProductTranslationDtoMap()
    {
        ReadOnly();
        Table(" ProductTranslation");
        Id(x => x.Id, "ProductTranslationId");

        Map(x => x.Language).Formula("(SELECT l.LanguageName FROM Language l WHERE l.LanguageId = LanguageId)");  // simple and highly portable sql formula
        Map(x => x.LocalName);
        Map(x => x.LocalDescription);
        Map(x => x.Price).Formula("(SELECT p.Price FROM Products p WHERE p.ProductId =  ProductId)");
    }
}

Option 2

Product prod = null;
Language lang = null;
TranslationDto alias = null;
var results = session.QueryOver<ProductTranslation>()
    .JoinAlias(pt => pt.Language, () => lang)
    .JoinAlias(pt => pt.Product, () => prod)
    .SelectList(list => list
        .Select(pt => pt.Id).WithAlias(() => alias.ProductTranslationId)
        .Select(() => lang.Name).WithAlias(() => alias.Language)
        ...)
    .TransformUsing(Transformers.AliasToBean<TranslationDto>())
    .List<TranslationDto>();

Thanks Firo. Not sure I fully understand Option 1. Is that using HQL or SQL? I'm asking because I'm trying (hoping) to avoid a specific SQL dialect for portability. Option 2 makes sense (I think) but clarify for me. This is in code in my data access layer, not my mapping file. Also this requires 4 classes to support my example: Product, Language, TranslationDto, and ProductTranslation. Is that what you are proposing? Are you suggesting that there is no way to map these 3 tables to a single class? Also, what is Transformers.AliasToBean? Does it matter that I'm not using Java? — Kerry, Sep 19 '12 at 13:17
**1.** Formula is SQL **2.** i assumed you already had classes for the different tables then Option 2 is good **3.** Transformers.AliasToBean() inherited its name from Hibernate but is a NHibernate class which maps Alias names to Properties — Firo, Sep 19 '12 at 13:47

score 1 · Answer 2 · answered Sep 19 '12 at 10:29

1

Instead of using a NHibernate mapping you could also use a class mapper like AutoMapper to create a flat representation of your complex object graph. The advantage would be one nhibernate mapping less to maintain (although the fluent variant from Firo offers compiler syntax checking) in case your db schema changes. The disadvantage of course would be speed, as you first have to make nhibernate create your complex object graph, then transform that graph into a flat object. If you only need this one flat object, you may be better of with a single NH mapping. If you think about creating several more views, a class mapper might be worth a look.

answered Sep 19 '12 at 10:29

Dirk Trilsbeek

5,873
2
25
23

AutoMapper is great. I agree. This too requires that I have Product, Language and ProductTranslation classes in addition to the ProductDTO, and have (minimal) code to invoke AutoMapper to convert schemas. My question was really, is there a way in NHibernate to map the relationship between 3 tables into one class (entity)? Is this possible? – Kerry Sep 19 '12 at 13:54
if you don't have the individual classes already mapped, automapper would indeed require much more work than a single NH mapping. In that case you should try one of the options from Firos answer. – Dirk Trilsbeek Sep 19 '12 at 14:05

how to flatten a model with NHibernate

2 Answers2

Option 1