How can I select a column within a dictionary value with nhibernate?

Question

I have structure similar to this:

public class Entity
{
    public int Id { get; set; }
    public IDictionary<string, EntityLocale> Locales { get; set; }
}
public class EntityLocale
{
    public string Name { get; set; }
}
public class EntityMap : ClassMap<Entity>
{
    public EntityMap()
    {
        HasMany(x => x.Locales)
                .AsMap<string>("Locale")
                .Component(
                c => {
                         c.Map(x => x.Name);
                     }
                );
    }
}

And I want to recieve all names of product locales with a "en" key. With linq it will be:

var names = Session.QueryOver<Product>().List().Select(x => x.Locales["en"].Name).ToList();

How do I achieve this with nhibernate? (I don't care if it's QueryOver or Criteria api, I just don't want to select everything).

Update

I came up with the following ugly hack (which I'm not satisfied with, I don't want any sql in my code):

var names = Session.CreateSQLQuery("SELECT Name FROM ProductLocales WHERE Locale = 'en'").List<string>()

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

For these cases, NHibernate has very nice solution: 18.1. NHibernate filters. At the end, we will select Product and apply filter on the dictionary... therefore having only SingleOrDefault() item in the Locales.

Definiton of the Filter

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

and apply it

HasMany(x => x.Locales)
  .AsMap<string>("Locale")
  ... 
  .ApplyFilter<CulturFilter>("Locale = :culture"))
;

From that moment, whenever you enable the filter in the session (even with some AOP filter), you can be sure that the IDictionary contains exactly one (or none) element..

session.EnableFilter("CultureFilter")
    .SetParameter("culture", "en");

// applied every time
var criteria = session.CreateCritieria...
var query = session.QueryOver....

There is similar posts with some more links if needed https://stackoverflow.com/a/17109300/1679310

EDIT: Restricting the column "Locale" directly, getting the list of Names

Other approach (keeping the current solution almost the same) which could be used (and I know about) is to extend the LocalEntity mapping

public class EntityLocale
{
    public virtual string CultureName { get; set; }
    public virtual string Name { get; set; }
}
public class EntityMap : ClassMap<Entity>
{
    public EntityMap()
    {
        HasMany(x => x.Locales)
            .AsMap<string>("Locale")
            .Component(
            c => {
                c.Map(x => x.CultureName).Formula("Locale").Not.Insert().Not.Update();
                c.Map(x => x.Name);
            }
        );
    }
}

Having this, we can get the list of all "en" Names like this:

var criteria = 
    session.QueryOver<Entity>()
    .JoinQueryOver<IDictionary<string, EntityLocale>>(c => c.Locales)
    .UnderlyingCriteria;

var list = criteria
    .Add(Restrictions.Eq("CultureName", "en"))
    .SetProjection(Projections.SqlProjection("Name"
        , new string[] { "name" }
        , new IType[] { NHibernateUtil.String }))
    .List()
    .Cast<string>()
    .ToList<String>();

Now we have a list containing all the Names from EntityLocale filtered by "en" culture

That's a great start. Can you add just a bit more information on how to access the dictionary (JoinQueryOver?) and how to select just 1 column ( **.Select(x => x.Locales["en"].Name).ToList();** in linq ) — Adam Tal, Jun 22 '13 at 14:32
I really rather not changing the dictionary to a list.. Are you sure there's no alternative? — Adam Tal, Jun 22 '13 at 14:44
Not sure... but... what we can do... is to extend the `EntityLocale` object anyway! so it can also have a property `local` (containg "en", "cs") marked as readonly (NHibernate will manage that column as a key of dictionary, and allow us to use it for select and where restricting ... another trick I like to use. — Radim Köhler, Jun 22 '13 at 14:45
Thanks for your help. Can you try and elaborate on how to map the new readonly property? and how do I query over it if the Locale object doesn't know the entity that contains him? (in the C# object, it has an EntityId column in the database). Maybe I should surrender and turn to hql? — Adam Tal, Jun 22 '13 at 14:53
I've updated my answer, with the answer you wanted... sorry for taking you into different dicrections (filter). I am using it and think, that it is amazing feature. But now you have what you needed ;) — Radim Köhler, Jun 22 '13 at 16:09

How can I select a column within a dictionary value with nhibernate?

1 Answers1

EDIT: Restricting the column "Locale" directly, getting the list of Names

Linked