Can I query a derived field in NHibernate?

Question

Is it possible to add a restriction to a derived field in an entity, ie. one that is not persisted? For example, if this is my entity:

public class Employee 
{
    public long Id { get; set; }
    public string Forename { get; set; }
    public string Surname {get; set; }
    public string FullName { get { return Forename + " " + Surname; }}
}

and this is the mapping:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
               namespace="Domain.Entities"
               assembly="Domain">
  <class name="Employee" table="`Employee`">
    <id name="Id" column="Id" type="long">
      <generator class="identity"/>
    </id>
    <property name="Forename"/>
    <property name="Surname"/>
  </class>
</hibernate-mapping>

And this is my query:

public Employee GetByFullName(string fullName)
{
    return _session
        .CreateCriteria<Employee>
        .Add(Restrictions.Eq("FullName", fullName))
        .List<Employee>();
}

Please ignore the fact that I could write the query myself, this is a trivial example to demonstrate. This would be useful in far more complex scenarios.

You could also do something like this: http://stackoverflow.com/questions/6709463/fuzzy-search-on-a-concatenated-full-name-using-nhibernate — Randy Burden, Oct 01 '12 at 18:10

score 4 · Accepted Answer · answered Oct 01 '12 at 15:03

4

It depends, basically if you can replicate the derived field with SQL then it can be done (so basically it can be done, but the practicality of it varies). In your example you would need to map your Fullname property like:

<property name="Fullname" formula="Forename + ' ' + Surname"/>

then to stop NHibernate from complaining you need to define an empty setter for Fullname:

public virtual string Fullname {
    get { return Forename + " " + Surname; }
    set { } // do nothing
}

Then you'll be able to query Fullname and NHibernate will insert the formula into the sql correctly.

answered Oct 01 '12 at 15:03

Martin Ernst

5,629
2
17
14

Would there need to be a field called 'Fullname' in the database table for this? – Paul T Davies Oct 01 '12 at 15:06
No, NHibernate will substitute your use of Fullname with the formula defined in the mapping when it issues SQL queries – Martin Ernst Oct 01 '12 at 15:09
My question was a simple example to illustrate more advance scaenarios, such as derived fields that traverse relationships and have complex algorithms. Although this would not be applicable to all circumstances, it did however answer the question in hand. – Paul T Davies Oct 02 '12 at 09:25
If your situation is complex you can define a SQL function to replicate it which can traverse relationships / joins and have complex algorithms. If you're using SQL Server, you can link to a .NET assembly to define even more complicated functions and call that function in the formula mapping of your property. – Martin Ernst Oct 02 '12 at 12:41

score 1 · Answer 2 · answered Oct 01 '12 at 13:52

1

No. Queries in Hibernate / NHibernate are always translated to SQL. If the field is not in the database, it can't be queried.

answered Oct 01 '12 at 13:52

Stefan Steinegger

63,782
15
129
193

Can I query a derived field in NHibernate?

2 Answers2