LINQ to nHibernate - translating SQL "NOT IN" expression to LINQ

Question

I tried to translate SQL "NOT IN" expression to LINQ, and I found that I should use the "Contains" option. I have 2 tables:

ProductsGroups         Products                    
--------------         ---------    
id                     product_id    
product_id             product_name 

My queries looks like this:

var innerQuery = from pg in Session.Query<ProductsGroups>       
select pg.product_id;     

var Query = from p in Session.Query<Products>                        
where !innerQuery.Contains(p.product_id)                        
select new {p.product_id, p.product_name};

But the sql that nHibernate generates is wrong:

select p.product_id, p.product_name    
from Products p    
where not (exists (select product_id                       
from ProductsGroups pg                   
where p.product_id = pg.id))

The "where" clause is not on the right field, it compares product_id to progucts group id. Does anybody knows how can I solve it?

The solution that I found for meanwhile is to convert first query to list, and then use this list in second query:

var innerQuery = (from pg .....).ToList();

Then, the nHibernate translates the "Contains" expression to "NOT IN", as I want:

select p.product_id, p.product_name    
from Products p    
where not (p.product_id in (1,2,3,4))

Answer 1

I am not sure, but I think you're running into a problem b/c contains determines if an element is in the collection by "using the default equality comparer." (MS documentation) I assume your productgroup mapping specifies it's Id as the Id property. So from nHibernate's perspective that is the value to use to determine equality.