Geek Answers Handbook

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

I am having problems handling a situation where the list of parameters sent by a named request 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 is 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 a method, I get this error:

System.NullReferenceException: An object reference is not set to an object instance.

Server Stack Trace:

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

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

in 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

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

What would be the best way to solve this problem? Note that a named query is, of course, much more complicated than the one presented above, so I would like to avoid copying it into a second version that does not use a parameter list.

+4
source share
3 answers

I just ran into the same problem, so although I need to share a solution:

Change your request to:

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

And the code:

 public IList<MyClassBE> FilterByCategoryID(List<String> categoryIDs) { return session.GetNamedQuery("MyClass_FilterByCategoryID") .SetIn32("hasCatogories", categoryIDs.Any() ? 1 : 0) .SetParameterList("categoryIDs", categoryIDs.Any() ? categoryIDs : new [] {"fake-non-existing-id"}) .List<MyClassBE>(); }

Explanation:

  • We modify the request to ignore categories when there are none.
  • We modify an additional parameter to indicate ( 1 ).
  • We add a random identifier that is never used. Just make sure the IN SQL statement is valid.

Thus, you can save your complex query and simply add additional parameters to it.

The obvious drawback is that it skips the unnecessary parameter.

But he does the job.

+3
source

I believe you can avoid this error using .NET Cast

 return session.GetNamedQuery("MyClass_FilterByCategoryID") .SetParameterList("categoryIDs", categoryIDs) .List().Cast<MyClassBE>();

This should return an empty list, not an exception.

0
source

check if the list is empty and do something else. in this particular query, you want all MyClassBE not to be in the ids category, 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>(); }
0
source

More articles:


All Articles