Using the SQL "IsNull()" command in NHibernate criteria in C#

Question

I need to be able to use with NHibernate criteria, the SQL's IsNull() function in C#.NET. I don't need to use it with LINQ. Meaning that Table1 has the following columns:

Name | Description

Table2 has the following columns:

OriginalDescription | TranslatedDescription

And Table1.Description = Table2.OriginalDescription. How would I write the following SQL statement with NHibernate criteria:

SELECT Table1.Model, IsNull(Table2.TranslatedDescription, Table1.Description)
FROM Table1
LEFT JOIN Table2 ON Table2.OriginalDescription = Table1.Description

The SQL statement above will give me the Names, and TranslatedDescriptions if the TranslatedDescriptions exist, otherwise it will return the Descriptions, for the records. There cannot be duplicates of OriginalDescription in the Table2.

Answer 1

The solution of the ISNULL could be expressed like this:

// here is the criteria of the "Entity1" and the join to the "Entity2"
var criteria = session.CreateCriteria("Entity1", "table1");
criteria.CreateAlias("Entity2", "table2");

// here we drive the SELECT clause
criteria.SetProjection(
    Projections.ProjectionList()
        .Add(Projections.Property("Model"))
        .Add(Projections.SqlFunction("COALESCE", NHibernateUtil.String
            , Projections.Property("table2.TranslatedDescription")
            , Projections.Property("table1.Description")
            ))
    );

// just a list of object arrays
var list = criteria.List<object[]>();

So, what we do here, is a call of the SqlFunction. In this case one of the out-of-the-box mapped in many Dialects coming with NHibernate (but we can even extend the dialect with custom ones, an example how to: Nhibernate count distinct (based on multiple columns))

Must note, that the JOIN Clause is coming from the mapping. So this Table2.OriginalDescription = Table1.Description must come from a mapped relation many-to-one