JoinQueryOver in QueryOver to select notin list

Question

I have a list of record that I picked up through the code:

  var list= NhSession.QueryOver<Data.Models.MembModel>()
                .Where(w => w.Comp.Id == idcomp)
                .JoinQueryOver(jq => jq.Ver)
                .Select(s => s.Ver)
                .List<Dados.Models.VerModel>();

With this code I get a list of VerModel that I have relation in a MembModel. The problem is that I what get the list of VerModel that don't be in relation in a MembModal, I think to describe this, I want to select one list that is "notin" a first list. How can I do this?

Tks

Answer 1

What we need, as you said, is a NOT IN (subquery) statement. And NHibernate does have a clear way how to achieve that. First the subquery, which will return MembModel collection (filtered or not - as needed), represented by the VerModel.ID

var subquery = QueryOver.Of<Data.Models.MembModel>()
  // we can still filter this sub-select ... or not
  // .Where(w => w.Comp.Id == idcomp)
  // what we need to be returned is the reference id, the VerModel.ID
  .Select(m => m.Ver.ID);

And now we will query the VerModel itself, with the NOT IN (subquery) clause:

var list = session.QueryOver<Dados.Models.VerModel>()
    .WithSubquery
        .WhereProperty(v => v.ID) // the ID to match the prev selected one
        .NotIn(subquery)          // should NOT be IN
    .List<Dados.Models.VerModel>();

Check:

• 16.8. Subqueries