Nhibernate condition on join with IQueryOver

Question

I was searching for similar problem on google and stackoverflow for almost 2 hours but did not find any solution.

I have 2 tables with relation 1 to many.

1) [Accounts]
PK Account_Id
int User_ID


2) [Temporary_Accounts]
Fk Account_Id
char IsAccepted   {'1','0',null}
varchar name

And 2 mapped classes

1) Acc
int Id;
User user;
TempAcc Temp; //cause each Account can have 0 or one TempAcc (with IsAccepted == null)

2)TempAcc
int Id;
bool IsAccepted;
string name;

I want to display all accounts for given user_id with additional information(f.e name) for Accounts which has record in [Temporary_Accounts] and IsAccepted == null.

so the SQL should look like:

select acc.Account_Id, acc.User_Id, tempacc.Name 

from Account acc left join Temporary_Account tempacc 
on (acc.Account_ID = tempacc.Account_Id and tempacc.IsAccepted is null)
where (acc.User_Id = 65);

but my IQueryOverquery:

IQueryOver<Acc> query = (...)
query.JoinAlias(f => f.Temp,
                () => Temp,
                JoinType.LeftOuterJoin)
     .Where(f => f.Temp.IsAccepted == null)
     .And(f => f.user.id == userid);

generates such sql:

select acc.Account_Id, acc.User_Id, tempacc.Name 

from Accounts acc left join Temporary_Accounts tempacc 
on (acc.Account_ID = tempacc.Account_Id)
where (acc.User_Id = 65 and tempacc.IsAccepted is null);

so I am getting less results than in first correct query.

Do you have any Idea what should I change or what could I do to obtain results from first query ? My Idea was to leftjoin Accounts table with subquery which selects all IsAccepted=null accounts from Temporary_Accounts table ,but I am not sure how to do it in Iqueryover or Icriteria.

I will be grateful for any advices

Answer 1

Since you have a 1-Many between Acc and Temp your sample sql will produce a Cartesian product.

The Queryover you will need uses a Subquery and looks something like the following:

Acc accountAlias = null;
var subQuery = QueryOver.Of<Temp>()
               .Where(x=>x.IsAccepted==null)
               .And(x=>x.Account.Id==accountAlias.Id);

var results = session.QueryOver<Acc>(()=>accountAlias)
              .Where(x=>x.User.Id==65)
              .WithSubquery.WhereExists(subQuery);

Producing SQL like this:

select *
from Accounts a
where a.User_Id=65
and exists (
    select t.Account_Id
    from Temporary_Accounts t
    where t.IsAccepted is null and t.Account_Id=a.Account_Id
)

This article on nhibernate.info is very helpful for figuring out complex queries with QueryOver.

UPDATE:

If you need to also find Accounts which do not have any corresponding rows in Temporary_Accounts then you need two subqueries and a Disjunction.

Acc accountAlias = null;
var hasTempAccount = QueryOver.Of<Temp>()
               .Where(x=>x.IsAccepted==null)
               .And(x=>x.Account.Id==accountAlias.Id);

var doesNotHaveTempAccount = QueryOver.Of<Temp>()
               .And(x=>x.Account.Id==accountAlias.Id);

var results = session.QueryOver<Acc>(()=>accountAlias)
  .Where(x=>x.User.Id==65)
  .Where(Restrictions.Disjunction()
    .Add(Subqueries.WhereExists(hasTempAccount))
    .Add(Subqueries.WhereNotExists(doesNotHaveTempAccount))     
  );

UPDATE 2:

Since NH 3.2 you can add extra conditions to a JOIN. See this answer for further details: Adding conditions to outer joins with NHibernate ICriteria/QueryOver query

Temp tempAlias = null;
Account accountAlias = null;
dto dto = null;
var results = Session.QueryOver<Account>(()=>accountAlias)
  .JoinAlias(x=>x.TempAccounts,()=>tempAlias,JoinType.LeftOuterJoin,
    Restrictions.IsNull(Projections.Property(()=>tempAlias.IsAccepted))
  )
  .Where(x=>x.Account.Id==65)
  .SelectList(list=>list
      .Select(()=>accountAlias.Id).WithAlias(()=>dto.AccountId)
      .Select(()=>accountAlias.User.Id).WithAlias(()=>dto.UserId)
      .Select(()=>tempAlias.Name).WithAlias(()=>dto.TempAccName)
  )
  .SetResultTransformer(Transformers.AliasToBean<dto>())
  .List<dto>();