current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

Sorry for a lengthy question. But it is worth giving all the details so please bear with me through to the end.

I'm working against a legacy database over which I do not have much control. I want to be able to map a class to multiple database tables. Here is how my tables look

Lookup

+--------+--------------+------------+
| Column |   DataType   | Attributes |
+--------+--------------+------------+
| Id     | INT          | PK         |
| Code   | NVARCHAR(50) |            |
+--------+--------------+------------+

Culture

+--------------+--------------+------------+
|    Column    |   DataType   | Attributes |
+--------------+--------------+------------+
| Id           | INT          | PK         |
| Culture_Code | NVARCHAR(10) |            |
+--------------+--------------+------------+

Lookup_t9n

+----------------+---------------+---------------------+
|     Column     |   DataType    |     Attributes      |
+----------------+---------------+---------------------+
| Id             | INT           | PK                  |
| Culture_Id     | INT           | FK to Culture table |
| Localised_Text | NVARCHAR(MAX) |                     |
+----------------+---------------+---------------------+

As you can see, I have a lookup table where all lookups are stored. The display text for a lookup is localized and stored in a separate table. This table has a foreign key to culture table to indicate the culture for which the localized text exists.

My class looks like this

public class Lookup {

    public virtual int Id {get; set;}

    public virtual string Code {get; set;}

    public virtual string DisplayText {get; set;}
}

And my FNH mapping class looks like this

public class LookupMappings : ClassMap<Lookup> {

    public LookupMappings()
    {
        Table("Lookup");
        Id(x => x.Id).Column("Id");
        Map(x => x.Code).Column("Code");

        Join("Lookup_t9n", join => {
            join.Map(x => x.DisplayText).Column("Localised_Text"); //Note this place, my problem is here
        })
    }
}

In the above mapping, in Join part I want to provide some where clause like WHERE Lookup_t9n.Culture_Id = Culture.Culture_Id AND Culture.Culture_Code = System.Threading.Thread.CurrentUICulture.CultureCode.

I know this is not a valid SQL but conveys the intent I hope. Has anyone have any experience of doing such a thing.

I can add a mapping layer where I can have classes that map one-to-one with database tables and then write plain c# to map those classes back to my Lookup class. I have rather done that as an interim solution. I was wondering if I can remove that mapping layer with some smart NH use.

share|improve this question
up vote 1 down vote

I do not have simple answer, like CallThis(). I would like to give you suggestion, based on how we are using similar stuff. The solution is base on the standard mapping, hidding its complexity in C# Entities. It is just a draft of the solution so I'll skip the middle Culture table, and will expect that in Lookup_t9n we do store just a culture name (en, cs...)

Let's have this class

public class Lookup {
    public virtual int Id {get; set;}
    public virtual string Code {get; set;}
                                     // for simplicity skipping null checks    
    public virtual DisplayText { get { return Localizations.First().LocalizedText; } } 
    public virtual IList<Localization> Localizations {get; set;}
}

public class Localization { // mapped to Lookup_t9n
    public virtual string CultureName {get; set;}
    public virtual string LocalizedText {get; set;}
}

Having this, we can map the collection of Localizations as HasMany. It could even be mapped as a component (see example of component mapping)

Now, what we do need is to introduce a filter. Example with Fluent. The essential documentation: 18.1. NHibernate filters.

Simplified mapping

filter:

public class CulturFilter : FilterDefinition
{
  public CulturFilter()
  {
    WithName("CulturFilter")
        .AddParameter("culture",NHibernate.NHibernateUtil.String);
  }

collection:

HasMany(x => x.Localization)
    .KeyColumn("Id")
    ...
    .ApplyFilter<CulturFilter>("CultureName = :culture"))
    .Cascade.AllDeleteOrphan();

Finally, we have to introduce some AOP filter, IInterceptor... which will be triggered each time (needed) and adjust the ISession

session
   .EnableFilter("CulturFilter")
   .SetParameter("culture"
     ,System.Globalization.CultureInfo.CurrentCulture.TwoLetterISOLanguageName);

And now we have Localized string based on current culture, while using standard mapping of localized values as a collection.

share|improve this answer
    
I have gone over your solution twice already trying to get my head around it. I think I need to go through all the links that you have give and implement it myself first. – Suhas Jun 14 '13 at 17:51
    
I do agree. My sugestion is not as straightforward as you wished. But it is working at the end... – Radim Köhler Jun 14 '13 at 20:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.