Getting {"Specified method is not supported."} exception when perform a query on a list which elemts should be looked up in another list

Question

I know it sounds stupid to some extend and vague but I need it :D
I want to perform a query on NH3.1 :

var internalReferences = Repository<InternalReferenceRule>
                    .FindAll(e => e.PropertyType.EntityType.Id == 1)

var properties = Repository<IProperty>
                        .Find(p => p.PropertyType.RuleObjects.Any(r => internalReferences.ToList().Any(i => i.Id == r.Id)));

the first list (internalReferences) is going to be used in the second query which wants to check if the RuleObjects of a property are available in the first list.

I kinda simplified the original query to make it more understandable.....

anyway, I get the System.NotSupportedException from NHibernate and its message is : {"Specified method is not supported."}

any idea?

Answer 1

You can't use internalReferences.Any() in NHibernate queries because it can't know how to translate that to SQL. Try the following

var internalReferences = Repository<InternalReferenceRule>
   .FindAll(e => e.PropertyType.EntityType.Id == 1).Select(x => x.Id).ToList();

var properties = Repository<IProperty>
   .Find(p => p.PropertyType.RuleObjects.Any(r => internalReferences.Contains(r.Id)));

That should result in a SQL query that uses IN (:p0, :p1, :p2).