Join query in Nhibernate criteria or in Nhibernate Linq

Question

I have a query I am trying to run but I'm not getting desired result.

select * from employee_login e
left join employee_attendance ea on 
e.emp_id = ea.EmpId and dated = '2012-01-11'

The Linq query which I tried with Nhibernate is

 var attendance = from emp in session.Query<Employee>()
                                 join empatten in session.Query<EmployeeAttendance>()
                                 on emp.emp_id equals empatten.EmpId  into atts 
                                 from ur in atts.DefaultIfEmpty()                                     
                                 select new { ur };

In the var attendance resultview. How can I achieve these two things?

a left join over employee and employeeattendance ( employee is the left table)
a and condition on the join not over the join result .

I'm pretty new to this situation using Linq or detached criteria; a detached criteria would be a preferable answer.

Here are the models:

public class EmployeeAttendance
{
    public virtual string No_ { get; set; }
    public virtual Employee Employee { get; set; }       
}
public class Employee
{       
    public virtual string emp_id { get; set; }
    public virtual ISet<EmployeeAttendance> Attendances { get; set; }
    public Employee()
    {
        Attendances = new HashedSet<EmployeeAttendance>();
    } 
}

The Mapping is :

public class EmployeeAttendanceMap:ClassMap<EmployeeAttendance>
{
    public EmployeeAttendanceMap()
    {
        Table("Employee_Attendance");
        Id(x => x.No_).GeneratedBy.Assigned();          
        References(x => x.Employee).Column("emp_id");
    }
}
public class EmployeeMap : ClassMap<Employee>
{
    public EmployeeMap()
    {
        Table("employee_login");
        Id(x => x.emp_id).Column("emp_id").GeneratedBy.Assigned();           

        HasMany(x => x.Attendances).KeyColumn("No_").Cascade.All();        
    }
}

The Employee is the primary table and AttendanceLeave has the foreign key as EmpId from Employee Table

Edit : I tried this also in my last attempt:

 ICriteria criteria = session.CreateCriteria(typeof(Employee), "emp")
                     .CreateAlias("EmployeeAttendance", "Attendance", CriteriaSpecification.LeftJoin
                     , Restrictions.Eq("Attendance.Dated", DateTime.Parse("2012-1-11")));

but I ended up getting error as :

could not resolve property: EmployeeAttendance of: Royal.Data.Core.Domain.Employee

select * from employee_login e left join employee_attendance ea on e.emp_id = ea.EmpId and ea.dated = '2012-01-11' should be equivalent to ` select * from employee_login e left join employee_attendance ea on e.emp_id = ea.EmpId where ea.dated = '2012-01-11'`. Is there any Association between the Employee class and the EmployeeAttendance in your model? — Firo
@Firo sorry i forgot to put the domains . yes there is a Association between Employee Class and EmployeeAttendance in model where EmpId is the foreign key — Joy

AlexCuse AlexCuse · Accepted Answer · 2012-01-11T12:58:31

It looks like you want to get employees on leave as of a certain date. I think this would work, though I've never used the between expression in this way before:

var detached = DetachedCriteria.For<AttendanceLeave>("al")
    .Add(Expression.Between('2012-01-11', "LeaveFrom", "LeaveTo"))  //this should be a DateTime
    .Add(Restrictions.EqProperty ("al.EmpId", "e.emp_id")) //make sure to use "e" for employee criteria alias
    .SetProjection (Projections.Count ("al.EmpId"));

var employeesOnLeave = session.CreateCriteria<Employee>("e")
    .Add(Restrictions.Gt(Projections.Subquery(detached), 0))
    .List();

You'll still get the complete set of leaves on each employee, but it should be the employee you want.

update - looking at your comment, it seems something like this could be what you're after:

DateTime dateInQuestion = new DateTime(2012, 1, 11);

var employeesOnLeaveAsOfDateInQuestion =
                session.CreateCriteria<Employee>("e")
                    .CreateCriteria("e.Attendances", "ea"
                        , NHibernate.SqlCommand.JoinType.LeftOuterJoin
                        , Restrictions.Between(dateInQuestion, "ea.LeaveFrom", "ea.LeaveTo"))
                    .List<Employee>();

This seems to work - but you need to make sure the entities you get back are not cached, otherwise cached copies w/ the full collection will be returned. This is what I tested with - not exactly like your situation because collection is maintained through a link table, but I think it will work the same either way - you may need to evict the collection specifically with a straight one to many though (the EvictCollection method is found on the session factory, not the session). You should need this bit for testing only (in my tests, the database only lives as long as the session). There is also a QueryOver example in the gist if you'd prefer to solve it that way.

Join query in Nhibernate criteria or in Nhibernate Linq

1 Answers