Move records to another table using LINQ to SQL

Question

I have suddenly faced a problem with moving some records in a SQL Server database from one table to another, using LINQ to SQL. Is it possible to write in LINQ to SQL a query just as simple as this:

INSERT INTO Table2 SELECT * FROM Table1 WHERE Selected = 1
GO

DELETE FROM Table1 WHERE Selected = 1
GO

without using loops and collections?

Answer 1

You absolutely don't need LINQ here. Consider performance & maintainability implications of issuing two SQL statements (or invoking a sproc) versus having to load potentially thousands of objects and then saving them back again.

Resort to SqlCommands. That will be your best choice.

Answer 2

THis is just a simple insert and delete method which should written together.

One insert necessary fields to second table and when its finished, the second statement will remove the fields from first Table

Answer 3

I'm not aware of a way to move rows in Linq2Sql without a loop. :)

This kind of thing is much easier in SQL. You can use the output into clause to move rows in a single statement:

delete from Table1
output deleted.id, deleted.name into Table2
where Selected = 1