How to set parameters on a Query in MultiQuery ? "The named parameter code was used in more than one query."

674 Views Asked by At

I am trying to set a parameter for a Query inside a MultiQuery:

var multiQuery = SessionHolder.Current.CreateMultiQuery();

foreach (string name in names)
{
    var query = SessionHolder.Current
        .CreateQuery("select c.Name, c.Surname " +
                     "from Person as p " +
                     "where p.Name = :name or " +
                           "p.Name like ':name/%'")
        .SetParameter("name", name);

    multiQuery = multiQuery.Add(query);
}

var multiQueryResult = multiQuery.List();

and getting this exception:

QueryException was unhandled by user code

The named parameter code was used in more than one query. Either give unique names to your parameters, or use the multi query SetParameter() methods to set the named parameter

How can I use the multi query SetParameter() methods to set the named parameter so the example works as intended?

Thanks for replies!

2

There are 2 best solutions below

1
On BEST ANSWER

Maybe is not too clean but did u think to use an unique id for the parameter?

int i = 0;
foreach (string name in names)
{
    string paramname = "name" + (++i).ToString();
    var query = SessionHolder.Current
        .CreateQuery("select c.Name, c.Surname " +
                     "from Person as p " +
                     "where p.Name = :" + paramname + " or " +
                           "p.Name like ':" + paramname + "/%'")
        .SetParameter(paramname, name);
    multiQuery = multiQuery.Add(query);
}
1
On

You need to consider what multiquery is actually doing.

Behind the scenes you're going to end up with a single query to the database that looks like this:

select p.Name, p.Surname from Person as p where p.Name = :name or p.Name like ':name/%'
select p.Name, p.Surname from Person as p where p.Name = :name or p.Name like ':name/%'
select p.Name, p.Surname from Person as p where p.Name = :name or p.Name like ':name/%'
select p.Name, p.Surname from Person as p where p.Name = :name or p.Name like ':name/%'
select p.Name, p.Surname from Person as p where p.Name = :name or p.Name like ':name/%'
...

You probably don't want the same criteria in each part of the query as that doesn't make much sense. You should be using unique names if this is what you want to do.

However, looking at your query you probably want to rewrite it into a single query with multiple conditions anyway.

Perhaps something with a criteria more like:

p.Name in (:names) or (p.Name like ':name1/%' or p.Name like ':name2/%' ...)