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.

Does `t.TranslatedDescription != null ? t.TranslatedDescription : t.Description` not work? Or `t.TranslatedDescription ?? t.Description` — abatishchev, Feb 27 '14 at 20:09
It might, but i really need it in one nhibernate criteria... — John, Feb 27 '14 at 20:15

score 3 · Accepted Answer · edited May 23 '17 at 12:22

3

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

edited May 23 '17 at 12:22

Community

1
1

answered Feb 28 '14 at 05:19

Radim Köhler

122,561
47
239
335

Thank You. What about if there's no a foreign key? Let's say those two tables have no connection as foreign key... is it possible to have something like that? – John Feb 28 '14 at 18:04
The JOIN is always defined by the mapping, it comes from relationship definition. We can restrict it more... but we cannot create own JOIN and ON clause. That's how the ORM tools work in common... Not the best answer I know... but ... ;) – Radim Köhler Feb 28 '14 at 18:08
:) ... because ... it is not implemented ;) That's all. Simply Creators of (N)Hibernate were looking for least common denominator of the SQL Functions arround the DB engines.... COALESCE was good enough to cover ISNULL ... Hope it helps ;) *(And it is my guess, have to say)* – Radim Köhler Mar 25 '15 at 13:50
1

Ha. It's really interesting they chose not to implement both. The following article is a good summary as to why you may want to use one over the other...http://sqlmag.com/t-sql/coalesce-vs-isnull – beauXjames Mar 25 '15 at 14:05

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

1 Answers1