Deleting multiple records by id list using HQL statement

Question

I want to delete several records of a specific object, where the identifier of the object is in the list of identifiers that I have. I am trying to perform this action in C # with NHibernate.

I have a list of identifiers.

I want to do something similar to this:

var idList = new List<Guid>() { Guid.NewGuid(),Guid.NewGuid()}; _session.CreateQuery("DELETE FROM MapsItem o WHERE o.Id = IN :idList") .SetParameterList("idList", idList) .ExecuteUpdate(); 

This results in the following error:

 Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 1, column 33 [DELETE FROM Album o WHERE o.Id = IN :idList] 

The query is an HQL statement.

What is wrong with the HQL query.

Both answers provided to the above question give the right solution. However, when I execute HQL, the error looks like this:

 could not insert/select ids for bulk delete[SQL: insert into #MapsItem SELECT mapsitem0_.Id as Id FROM MapsItem mapsitem0_ inner join BaseEntity mapsitem0_1_ on mapsitem0_.Id=mapsitem0_1_.Id WHERE Id in (? , ? , ? , ? , ? , ?)] 

The MapsItem entity is derived from the BaseEntity entity. Both have a property identifier in the database. The SQL query cannot be executed because the column identifier in the WHERE clause is ambiguous.

How can I solve this problem?

Answer 1

Remove the equal sign:

 DELETE FROM myObject o WHERE o.Id IN :idList 

Answer 2

Remove the equal sign, as well as the invalid request. It should be something like this. and I suggest you use string.Format instead.

 var idList = new List<Guid>() { Guid.NewGuid(),Guid.NewGuid()}; _session.CreateQuery(string.Format("DELETE FROM myObject o WHERE o.Id IN ({0})",string.Join(",",idList.ToArray()))).ExecuteUpdate();