CODEWITHSUNDEEP
nhibernatefluent-nhibernatesql-server-ce
Haimon
Haimon

Reputation: 227

nhibernate delete multiple records by list of id's with CreateQuery

I am trying to delete multiple records from TABLE A that exits in list of ids in TABLE B as follows:

List<ulong> ids = new List<ulong> {1, 2};

string deleteQuery = string.Format(
                @"DELETE FROM TABLE_A WHERE EXISTS 
                (SELECT id FROM TABLE_B WHERE checkpoint_id IN :idList)");

Session.CreateQuery(deleteQuery).SetParameterList("idList", ids).ExecuteUpdate();

I get the following Exception:

NHibernate.Hql.Ast.ANTLR.QuerySyntaxException: Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 2, column 100

If I put the list hardcoded [IN (1,2)] it works. What do I miss?

Upvotes: 1

Views: 1694

Answers (1)

Haimon
Haimon

Reputation: 227

Found the problem:

I needed an extra brackets arround the :idList.

string deleteQuery = string.Format(
            @"DELETE FROM TABLE_A WHERE EXISTS 
            (SELECT id FROM TABLE_B WHERE checkpoint_id IN (:idList))");

Upvotes: 2

Related Questions