The most basic SQL query is to get a list of scalars (values).

sess.CreateSQLQuery("SELECT * FROM CATS")
 .AddScalar("ID", NHibernateUtil.Int64)
 .AddScalar("NAME", NHibernateUtil.String)
 .AddScalar("BIRTHDATE", NHibernateUtil.Date)

This query specified:

This will return an IList of Object arrays (object[]) with scalar values for each column in the CATS table. Only these three columns will be returned, even though the query is using * and could return more than the three listed columns.

The above query was about returning scalar values, basically returning the "raw" values from the result set. The following shows how to get entity objects from a native SQL query via AddEntity().

sess.CreateSQLQuery("SELECT * FROM CATS").AddEntity(typeof(Cat));
sess.CreateSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").AddEntity(typeof(Cat));

This query specified:

Assuming that Cat is mapped as a class with the columns ID, NAME and BIRTHDATE the above queries will both return an IList where each element is a Cat entity.

If the entity is mapped with a many-to-one to another entity it is required to also return its identifier when performing the native query, otherwise a database specific "column not found" error will occur. The additional columns will automatically be returned when using the * notation, but we prefer to be explicit as in the following example for a many-to-one to a Dog:

sess.CreateSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS")
    .AddEntity(typeof(Cat));

This will allow cat.Dog property access to function properly.

It is possible to eagerly join in the Dog to avoid the possible extra round-trip for initializing the proxy. This is done via the AddJoin() method, which allows you to join in an association or collection.

sess
    .CreateSQLQuery(
        "SELECT cat.ID, NAME, BIRTHDATE, DOG_ID, D_ID, D_NAME " +
        "FROM CATS cat, DOGS d WHERE cat.DOG_ID = d.D_ID")
    .AddEntity("cat", typeof(Cat))
    .AddJoin("cat.Dog");

In this example the returned Cat's will have their Dog property fully initialized without any extra round-trip to the database. Notice that we added a alias name ("cat") to be able to specify the target property path of the join. It is possible to do the same eager joining for collections, e.g. if the Cat had a one-to-many to Dog instead.

sess
    .CreateSQLQuery(
        "SELECT ID, NAME, BIRTHDATE, D_ID, D_NAME, CAT_ID " +
        "FROM CATS cat, DOGS d WHERE cat.ID = d.CAT_ID")
    .AddEntity("cat", typeof(Cat))
    .AddJoin("cat.Dogs");

At this stage we are reaching the limits of what is possible with native queries without starting to enhance the SQL queries to make them usable in NHibernate; the problems start to arise when returning multiple entities of the same type or when the default alias/column names are not enough.

Until now the result set column names are assumed to be the same as the column names specified in the mapping document. This can be problematic for SQL queries which join multiple tables, since the same column names may appear in more than one table.

Column alias injection is needed in the following query (which most likely will fail):

sess
    .CreateSQLQuery(
        "SELECT cat.*, mother.* " +
        "FROM CATS cat, CATS mother WHERE cat.MOTHER_ID = mother.ID")
    .AddEntity("cat", typeof(Cat))
    .AddEntity("mother", typeof(Cat))

The intention for this query is to return two Cat instances per row, a cat and its mother. This will fail since there is a conflict of names since they are mapped to the same column names and on some databases the returned column aliases will most likely be on the form "c.ID", "c.NAME", etc. which are not equal to the columns specified in the mappings ("ID" and "NAME").

The following form is not vulnerable to column name duplication:

sess
    .CreateSQLQuery(
        "SELECT {cat.*}, {mother.*} " +
        "FROM CATS cat, CATS mother WHERE cat.MOTHER_ID = mother.ID")
    .AddEntity("cat", typeof(Cat))
    .AddEntity("mother", typeof(Cat))

This query specified:

The {cat.*} and {mother.*} notation used above is a shorthand for "all properties". Alternatively, you may list the columns explicitly, but even in this case we let NHibernate inject the SQL column aliases for each property. The placeholder for a column alias is just the property name qualified by the table alias. In the following example, we retrieve Cats and their mothers from a different table (cat_log) to the one declared in the mapping metadata. Notice that we may even use the property aliases in the where clause if we like.

String sql = "SELECT c.ID as {c.Id}, c.NAME as {c.Name}, " + 
         "c.BIRTHDATE as {c.BirthDate}, c.MOTHER_ID as {c.Mother}, {mother.*} " +
         "FROM CAT_LOG c, CAT_LOG m WHERE {c.Mother} = m.ID";

var loggedCats = sess.CreateSQLQuery(sql)
    .AddEntity("c", typeof(Cat))
    .AddEntity("m", typeof(Cat)).List<object[]>();

It is possible to apply an IResultTransformer to native sql queries. Allowing it to e.g. return non-managed entities.

sess.CreateSQLQuery("SELECT NAME, BIRTHDATE FROM CATS")
        .SetResultTransformer(Transformers.AliasToBean(typeof(CatDTO)))

This query specified:

The above query will return a list of CatDTO which has been instantiated and injected the values of NAME and BIRTHNAME into its corresponding properties or fields.

IMPORTANT: The custom IResultTransformer should override Equals and GetHashCode, otherwise the query translation won't be cached. This also will result in memory leak.

Native SQL queries which query for entities that are mapped as part of an inheritance hierarchy must include all properties for the base class and all its subclasses.

Native SQL queries support positional as well as named parameters:

var query = sess
    .CreateSQLQuery("SELECT * FROM CATS WHERE NAME like ?")
    .AddEntity(typeof(Cat));
var pusList = query.SetString(0, "Pus%").List<Cat>();

query = sess
    .createSQLQuery("SELECT * FROM CATS WHERE NAME like :name")
    .AddEntity(typeof(Cat));
var pusList = query.SetString("name", "Pus%").List<Cat>();

With <return-property> you can explicitly tell NHibernate what column aliases to use, instead of using the {}-syntax to let NHibernate inject its own aliases.

<sql-query name="mySqlQuery">
    <return alias="person" class="eg.Person">
        <return-property name="Name" column="myName"/>
        <return-property name="Age" column="myAge"/>
        <return-property name="Sex" column="mySex"/>
    </return>
    SELECT person.NAME AS myName,
           person.AGE AS myAge,
           person.SEX AS mySex,
    FROM PERSON person WHERE person.NAME LIKE :name
</sql-query>

<return-property> also works with multiple columns. This solves a limitation with the {}-syntax which can not allow fine grained control of multi-column properties.

<sql-query name="organizationCurrentEmployments">
    <return alias="emp" class="Employment">
        <return-property name="Salary">
            <return-column name="VALUE"/>
            <return-column name="CURRENCY"/>
        </return-property>
        <return-property name="EndDate" column="myEndDate"/>
    </return>
        SELECT EMPLOYEE AS {emp.Employee}, EMPLOYER AS {emp.Employer},
        STARTDATE AS {emp.StartDate}, ENDDATE AS {emp.EndDate},
        REGIONCODE as {emp.RegionCode}, EID AS {emp.Id}, VALUE, CURRENCY
        FROM EMPLOYMENT
        WHERE EMPLOYER = :id AND ENDDATE IS NULL
        ORDER BY STARTDATE ASC
</sql-query>

Notice that in this example we used <return-property> in combination with the {}-syntax for injection, allowing users to choose how they want to refer column and properties.

If your mapping has a discriminator you must use <return-discriminator> to specify the discriminator column.

NHibernate introduces support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset to be able to work with NHibernate. An example of such a stored function in MS SQL Server 2000 and higher is as follows:

CREATE PROCEDURE selectAllEmployments AS
    SELECT EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE,
    REGIONCODE, EMPID, VALUE, CURRENCY
    FROM EMPLOYMENT

To use this query in NHibernate you need to map it via a named query.

<sql-query name="selectAllEmployments_SP">
    <return alias="emp" class="Employment">
        <return-property name="employee" column="EMPLOYEE"/>
        <return-property name="employer" column="EMPLOYER"/>
        <return-property name="startDate" column="STARTDATE"/>
        <return-property name="endDate" column="ENDDATE"/>
        <return-property name="regionCode" column="REGIONCODE"/>
        <return-property name="id" column="EID"/>
        <return-property name="salary">
            <return-column name="VALUE"/>
            <return-column name="CURRENCY"/>
        </return-property>
    </return>
    exec selectAllEmployments
</sql-query>

Notice that stored procedures currently only return scalars and entities. <return-join> and <load-collection> are not supported.