CODEWITHSUNDEEP
c#nhibernatenhibernate-criteria
Tyler Wright
Tyler Wright

Reputation: 825

How to return a list of entities that are not in a given list

In NHibernate, how can I return a list of entities that are not in a given list?

Let's say I have a Client entity and I have a list defined called IList<Client> notInClients. I know that I want my sql to turn out something like this:

SELECT *
FROM Clients c1
WHERE c1.ClientId NOT IN (
    SELECT *
    FROM Clients c2
    WHERE c2.ClientId IN ('1', '2', '3', '4')
)

I'm assuming I would need to use a DetachedCriteria for this. Maybe something like:

var clients = session.CreateCriteria("c1")
    .Add(
        Subqueries.PropertyNotIn("c1.ClientId",
            DetachedCriteria.For<Client>("c2").HOW_DO_I_ADD_MY_LIST)));

I guess I'm just not sure how to build my subquery. Am I going down the right path on this?

Upvotes: 1

Views: 77

Answers (1)

Tyler Wright
Tyler Wright

Reputation: 825

Figured it out:

var notGuids = from c in notClients
               select c.ClientId;

var clients = unitOfWork.Session.CreateCriteria(typeof(Client))
    .Add(Expression.Not(Expression.In("ClientId", notGuids.ToArray()))).List<Client>();

Upvotes: 1

Related Questions