CODEWITHSUNDEEP
fluent-nhibernatenhibernate-criteria
user2534947
user2534947

Reputation: 3

Fluent NHibernate - Query using Criteria with filter on connecting table

I have following tables

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.

Upvotes: 0

Views: 4517

Answers (1)

Firo
Firo

Reputation: 30813

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();

Upvotes: 1

Related Questions