NHibernate Simple SELECT from multiple tables

Question

I got the following tables:

table X(CODE, FLAGGED, ENTRY_DATE)
table Y(ID, CODE)
table Z(ID, FIRST_NAME, LAST_NAME)

And the following classes:

public class Xclass
{
    public virtual string Code { get; set; }
    public virtual bool IsFlagged { get; set; }
    public virtual DateTime EntryDate { get; set; }
}
public class Yclass
{
    public virtual string Id { get; set; }
    public virtual string Code { get; set; }
}
public class Zclass
{
    public virtual string Id { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }
}

With the following mappings:

<class name="Xclass">
    <id name="Code" />
    <property name="IsFlagged" />
    <property name="EntryDate" />
</class>

<class name="Yclass">
    <id name="Id" />
    <property name="Code" />
</class>

<class name="Zclass">
    <id name="Id" />
    <property name="FirstName" />
    <property name="LastName" />
</class>

And I want to do the following simple query:

SELECT X.CODE, Z.FIRST_NAME, Z.LAST_NAME
FROM X, Y, Z
WHERE X.FLAGGED = '1'
AND X.CODE = Y.CODE
AND Y.ID = Z.ID;

I'm new to NHibernate, I read about QueryOver, JoinAlias/JoinQueryOver. But I can't seem to find anything that points me to the right direction. Any feedback will be appreciated.

Frédéric · Accepted Answer · 2017-05-22 10:08:42Z

An ORM is usually used for modeling relationships between your classes, and then use them. Here you do not have modeled any. But hql can still perform the query you want:

var query = session.CreateQuery(@"
select x.Code, z.FirstName, z.LastName
    from Xclass x, Yclass y, Zclass z
    where x.IsFlagged = :flagged
        and x.Code = y.Code
        and y.Id = Z.Id")
    .SetBoolean("flagged", true);
var results = query.ToList();

This will yield a list of object arrays. If you want to project them in a typed class, use a result transformer, by example:

var results = query
    .SetResultTransformer(
        Transformers.AliasToBean<ADtoWithPropertiesNamedAsQueryColumnAliases>())
    .List<ADtoWithPropertiesNamedAsQueryColumnAliases>();

A raw HQL query in code as above is validated only when trying to execute the query. You can have it validated earlier, when building NHibernate session factory, by using a named query. This usually ensures your queries are validated at application bootstrap, instead of having to test each feature using raw HQL queries in code. In case of errors in named queries, details of errors would be logged with log4net, if you have enabled it.

In your mappings under, add:

<hibernate-mapping>
  <query name="YourQueryName"><![CDATA[
    select x.Code, z.FirstName, z.LastName
        from Xclass x, Yclass y, Zclass z
        where x.IsFlagged = :flagged
            and x.Code = y.Code
            and y.Id = Z.Id
  ]]></query>
</hibernate-mapping>

And replace CreateQuery(@"...") by GetNamedQuery("YourQueryName").

For using other querying API, in most cases, you will have to map relationship between your tables. Here it looks like you have a one-to-one between Y and Z on their primary key. If this is actually the case, map it, and then you will be able to navigate the relationship with queryover or linq-to-nhibernate and select data from the relationship.

For the relationship on Code, you can map it too, provided Code is a unique key in some table, maybe X, maybe Y, or maybe another like W. On the table where Code is unique, preferably map it as a natural identifier. Then declare many-to-one relationships toward that table while specifying property-ref toward Code (and column too, unless name is Code too). Declare one-to-many relationships from that table, using property-ref on key for specifying the mapping is done on Code (default is the primary key otherwise).
Since your query could select Y.Code instead of X.Code, and that querying api allows to use subqueries in where condition, you may avoid mapping the relationship between X and Y and just filter Y with a subquery instead.

Thank you for the additional explanation. It's very helpful. I am working on a legacy back-end and I might need to tweak some relationships to improve the overall structure.
@janonimus, I have added some more explanations for a better use of HQL, if you need this.

Collectives™ on Stack Overflow

NHibernate Simple SELECT from multiple tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related