performancenhibernatecriteriaicriteria

NHibernate - CreateCriteria vs CreateAlias


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?


Solution

  • 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'