EF: Delete Multiple Rows using SQL Query Oracle

Question

How can I delete multiple rows in a single SQL query for Oracle using Entity Framework?

"DELETE FROM WOTRANSITION WHERE woiddisplay = @id OR woid = @id" 

Context.Database.ExecuteSqlCommand(
    "DELETE FROM WOTRANSITION WHERE woiddisplay = @id OR woid = @id",
    new[] { new SqlParameter("@id", id) });

Example code above is wrong and will return error:

Unable to cast object of type 'System.Data.SqlClient.SqlParameter' to type 'Oracle.ManagedDataAccess.Client.OracleParameter

Answer 1

You're trying to connect to Oracle, but you're using an SqlParameter.

Use an OracleParameter instead:

Context.Database.ExecuteSqlCommand(
    "DELETE FROM DPCMWOTRANSITION WHERE woiddisplay = :id OR woid = :id",
    new[] { new OracleParameter("id", id) });

I made a few others changes too, since I don't think the parameter names are quite correct.

Answer 2

I found the solution by myself. Here is the answer:

string query = string.Format("DELETE FROM WOTRANSITION WHERE woiddisplay = {0} OR woid = {0}", id);
Context.Database.ExecuteSqlCommand(query);
Context.SaveChanges();

For more detail, Solution