How can I select a column with a dictionary value using nhibernate?
I have a 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 get all the names of the product locales with the key "en". With linq it would be:
var names = Session.QueryOver<Product>().List().Select(x => x.Locales["en"].Name).ToList();
How can I achieve this using 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 happy with, I don't want sql in my code):
var names = Session.CreateSQLQuery("SELECT Name FROM ProductLocales WHERE Locale = 'en'").List<string>()
source to share
In these cases NHibernate has a very good solution: 18.1. NHibernate filters . At the end we will select Product
and apply a filter in the dictionary ... so having only SingleOrDefault()
an element in Locales
.
Define 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 now on, whenever you enable a filter in a session (even with some AOP filter), you can be sure to IDictionary
contain exactly one element (or none).
session.EnableFilter("CultureFilter")
.SetParameter("culture", "en");
// applied every time
var criteria = session.CreateCritieria...
var query = session.QueryOver....
There are similar posts with multiple links if needed fooobar.com/questions/2154872 / ...
EDIT: Constraining column "Locale" directly, getting a list of names
Another approach (supporting the current solution is almost the same) that can be used (and I know) is to extend the LocalEntity display
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);
}
);
}
}
With this, we can get a 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>();
We now have a list containing everything Names
from the EntityLocale, filtered by the "en" culture
source to share