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.

karagoz · Accepted Answer · 2023-04-14 07:53:18Z

1

You can change this line

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

answered Apr 14, 2023 at 7:53

karagoz

1355 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bouyalife Over a year ago

var list = "(" + string.Join(", " , rowIds.Select(s => $"{s}")) + ")"; This worked. Ty!

bbsimonbb · Accepted Answer · 2023-04-14 14:25:31Z

1

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

edited Apr 14, 2023 at 14:25

answered Apr 14, 2023 at 13:50

bbsimonbb

29.3k18 gold badges92 silver badges129 bronze badges

Collectives™ on Stack Overflow

How can i write raw SQL commands in EntityFramework 6

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related