CODEWITHSUNDEEP
c#sqlnhibernate
randoms
randoms

Reputation: 2783

NHibernate Delete from Where COLUMN in (collection)

I am trying to delete rows from a table using where column in (collection) using the following method: 

public void DeleteRows(int parentId, List<int> years)
{
    var yearsAsCommaSeperatedString = ListToCommaSeperatedString(years);
    const string query = "DELETE FROM TABLE t WHERE t.PARENT_ID=:Parent AND t.YEAR in(:yearList)";
    Session
            .CreateSQLQuery(query)
            .SetParameter("Parent", parentId)
            .SetParameter("yearList", yearsAsCommaSeperatedString)
            .ExecuteUpdate();
}

private static string ListToCommaSeperatedString(IEnumerable<int> ints)
{
    var aggregate = ints.Aggregate("", (current, i) => current + (i + ", "));
    return aggregate.Substring(0, aggregate.LastIndexOf(",", StringComparison.Ordinal));
}

The problem is that yearsAsCommaSeperatedString is a string, therefor the db can not interpret it the numbers. I've also tried adding the list of integers as the parameter, but NHibernate does not know what to do with it.

How can i use where in(collection) with CreateSQLQuery?

Upvotes: 3

Views: 6558

Answers (2)

Verni
Verni

Reputation: 211

If your Method works, you can use the SetParamter again, but must change the SQL-Query to the follow:

var yearsAsCommaSeperatedString = ListToCommaSeperatedString(years);
const string query = "DELETE FROM TABLE t WHERE t.PARENT_ID=:Parent AND t.YEAR in(\":yearList\")";
Session .CreateSQLQuery(query)
        .SetParameter("Parent", parentId)
        .SetParameter("yearList", yearsAsCommaSeperatedString)
        .ExecuteUpdate();

Should be better than string concatenation (sql-injection) :)

Greetings

Upvotes: 1

Rahul Agrawal
Rahul Agrawal

Reputation: 8971

You can use something like this

    ISession session = GetSession();
    string hql = @"from Product p
                   where p.Category in (:categories)";

    var categoriesToSearch = new[] {new Category {Id = 1}, new Category {Id = 2}};

    var query = session.CreateQuery(hql);
    query.SetParameterList("categories", categoriesToSearch);

    var products = query.List<Product>();

Or you can try this

public void DeleteRows(int parentId, List<int> years)
{        
    const string query = "DELETE FROM TABLE t WHERE t.PARENT_ID=:Parent AND t.YEAR in (:yearList)";
    Session
            .CreateSQLQuery(query)
            .SetParameter("Parent", parentId)
            .SetParameterList("yearList", years)
            .ExecuteUpdate();
}

Upvotes: 6

Related Questions