c# - NHibernate Delete from Where COLUMN in(集合)

Question

我正在尝试使用以下方法使用 where column in (collection) 从表中删除行:

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));
}

问题是 yearsAsCommaSeperatedString 是一个字符串，因此数据库无法将其解释为数字。我也试过添加整数列表作为参数，但 NHibernate 不知道如何处理它。

如何将 where in(collection) 与 CreateSQLQuery 一起使用？

Answer 1

你可以这样使用

    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>();

或者你可以试试这个

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();
}