Nhibernate Queryover with subquery get Next free number

Question

How can I do this in nHibernate using queryover :

SELECT MIN(t.subid)+1 AS NextID 
FROM subject t  
WHERE NOT EXISTS  
    (SELECT id FROM subject n WHERE n.subid=t.subid+1)

Currently I have this but its not working because of this statement "SubId+1"

 _session.QueryOver(() => subject)
 .WithSubquery
 .WhereNotExists(
       subject 
       .Where(x => x.SubId==SubId+1)
       .Select(x => x.Id)
     )                                                         
 .Select(Projections.ProjectionList()
                     .Add(Projections.Min<subject>(x => x.SubId)))
 .List().First()                                     

Answer 1

One way, using NOT IN instead of NOT EXISTS (results are the same) would be like this (the SQL query would be a bit different, but result will be the same)

Subjectsubject = null;
Subjectinner = null;
var subquery = QueryOver.Of<Subject>(() => inner)
    .Select(Projections.SqlProjection(
       // important the subid is the column name, not property name
       " subid - 1 as innerId" // the trick here is, that we compare 
       , new[] { "innerId" }       // inner ID - 1, with outer ID
       , new IType[] { NHibernateUtil.Int32 }))
    ;

var id = session.QueryOver(() => subject)
    .WithSubquery
      .WhereProperty(() => subject.ID)
      .NotIn(subquery)
    .Select(Projections.ProjectionList().Add(Projections.Min<Subject>(s => s.ID)))
    .SingleOrDefault<int>();

var id = id + 1 ; // here we increment in C#, instead of projecting that into SQL

Summary: not sure about algorithm you've used, but the trick how to get "subid - 1" is to use projection:

Projections.SqlProjection(
       " subid - 1 as innerId" // sql statement 
       , new[] { "innerId" }   // its alias
       , new IType[] { NHibernateUtil.Int32 }) // type is int

NOTE: I would expect the last Projections to be MAX