NHibernate - CreateCriteria vs CreateAlias

Question

Assuming the following scenario:

class Project{
   public Job Job;
}

class Job{
   public Name;
}

Assuming I want to use the Criteria API to search for all projects whose Job has the name "sumthing".

I could use the CreateAlias to create an alias for Job and use it to access Name, or I could create a new Criteria for the property Job and search by Name.

Performance wise, is there any difference?

Nifty

Answer 1

given these requirements there would be no difference, the generated SQL is the same: for mappings:

    <?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class name="Project" table="Project">
        <id name="Id" type="Int32" unsaved-value="0">
            <column name="Id" sql-type="int" not-null="true" unique="true"/>
            <generator class="native" />
        </id>
        <many-to-one name="Job" column="FK_JobId" cascade="save-update" not-null="true" />
    </class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class name="Job" table="Job">
        <id name="Id" type="Int32" unsaved-value="0">
            <column name="Id" sql-type="int" not-null="true" unique="true"/>
            <generator class="native" />
        </id>
        <property name="Name" type="String">
            <column name="Name" sql-type="nvarchar" length="50" not-null="true"/>
        </property>
    </class>
</hibernate-mapping>

and classes

public class Project
    {
        public Project() { }

        public virtual int Id { get; set; }

        public virtual Job Job { get; set; }
    }
public class Job
    {
        public Job() { }

        public virtual int Id { get; set; }

        public virtual String Name { get; set; }
    }

these criteria definitions

ICriteria criteriacrit = session
  .CreateCriteria(typeof (Project))
  .CreateCriteria("Job", "job")
  .Add(Restrictions.Eq("job.Name", "sometextA"));

ICriteria aliascrit = session
  .CreateCriteria(typeof (Project))
  .CreateAlias("Job", "job")
  .Add(Restrictions.Eq("job.Name", "sometextB"));

generate the same SQL

SELECT 
  this_.Id as Id2_1_, 
  this_.FK_JobId as FK2_2_1_, 
  job1_.Id as Id1_0_, 
  job1_.Name as Name1_0_ 
FROM 
  Project this_ 
  inner join Job job1_ 
    on this_.FK_JobId=job1_.Id 
WHERE job1_.Name = @p0; @p0 = 'sometextA'

SELECT 
  this_.Id as Id2_1_, 
  this_.FK_JobId as FK2_2_1_, 
  job1_.Id as Id1_0_, 
  job1_.Name as Name1_0_ 
FROM
  Project this_ 
  inner join Job job1_ 
    on this_.FK_JobId=job1_.Id 
WHERE job1_.Name = @p0; @p0 = 'sometextB'

note however that the CreateAlias relies on the mappings to generate associations whereas the CreateCriteria call allows to specify JoinType.

so, these calls

ICriteria criteriacrit = session
  .CreateCriteria(typeof(Project))
  .CreateCriteria("Job",JoinType.LeftOuterJoin)
  .Add(Restrictions.Eq("Name", "sometextA"));

ICriteria aliascrit = session
  .CreateCriteria(typeof (Project))
  .CreateAlias("Job", "job")
  .Add(Restrictions.Eq("job.Name", "sometextB"));

generate these SQL statements

SELECT 
  this_.Id as Id2_1_, 
  this_.FK_JobId as FK2_2_1_, 
  job1_.Id as Id1_0_, 
  job1_.Name as Name1_0_ 
FROM 
  Project this_ 
  **left outer** join Job job1_
    on this_.FK_JobId=job1_.Id 
WHERE job1_.Name = @p0; @p0 = 'sometextA'

SELECT 
  this_.Id as Id2_1_, 
  this_.FK_JobId as FK2_2_1_, 
  job1_.Id as Id1_0_, 
  job1_.Name as Name1_0_ 
FROM Project this_ 
  **inner join** Job job1_ 
    on this_.FK_JobId=job1_.Id 
WHERE job1_.Name = @p0; @p0 = 'sometextB'

Otolaryngology

But you can specify join type in the CreateAlias overload? CreateAlias always seems to default to inner join for me...even when the many-to-one allows nulls. – Naxos

Yes with NH2++ the CreateAlias also allows to specify a JoinType overriding the mapped associations. I'm guessing that since the CreateCriteria returns an ICriteria object ("rooted" at the associated entity) it may be possible to generate different (advanced) queries since the generated ICriteria can be manipulated in multiple ways. – Otolaryngology

Answer 2

To explain the difference between CreateCriteria and CreateAlias in NHibernate 2.0 + lets see the following domain model.

public class Product
{
    public virtual int Id { get; private set; }
    public virtual string Name { get; set; }
    public virtual decimal Price { get; set; }
    public virtual Category Category { get; set; }
    public virtual IList<ProductStock> ProductStocks { get; set; }
}

public class Category
{
    public virtual int Id { get; private set; }
    public virtual string Name { get; set; }
    public virtual IList<Product> Products { get; set; }
}

public class ProductStock
{
    public virtual int Id { get; private set; }
    public virtual Product Product { get; set; }
    public virtual string WarehouseName { get; set; }
    public virtual int Stock { get; set; }
}   

Now if you write following criteria to inner join these entities

var criteria = DetachedCriteria.For<Product>()
                .CreateCriteria("Category", JoinType.InnerJoin)
                .CreateCriteria("ProductStocks", "ps", JoinType.InnerJoin)
                .Add(Restrictions.Le("ps.Stock",10));

The above criteria wont work because when the first CreateCriteria runs it return "Category" entity, therefore when the second CreateCriteria execute it wont find property ProductStocks in the "Category" entity and the query will fail.

So the correct way to write this criteria is

var criteria = DetachedCriteria.For<Product>()
                .CreateAlias("Category", "c", JoinType.InnerJoin)
                .CreateCriteria("ProductStocks", "ps", JoinType.InnerJoin)
                .Add(Restrictions.Le("ps.Stock",10));

When the first CreateAlias runs it return "Product" entity, when the second CreateCriteria execute it will find property ProductStocks in the "Product" entity.

So the TSQL will be like this.

SELECT this_.ProductID     as ProductID8_2_,
       this_.Name          as Name8_2_,
       this_.Price         as Price8_2_,
       this_.CategoryID    as CategoryID8_2_,
       ps2_.ProductStockID as ProductS1_9_0_,
       ps2_.Stock          as Stock9_0_,
       ps2_.ProductID      as ProductID9_0_,
       ps2_.WarehouseID    as Warehous4_9_0_,
       c1_.CategoryID      as CategoryID0_1_,
       c1_.Name            as Name0_1_
FROM   [Product] this_
       inner join [ProductStock] ps2_ on this_.ProductID = ps2_.ProductID
       inner join [Category] c1_ on this_.CategoryID = c1_.CategoryID
WHERE  ps2_.Stock <= 10 

I hope this will help.

Karlotte

This makes a lot of sense and answers the original question. – Employment

Answer 3

createAlias() returns original criteria as is result createCriteria() returns new criteria constructed with createCriteria

difference will be when chaining methods e.g.

cr.createAlias().add(Restrictions.ilike("code","abc")) will add restriction to entity cr.createCriteria("parent","p").add(Restrictions.ilike("code","abc")) will add restriction to its parent

Tearing