0

I have following tables

  • User - Primary column - Id and other detail columns
  • Department - Primary column - Id and other detail columns
  • UserDepartment - Primary column - Id and other columns are UserId and DepartmentId

I want to find all users those are in department - (1 and 2). I also want to find all users those are in department - (1 or 2).

Can anybody suggest me the criteria to get all users in department (1 and 2)? Another criteria to get all users in department - (1 or 2)?

I am new to FluentNHibernate, so didn't tried anything yet as I found nothing relevant on Google search? With the help of Google search, I was able to write criteria for 1-1 relations but not for above scenario.

user2534947
  • 3
  • 1
  • 3
  • Welcome to StackOverflow! What have you tried and what isn't working for you? – Andrew Whitaker Jun 29 '13 at 19:52
  • I am new to FluentNHibernate, so didn't tried anything yet as I found nothing relevant on Google search? With the help of Google search, I was able to write criteria for 1-1 relations but not for above scenario. – user2534947 Jun 30 '13 at 04:25

1 Answers1

1

assuming following classes

class User
{
    public virtual int Id { get; private set; }
    public virtual ICollection<Department> Departments { get; private set; }
}

class Department
{
    public virtual int Id { get; private set; }
}

class UserMap : ClassMap<User>
{
    public UserMap()
   {
       Id(x => x.Id);
       HasManyToMany(x => x.Departments)
           .Table("UserDepartment")
           .ParentKeyColumn("UserId")
           .ChildKeyColumn("DepartmentId");
   }
}

then query for 1 or 2

var results = session.QueryOver<User>()
    .JoinQueryOver<Department>(x => x.Departments)
        .Where(d => d.Id.IsIn(departmentIds))
    .List();

query for 1 and 2

User userAlias = null;
var subquery = session.QueryOver<Department>()
    .Where(d => d.User.Id == userAlias.Id && d.Id.IsIn(departmentIds))
    .Select(Projections.RowCount());

var results = session.QueryOver<User>()
    .WithSubquery.WhereValue(departments.Count).Eq(subquery)
    .List();
Firo
  • 30,626
  • 4
  • 55
  • 94
  • Thanks for reply.. but it's not working... First it is not able to get d.Id in the query. I guess, it is because d represents collection of Department. And if I apply the where condition on x.Departments then IsIn option is not available. Am I missing anything? Other doubt that I have is IsIn might give users in either department 1 or 2, but I want users who is associated with both Department 1 and 2. – user2534947 Jul 16 '13 at 10:04
  • the compiler sometimes doesn't use the right Overload of JoinQueryOver i'll edit – Firo Jul 16 '13 at 12:25
  • Thanks, with help of your QueryOver logic, I was able to built criteria and resolved my problem. One more thing I would like to know, that is, what is recommended to use "QueryOver" or "CreateCriteria"? – user2534947 Jul 18 '13 at 10:05
  • QueryOver is a typesafe wrapper around Criteria. It makes your life easier at the expense of minimal performance overhead. If you are crazy about shaving nanoseconds from a second or have untyped/stringified properties then Criteria is the way to go. – Firo Jul 18 '13 at 11:34