What to do when your passing in a collection of zero into a hql request?

I have this

     public List<TableA> Get(Guid Id, List<int> listId)
    {
        const string query = "FROM TableA WHERE MyListColumn in (:listId) AND Id in (:Id)";
        return session.CreateQuery(query).SetParameterList("listId", listId).SetParameter("Id",Id).List<TableA>().ToList();
    }

I am wondering what to do if keys count equals zero? When using a linq way it would jsut return an empty List 开发者_JS百科of TableAs. The only way I can think of is a "if" statement but I am wondering if there is any other way before I do that since I don't want really logic in my method here as it is a repo method.

if(listId.Count == 0){..}

As I get this error

Server Error in '/' Application. An empty parameter-list generate wrong SQL; parameter name 'listId'

---

I consider an if block proper as it's not "program logic": it's query generation.

That said, i personally change the query signature to a MyListColumn =:id for listId.Count==1 and for listId.Count==0 i don't run the query at all: Why have a round-trip when you know that no results will return?

---

Based on your other questions regarding this topic, I suggest using QueryOver: The following query will work with listId == null and listId.Count == 0.

public List<TableA> Get(List<int> listId)
{
    return session.QueryOver<TableA>()
            .Where(Restrictions.In(
                Projections.Property<TableA>(e => e.Id), 
                listId ?? (new List<int>() { }))).List().ToList();
}

I dropped the Guid Id parameter, since that doesn't make much sense here.

Edit:

Yes, there are several ways in NHibernate to query the database (plain SQL, HQL, ICriteria, QueryOver and NHibernate.Linq). All of these have their strengths and weaknesses and I usually try to use the method best suited for the job.

In this I suggestes QueryOver instead of HQL, because you get full Intellisense and compiler support. Also, QueryOver does not throw an error if the list is empty (it will translate to WHERE 1=0). As Jaguar wrote, in that case you could also return an empty list without going to the DB.

I usually only use HQL when QueryOver or ICriteria can't get me the result I want. The Linq provider still lacks some features, so that will currently only work for rather simple queries.

You can also do the above query with Linq. Here is the NHibernate.Linq version of the code above: (this works in NHibernate 3.1, not tested in previous versions)

List<int> idList = new List<int>(){ 1, 2 };

// if idList is null this will still throw an error
var result = session.Query<TableA>()
               .Where(e => idList.Contains(e.Id)).ToList();