How to get an object from the database with NHibernate?

Question

I'm trying to get an object back from an NHibernate query.

My method is as follows:

public Site GetSiteByHost(string host)
{
    var result = _session.CreateCriteria<Site>()
        .Add(SqlExpression.Like<Site>(g => g.URLName, host));

    return result;
}

the problem is, result is a type of HNibernate.ICriteria.

How can I get this to return a Site object?

If I was doing this with LINQ to SQL it'd be something like .FirstOrDefault() but that's not available with NHibernate... or is it?!?!

Answer 1

You need to first execute the query (by calling List<T>() on the criteria) before calling FirstOrDefault. Notice that this query might return multiple objects:

IEnumerable<Site> sites = _session
    .CreateCriteria<Site>()
    .Add(SqlExpression.Like<Site>(g => g.URLName, host))
    .List<Site>();

And you could take the first one:

Site result = sites.FirstOrDefault();

or directly:

public Site GetSiteByHost(string host)
{
    return _session
        .CreateCriteria<Site>()
        .Add(SqlExpression.Like<Site>(g => g.URLName, host))
        .List<Site>()
        .FirstOrDefault();
}

Answer 2

I think you can put a .List<Site>() on the end, and then do the .FirstOrDefault() on it.

Answer 3

I believe .UniqueResult() is what you're after...

from the docs:

Convenience method to return a single instance that matches the query, or null if the query returns no results.

Answer 4

You can use linq with NHibernate. It is in the NHibernate.Linq namespace in the trunk of NHiberante.

return session.Query<Site>().FirstOrDefault(site => site.UrlName.Contains(host));

When you prefer the criteria API over Linq, you have to use result.SetMaxResults(1).UniqueResult() to create something equal to IQueryable.FirstOrDefault