CODEWITHSUNDEEP
c#sql-serverdapper
John
John

Reputation: 71

How to use IN Clause for list of GUID's in Dapper Delete Query

How to use in clause in delete dapper query?

DELETE FROM [tableName] where [columnName] in ({list of guids});

I tried the below code, but I'm getting "unique identifier conversion" error:

//EmpIds is List of Guids;
var empIds = string.Format("{0}", string.Join(",", EmpIds.Select(x => $"'{x}'").ToArray()));

using (var connection = new SqlConnection(ConnectionString))
{
    connection.Open();
    var deletedRowsCount = connection.Execute("
                         DELETE FROM [Employee] WHERE EmployeeId in                         
                       (@empIds)",  new { empIds = empIds });
                        //EmployeeId is uniqueidentifier datatype in sqlserver db
}

Anybody tell me how to resolve this issue in dapper query for bulk delete.

Upvotes: 2

Views: 3200

Answers (1)

Alex
Alex

Reputation: 8116

The syntax for List<> support in dapper is slightly different than in an actual T-SQL query. See: https://github.com/StackExchange/Dapper

DELETE FROM [Employee] 
WHERE EmployeeId in @empIds

It does not require braces around the IN clause's parameter. And you can just pass an IEnumerable<>. Like this 

new { empIds = new[] { Guid.NewGuid(), Guid.NewGuid() } }

Upvotes: 4

Related Questions