haipham23 haipham23 - 2 months ago 21
C# Question

NHibernate: Could not resolve property: empAlias

My purpose is to find Employee information in Project that have ProjectNumber > 2

This is my query:

List<EmployeeDAO> empList = new List<EmployeeDAO>();
using (ISession mySession = SessionFactory().OpenSession())
{
using (ITransaction myTransaction = mySession.BeginTransaction())
{
EmployeeDAO empDaoAlias = null;
Employee empAlias = null;
Group groupAlias = null;
Project projectAlias = null;
Rank rankAlias = null;
EmployeeFunctionInProject efpAlias = null;

empList = mySession.QueryOver<Employee>(() => empAlias)
.JoinAlias(() => empAlias.Rank, () => rankAlias)
.JoinAlias(() => empAlias, () => efpAlias.Employee)
.Inner.JoinAlias(() => efpAlias.Project, () => projectAlias)
.Where(() => projectAlias.ProjectNumber > 2)
.Select(Projections.ProjectionList()
.Add(Projections.Property(() => empAlias.Visa).WithAlias(() => empDaoAlias.Visa))
.Add(Projections.Property(() => empAlias.FirstName).WithAlias(() => empDaoAlias.FirstName))
.Add(Projections.Property(() => empAlias.LastName).WithAlias(() => empDaoAlias.LastName))
.Add(Projections.Property(() => empAlias.FirstName).WithAlias(() => empDaoAlias.Project))
.Add(Projections.Property(() => rankAlias.Name).WithAlias(() => empDaoAlias.Rank)))
.TransformUsing(Transformers.AliasToBean<EmployeeDAO>())
.List<EmployeeDAO>().ToList();
}
}
return empList;


I got this error


could not resolve property: empAlias of: MyWeb10.Models.Employee


I doub that the problem occurs in line
.JoinAlias(() => empAlias, () => efpAlias.Employee)


My Employee class

public class Employee {
private int _id;
private Rank _rank;
private string _visa;
private string _firstName;
private string _lastName;
private DateTime? _birthday;
private string _university;
private DateTime _rowversion;
public Employee() {
EmployeeFunctionInProject = new List<EmployeeFunctionInProject>();
Group = new List<Group>();
}
public virtual int Id {
get {
return this._id;
}
set {
this._id = value;
}
}
public virtual Rank Rank {
get {
return this._rank;
}
set {
this._rank = value;
}
}
public virtual string Visa {
get {
return this._visa;
}
set {
this._visa = value;
}
}
public virtual string FirstName {
get {
return this._firstName;
}
set {
this._firstName = value;
}
}
public virtual string LastName {
get {
return this._lastName;
}
set {
this._lastName = value;
}
}
public virtual DateTime? Birthday {
get {
return this._birthday;
}
set {
this._birthday = value;
}
}
public virtual string University {
get {
return this._university;
}
set {
this._university = value;
}
}
public virtual DateTime Rowversion {
get {
return this._rowversion;
}
set {
this._rowversion = value;
}
}
public virtual IList<EmployeeFunctionInProject> EmployeeFunctionInProject { get; set; }
public virtual IList<Group> Group { get; set; }
}


My EmployeeFunctionInProject class

public class EmployeeFunctionInProject {
private int _id;
private Function _function;
private Project _project;
private Employee _employee;
private DateTime _rowversion;
public virtual int Id {
get {
return this._id;
}
set {
this._id = value;
}
}
public virtual Function Function {
get {
return this._function;
}
set {
this._function = value;
}
}
public virtual Project Project {
get {
return this._project;
}
set {
this._project = value;
}
}
public virtual Employee Employee {
get {
return this._employee;
}
set {
this._employee = value;
}
}
public virtual DateTime Rowversion {
get {
return this._rowversion;
}
set {
this._rowversion = value;
}
}
}


My Employee mapping

<hibernate-mapping assembly="MyWeb10" namespace="MyWeb10.Models" xmlns="urn:nhibernate-mapping-2.2">
<class name="Employee" table="EMPLOYEE" lazy="true" >
<id name="Id" column="ID">
<generator class="identity" />
</id>
<many-to-one name="Rank">
<column name="RANK" sql-type="int" not-null="true" />
</many-to-one>
<property name="Visa">
<column name="VISA" sql-type="varchar" not-null="true" unique="true" />
</property>
<property name="FirstName">
<column name="FIRST_NAME" sql-type="varchar" not-null="true" />
</property>
<property name="LastName">
<column name="LAST_NAME" sql-type="varchar" not-null="true" />
</property>
<property name="Birthday">
<column name="BIRTHDAY" sql-type="date" not-null="false" />
</property>
<property name="University">
<column name="UNIVERSITY" sql-type="varchar" not-null="true" />
</property>
<property name="Rowversion">
<column name="ROWVERSION" sql-type="timestamp" not-null="true" />
</property>
<bag name="EmployeeFunctionInProject" inverse="true">
<key column="EMPLOYEE" />
<one-to-many class="EmployeeFunctionInProject" />
</bag>
<bag name="Group" inverse="true">
<key column="LEADER" />
<one-to-many class="Group" />
</bag>
</class>
</hibernate-mapping>


My EmployeeFunctionInProject mapping

<hibernate-mapping assembly="MyWeb10" namespace="MyWeb10.Models" xmlns="urn:nhibernate-mapping-2.2">
<class name="EmployeeFunctionInProject" table="EMPLOYEE_FUNCTION_IN_PROJECT" lazy="true" >
<id name="Id" column="ID">
<generator class="identity" />
</id>
<many-to-one name="Function">
<column name="FUNCTION" sql-type="int" not-null="false" />
</many-to-one>
<many-to-one name="Project">
<column name="PROJECT" sql-type="int" not-null="false" />
</many-to-one>
<many-to-one name="Employee">
<column name="EMPLOYEE" sql-type="int" not-null="false" />
</many-to-one>
<property name="Rowversion">
<column name="ROWVERSION" sql-type="timestamp" not-null="true" />
</property>
</class>
</hibernate-mapping>

Answer

In these case, when we want to filter root/parent entity by some of its collection/child memebers, we should use subqueries (inner SELECT)

EmployeeFunctionInProject efpAlias = null;
Employee empAlias = null;
Project projectAlias = null;

// subquery, selecting two pair table and the Project table
var subquery = QueryOver.Of<EmployeeFunctionInProject>(() => efpAlias)
    .JoinAlias(() => efpAlias.Entity, () => projectAlias)
    // just the ProjectNumber over 2
    .Where(() => projectAlias.ProjectNumber > 2)
    // the ID of an employee
    .Select(x => efpAlias.Employee.Id);

// the root query, over Employee
var list = session.QueryOver<Employee>(() => empAlias)
    .WithSubquery
       .WhereProperty(() => empAlias.Id)
       .In(subquery)
    // the rest of the query
    ...// Take(), Skipe(), Select(), List()

See more here:

Comments