Delete multiple records by list of id's with HQL statement

Question

I want to delete multiple records of a certain entity where the id of the entity is in the list of ids I have. I am trying to perform this action in C# with NHibernate.

What I have is a list of Ids.

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 into 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 a HQL statement.

What is wrong in the HQL query.

Both of the provided answers to the above question give a correct solution. However, when I execute the HQL,the error is 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 entity MapsItem derives from the entity BaseEntity. Both have a property Id in the database. The SQL query cannot be executed, because the column Id in the WHERE clause is ambigious.

How can I solve this problem ?

I've encountered the same problem (ambiguous column name 'id') while trying to bulk update derived elements using query.ExecuteUpdate — Silviu, Jul 28 '11 at 13:50

score 5 · Answer 1 · answered Jan 12 '11 at 15:37

5

Remove the equal sign:

DELETE FROM myObject o WHERE o.Id IN :idList

answered Jan 12 '11 at 15:37

jgauffin

99,844
45
235
372

score 2 · Answer 2 · answered Jan 12 '11 at 16:49

Delete the equals to sign and also your inquery is incorrect. It should be something like this. and i would suggest you to 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();

Delete multiple records by list of id's with HQL statement

2 Answers2