How can i write raw SQL commands in EntityFramework 6

Question

I'm trying to do a raw SQL statement which resembles this

DELETE FROM db.T0590_CODE_STRING WHERE IN (1,1,2,3,5)

the values in the IN clause are from rowIds list so therefore I'm trying to use a string.Join() but then the values are implemented as a String and not an Integer. Is there a workaround?

public void DeleteRow(List<int> rowIds)
{
    using (var db = new SkataModel())
    {
        var list = "(" + string.Join(", " , rowIds.Select(s => s)) + ")";
        db.Database.ExecuteSqlCommand("DELETE FROM db.T0590_CODE_STRING WHERE IN " + list);
            
        db.SaveChanges();
    }
}

If anyone have insight in how to do this with Linq or PreparedStatements that would also be appreciated.

Answer 1

You can change this line

var list = "(" + string.Join(", " , rowIds.Select(s => $"'{s}'")) + ")";

Answer 2

Since you seem open to other approaches, do it with QueryFirst. In your .sql file, put

DELETE FROM db.T0590_CODE_STRING WHERE id IN (@IdsToDelete)

QueryFirst will detect the parameter, then generate a repository class with execute methods, correctly inferring that you want a list of ints, not a single value. The C# type will automatically reflect the underlying db type, normally int (but not always). There's no string manipulation, no sql injection, and your sql is continuously validated.

• disclaimer - I wrote QueryFirst