How to delete DB records without loading them into memory in LINQPad?

svonidze 0
Viewed 60

LINQPad allows to delete records from DB like this

var ids = new [] { 1, 2, 3 ... N };
var records = Table.Where(t => ids.Contains(t.Id)).ToList();

Table.DeleteAllOnSubmit(records);
SubmitChanges();

However it is overkill to load N records if needed to delete by Ids.

Installing EF or some other package? No native way?

@JoeAlbahari?

linqpad c#
2 Answers

Create entities with these IDs and attach them to the table. After make a deletion query.

var ids = new[] { 1, 2, 3... N };

var records = ids.Select(x => new Record { Id = x }).ToArray(); // entity in Table

Table.AttachAll(records);

Table.DeleteAllOnSubmit(records);
SubmitChanges();
Alexander Petrov0

You're not actually loading everything in your example. The SQL isn't executed against the database until you call ToList(), at which point all the previous conditions are taken into account when generating the SQL statement. In your example, it probably transforms into something like

SELECT id, ... FROM Table where id in (1, 2, 3)

Meaning (assuming ids are unique) you only get three rows, no matter how many rows are in the actual table.

If you only have a small list of IDs, you can enter straight SQL into LinqPad by selecting it in the language dropdown.

delete from Table where id in (1, 2, 3);

Or you can generate a SQL delete statement as a string and execute it against the database using ExecuteCommand.

I'm afraid I've not found any easier way of deleting records than that.

RoadieRich0
Related