Delete rows that not in subquery - ends with NHibernate.QueryException:

Question

I would like to delete all rows from table A that are not exist in table B. In order to do it I use session.Delete(query) as follows:

Session.Delete<A>("FROM A WHERE id NOT IN (SELECT vpg_id FROM B)");

I get the following error:

NHibernate.QueryException: could not resolve property: vpg_id of: A [FROM A WHERE Id NOT IN (SELECT vpgid FROM B)]

I tried also doing it using HQL as follows:

Session.CreateQuery("DELETE A WHERE id NOT IN (SELECT vpg_id FROM B)").ExecuteUpdate();

and get this Exception:

    NHibernate.QueryException: No data type for node: IdentNode vpg_id [DELETE A WHERE id NOT IN (SELECT vpg_id FROM B)]

Answer 1

You should try aliasing your classes (from NHibernate reference : http://nhibernate.info/doc/nhibernate-reference/queryhql.html#queryhql-subqueries)

from A as aClass 
where aClass.id not in ( 
    select bClass.vpg_id from B as bClass
)

or try exists

from A as aClass 
where not exists ( 
    select bClass.vpg_id from B as bClass where aClass.id = bClass.vpg_id
)