NHibernate各种查询



NHibernate各种查询

NHibernate's methods of querying are powerful, but there's a learning curve. Longer term, Linq is the way to go, although it may not be as capable as Linq2Sql or Entity Framework (Hql and Criteria are arguably superior for advanced scenarios anyway).

• Get by primary key is built-in (session.Get and session.Load; latter loads a proxy which only does the database select when needed).
• HQL - a provider-neutral Sql-like language. Unfortunately it's just a big string. It's good for ststic queries and has the most advanced capabilities.
• Criteria - more object like and good for building dynamic runtime queries. Property names are still strings.
• QueryOver - (NHibernate 3+) uses lambdas over Criteria to make it strongly typed.
• Linq -
  • In NHibernate 2 and 2.1, this is a NHibernate Contrib extension (as ISession.Linq<T>). You need to get the source and rebuild against the latest NHibernate 2.1 version. It works well for simple queries.
  • From NHibernate 3.0, Linq is part of the NHibernate core (as ISession.Query<T>) and gives more advanced features, although not everything may be there.

Lists with restrictions

//directly get by id (see also Load<> - loads proxy)
var category = session.Get<Category>(2);
 
//hql
var hqlQuery = session.CreateQuery("from Product p where p.Category.Id = ? order by p.Id")
    //set the parameter
    .SetInt32(0, 2)
    //second page of 10
    .SetFirstResult(10).SetMaxResults(10);
 
var list = hqlQuery.List<Product>();
 
//criteria
var criteria = session.CreateCriteria<Product>()
    //"Restrictions" used to be "Expression"
    .Add(Restrictions.Eq("Category.Id", 2))
    //ordering
    .AddOrder(NHibernate.Criterion.Order.Asc("Id"))
    //paging, 2nd page of 10
    .SetFirstResult(10) //zero based
    .SetMaxResults(10);
 
var list2 = criteria.List<Product>();
 
//query over
var queryOver = session.QueryOver<Product>()
    .Where(x => x.Category.Id == 2)
    //simple restrictions- And or &&/||
    //.And(x => !x.Discontinued)
    //.And(x => !x.Discontinued && x.UnitsInStock > 0)
    .OrderBy(x => x.Id).Asc
    .Skip(10)
    .Take(10);
var list3 = queryOver.List();
 
 
//using NHibernate.Linq (session.Linq in NH 2/session.Query in NH3)
var linq = (from product in session.Query<Product>()
            where product.Category.Id == 2
            orderby product.Id
            select product)
    .Skip(10)
    .Take(10);
var list4 = linq.ToList();

Single Results

//HQL counts
var hqlCountQuery = session.CreateQuery("select count(*) from Product p where p.Category.Id = ?")
            .SetInt32(0, 2);
var count1 = hqlCountQuery.UniqueResult<long>(); //always a long
 
//criteria counts
var countCriteria = session.CreateCriteria<Product>()
    //add rowcount projection - NB: RowCountInt64 for long
        .SetProjection(Projections.RowCount())
        .Add(Restrictions.Eq("Category.Id", 2));
var count2 = countCriteria.UniqueResult<int>();
 
//queryOver counts
var count3 = session.QueryOver<Product>()
    .Where(x => x.Category.Id == 2)
    .RowCount();
 
//linq counts
var count4 = session.Query<Product>().Count(p => p.Category.Id == 2);

MultiQuery

You can batch multiple queries in a single call. The Hql equivalent would use Multiquery; Criteria uses MultiCriteria.

//you can also use separate ICriterias with .Future / .FutureValue
IMultiCriteria multiCriteria = s.CreateMultiCriteria()
    .Add<Product>(
        s.CreateCriteria<Product>()
        //"Restrictions" used to be "Expression"
        .Add(Restrictions.Eq("Category.Id", 2))
        //ordering
        .AddOrder(NHibernate.Criterion.Order.Asc("Id"))
        //paging, 2nd page of 10
        .SetFirstResult(10) //zero based
        .SetMaxResults(10)
        )
    .Add(
        s.CreateCriteria<Product>()
        //add rowcount projection - NB: RowCountInt64 for long
        .SetProjection(Projections.RowCount())
        .Add(Restrictions.Eq("Category.Id", 2))
        )
    ;
var criteriaResults = multiCriteria.List();
IList<Product> products = (IList<Product>)criteriaResults[0];
int criteriaCount = (int)((IList)criteriaResults[1])[0];

Joins

In criteria, use a nested criteria or alias.

//no join, it knows Id is on Product
var list1 = session.CreateCriteria<Product>()
    .Add(Restrictions.Eq("Discontinued", false))
    .Add(Restrictions.Eq("Category.Id", 2))
    .List<Product>();
 
//for any other category properties create nested criteria
var list2 = session.CreateCriteria<Product>()
    .Add(Restrictions.Eq("Discontinued", false))
    .CreateCriteria("Category")
        .Add(Restrictions.Eq("CategoryName", "Condiments"))
    .List<Product>();
 
//use alias to flatten
var list4 = session.CreateCriteria<Product>()
    .Add(Restrictions.Eq("Discontinued", false))
    .CreateAlias("Category", "c")
    .Add(Restrictions.Eq("c.CategoryName", "Condiments"))
    .List<Product>();
 
//queryOver with join
var qover = session.QueryOver<Product>()
    .Where(x => !x.Discontinued)
    .JoinQueryOver(x => x.Category)
    .Where(c => c.CategoryName == "Condiments")
    .List();
 
//queryOver with join and aliases
Product productAlias = null; //you need null objects
Category categoryAlias = null;
var qoAlias = session.QueryOver(() => productAlias)
    .JoinQueryOver(x => x.Category, () => categoryAlias)
    //you can use the simple 
    .Where(() => categoryAlias.CategoryName == "Condiments")
    .And(() => !productAlias.Discontinued)
    .List();
 
//linq
var linq = (from product in session.Query<Product>()
            join category in session.Query<Category>()
                 on product.Category.Id equals category.Id
            where category.CategoryName == "Condiments"
            && !product.Discontinued
            select product).ToList();

Subqueries

//with HQL
var hqlList = session.CreateQuery(
    @"from Product p where p.Id in 
        (select n.Id from Product n
         where (n.UnitsInStock = :units
         or n.Discontinued = :dis))
         and p.Category.Id = :cat")
    .SetInt16("units", (short)0)
    .SetBoolean("dis", true)
    .SetInt32("cat", 2)
    .List<Product>();
 
//with Criteria and DetachedCriteria
var notForSale = DetachedCriteria.For<Product>("noSale")
    //for subquery you must project
    .SetProjection(Projections.Property("noSale.id"))
    .Add(Restrictions.Disjunction()
        .Add(Restrictions.Eq("noSale.UnitsInStock", (short)0))
        .Add(Restrictions.Eq("noSale.Discontinued", true)))
    .Add(Restrictions.Eq("Category.Id", 2));
 
var criteriaList = session.CreateCriteria<Product>()
    //the id must be in our subquery select
    .Add(Subqueries.PropertyIn("Id",notForSale))
    .List<Product>();
 
//with QueryOver (NH3)
var detachedQueryOver = QueryOver.Of<Product>()
    //you can .And or use simple expressions with && and ||
    .Where(x => x.UnitsInStock == 0 || x.Discontinued)
    .And(x=> x.Category.Id == 2)
    .Select(x => x.Id) //simple projection
    ;
var queryOverList = session.QueryOver<Product>()
    .WithSubquery
    .WhereProperty(x => x.Id)
    .In(detachedQueryOver)
    .List();
 
//NH Linq doesn't support subqueries :(
var linq = from product in session.Query<Product>()
           where 
            (product.Discontinued 
            || product.UnitsInStock == 0)
           && product.Category.Id == 2
           select product;

Projecting to a DTO

See above for projections for aggregations such as counts. The generated SQL only selects the required columns- not everything. The Transformers.AliasToBean is an ugly relic of the port from Java.

var proj = Projections.ProjectionList()
    //projected mapped class property to alias of dto property
    .Add(Projections.Property("ProductName"), "ProductName")
    .Add(Projections.Property("c.CategoryName"), "CategoryName")
    .Add(Projections.Property("UnitsInStock"), "Units");
 
var result = session.CreateCriteria<Product>("p")
    .Add(Restrictions.Gt("UnitPrice", 10m))
    .CreateAlias("Category", "c")
    .Add(Restrictions.Eq("Category.Id", 2))
    .SetProjection(proj)
    .SetResultTransformer(
        NHibernate.Transform.Transformers.AliasToBean(typeof(Northwind.Dto.ProductLite)))
    .List<Northwind.Dto.ProductLite>();

In Hql, you can either have fun with object arrays, or you have to use a mappings import on the DTO.

IList results = session.CreateQuery(
     @"select p.ProductName, c.CategoryName, p.UnitsInStock
      from Product p join p.Category c
      where p.UnitPrice > 10 and c.Id = 2").List();
 
foreach (object[] row in results)
{
    string name = (string)row[0];
    string category = (string)row[1];
    var units = (short)row[2];
    var dto = new Northwind.Dto.ProductLite(name, category, units);
}
 
//need to import it
//<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
//  <import class="Northwind.Dto.ProductLite, Northwind" />
//</hibernate-mapping>
var results2 = session.CreateQuery(
     @"select new ProductLite(p.ProductName, c.CategoryName, p.UnitsInStock)
      from Product p join p.Category c
      where p.UnitPrice > 10 and c.Id = 2")
     .List<Northwind.Dto.ProductLite>();

Lately I have been exploring NHibernate and ICriteria. NHibernate is great I would recommend it to anyone but often I find the documentation and examples are hard to follow.

I found an interesting article by Max Andersen on Criteria Transformers for Hibernate but of course it applies to NHibernate as well. I tried it out and it worked well.

It’s really easy with NHibernate to select a simple typed list of results

For example:

public static IList List()

{

ICriteria criteria = Persistence.Session.CreateCriteria(typeof(Profile));

return criteria.List();

}

But how about when your HQL or ICriteria selects across multiple classes or entities. For example you may have an class Profile that has a many to one relationship with an class Department.

Your SQL would be like:

SELECT p.ItemID, p.Name, p.Summary, d.ItemID, d.Name
FROM Profile p inner join Department d on p.DepartmentID = d.ItemID

First we need to create a class to hold the result of our query. Below I have created a DTO or Data Transfer Object which combines the properties from the Profile and Department classes that I require.

public class ProfileDTO

{

private int _itemID;

private int _departmentID;

private string _departmentName;

private string _name;

private string _summary;

public virtual int ItemID

{

get { return _itemID; }

set { _itemID = value; }

}

public virtual int DepartmentID

{

get { return _departmentID; }

set { _departmentID = value; }

}

public string DepartmentName

{

get { return _departmentName; }

set { _departmentName = value; }

}

public string Name

{

get { return _name; }

set { _name = value; }

}

public string Summary

{

get { return _summary; }

set { _summary = value; }

}

}

From here we can create a query that returns a Generic list of ProfileDTO.

public static IList List()

{

ICriteria criteriaSelect = Persistence.Session.CreateCriteria(typeof(Profile));

criteriaSelect.CreateAlias("Department", "d");

criteriaSelect.SetProjection(

Projections.ProjectionList()

.Add(Projections.Property("ItemID"), "ItemID")

.Add(Projections.Property("Name"), "Name")

.Add(Projections.Property("Summary"), "Summary")

.Add(Projections.Property("d.ItemID"), "DepartmentID")

.Add(Projections.Property("d.Name"), "DepartmentName"));

criteriaSelect.AddOrder(Order.Asc("Name"));

criteriaSelect.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(ProfileDTO)));

return criteriaSelect.List();

}

NHibernate SetResultTransformer combined with the generic list make returning a typed list from a complicated query really easy.