Why does fluent nHibernate throw a NotSupportedException?

Question

I use this query to search for a name in a SQL Server database via fluent nHibernate:

var content = (from person in DBSession.Query<Person>() 
               where
               (PersonenBezeichnungsGenerator.BuildName(person.FirstName, person.FamilyName).ToUpper() == contaktperson.ToUpper()) //This throws a NotSupportedException
               //((person.FirstName+ " " + person.FamilyName).ToUpper() == contaktperson.ToUpper()) //This works
               select person).ToList();

This is the function BuildName:

public static string BuildName(string firstName, string familyName)
{
    return familyName + " " + firstName;
}

When I run this nHibernate throws a NotSupportedException. Why is this?

System.NotSupportedException HResult=0x80131515 Message=System.String BuildName(System.String, System.String) Source=NHibernate

Is there any way to use functions like this in a fluent nHibernate query?

Answer 1

The NotSupportedException is thrown because the LINQ provider for NHibernate does not know how to translate your custom function PersonenBezeichnungsGenerator.BuildName() into SQL.

When using LINQ to query with NHibernate, the provider inspects the LINQ expression tree at runtime and tries to translate it into a regular SQL expression.

In your case, you could probably do this:

var allPersons = DBSession.Query<Person>().ToList();
var content = allPersons
                .Where(person => PersonenBezeichnungsGenerator.BuildName(person.FirstName, person.FamilyName).ToUpper() == contaktperson.ToUpper())
                .ToList();

Be aware that this approach fetches all the Person records from the database before filtering, which may not be the best solution large datasets.