(N)Hibernate find content from schema: Object <- AttributeValue -> Attribute

Question

We have a common db schema of:

Object <- AttributeValue -> Attribute

lets say we talk about houses. The house can have lots and lots of attributes (door shape, roof color etc.). The number of attributes for concrete house is not known.

Straight forward mapping to objects is used:

• House - contains collection of HouseAttributeValue objects,
• HouseAttributeValue - contains Attribute object and its string value
• HouseAttribute - contains attribute name

Now simple get works as expected:

ICriteria criteria = this.Repository.CreateCriteria(typeof(House))
IList<House> searchResult = this.Repository.GetList<House>();

House with collection of attribute values (with attribute object) is returned.

We need to find specific house(s) (with big windows and glass doors) and get all of it's attributes.

select statement that I imagine (do not mind performance) is no magic:

SELECT 
  this_.*, 
  att_val_fetch.*, 
  att_fetch.*
FROM House this_

INNER JOIN attribute_value att_val_fetch ON this_.versionedobjectid = att_val_fetch.versionedobjectid
INNER JOIN attribute att_fetch ON att_val_fetch.attributeid = att_fetch.attributeid

-- only for filter, no need to fetch
INNER JOIN attribute_value att_val_1 ON this_.versionedobjectid = att_val_1.versionedobjectid
INNER JOIN attribute att_1 ON att_val_1.attributeid = att_1.attributeid

INNER JOIN attribute_value att_val_2 ON this_.versionedobjectid = att_val_2.versionedobjectid
INNER JOIN attribute att_2 ON att_val_2.attributeid = att_2.attributeid

WHERE((att_1.attributename = 'window' AND att_val_1.valuestring IN('big')) 
 and (att_2.attributename = 'door' AND att_val_2.valuestring IN('glass')));

How can this be done using NHibernate (or Hibernate)? Would love to see solution without direct SQL statement in code, but actually any solution is welcomed.

Answer 1

Maybe an HQL query by example (QBE) is what you need.

Answer 2

assuming this simple definition

class House
{
    public int Id{get;set;}
    .
    .
    .
    public IList<AttributeValue> AttributeValues {get;set;} 
}

class AttributeName
{
    public int Id{get;set;}
    .
    .
    .
    public string Name{get;set;} 
}

class AttributeValue
{
    public int Id{get;set;}
    .
    .
    .
    public House House {get;set;}
    public AttributeName Attribute{get;set;}
    public string Value {get;set;}
}

and appropriate corresponding mappings, this criteria should do the job

ICriteria criteria = this.Repository.CreateCriteria(typeof(House))
criteria.CreateAlias("AttributeValues", "av");
criteria.SetFetchMode("av", FetchMode.Select);
criteria.Add(Expression.Eq("av.Attribute.Name", "door") && Expression.Eq("av.Value", "glass"));
criteria.Add(Expression.Eq("av.Attribute.Name", "window") && Expression.Eq("av.Value", "big"));
IList<House> houses = criteria.List<House>();

of which i don't remember now when setting the fetch mode if using the aliased collection is proper or the full property name is required like

criteria.SetFetchMode("AttributeValues", FetchMode.Select);