How can I handle a possibly empty parameter List when using a named query in NHibernate?

Question

I'm having issues on handling a situation where a list of parameters sent to a named query in NHibernate is empty.

This is an example of my situation:

<sql-query name="MyClass_FilterByCategoryID">
    <return alias="MyClass" class="MyProject.BusinessEntities.MyClassBE"/>
    <![CDATA[
    SELECT DISTINCT MyClass.*
    FROM MyClassTable MyClass
    WHERE 1 = 1
            AND MyClassTable.CategoryID NOT IN (:categoryIDs) 
    ]]>
</sql-query>

This is the method that gets called:

public IList<MyClassBE> FilterByCategoryID(List<String> categoryIDs)
{
    return session.GetNamedQuery("MyClass_FilterByCategoryID")
        .SetParameterList("categoryIDs", categoryIDs)
        .List<MyClassBE>();
}

However, when I pass an empty List to the method, I get this error:

System.NullReferenceException : Object reference not set to an instance of an object.

Server stack trace:

at NHibernate.Engine.TypedValue..ctor(IType type, Object value, EntityMode entityMode) in C:\junctions\BS\3rdParty\NHibernate.2.1.2.GA-src\src\NHibernate\Engine\TypedValue.cs: line 25

at NHibernate.Impl.AbstractQueryImpl.SetParameterList(String name, ICollection vals, IType type) in C:\junctions\BS\3rdParty\NHibernate.2.1.2.GA-src\src\NHibernate\Impl\AbstractQueryImpl.cs: line 647

at NHibernate.Impl.AbstractQueryImpl.SetParameterList(String name, ICollection vals) in C:\junctions\BS\3rdParty\NHibernate.2.1.2.GA-src\src\NHibernate\Impl\AbstractQueryImpl.cs: line 666

at MyProject.Dao.MyClassDao.FilterByCategoryID(List`1 categoryIDs) in MyClassDao.cs: line 50

What would be the best way to solve this? Please note that the named query is of course far more complicated than the one presented above, so I'd like to avoid copying it to a second version that doesn't use the parameter list.

Answer 1

test if the list is empty and do something else. in this particular query you want all the MyClassBE that are not in the category ids which means all of them:

public IList<MyClassBE> FilterByCategoryID(List<String> categoryIDs)
{
    if (categoryIDs.Count > 0)
        return session.GetNamedQuery("MyClass_FilterByCategoryID")
            .SetParameterList("categoryIDs", categoryIDs)
            .List<MyClassBE>();
    else
        return session.CreateQuery("from MyClassBe").List<MyClassBE>();
}