Can I do an INSERT with SELECT equivalent in Entity Framework 4

Question

I am migrating an application from SqlClient to Entity Framework 4 while working with SQL Server. I have a situation where I have to copy multiple rows from one table to another, so I do this with INSERT ... SELECT, as shown below:

INSERT INTO dbo.Table1 (Reg1, Reg2, Reg3, Reg4, Reg5, Reg6, Reg7, Reg8) SELECT Reg1, Reg2, Reg3, Reg4, Reg5, @Reg6, GETDATE(), @Reg8 FROM dbo.Table2 WHERE Reg1 = @Reg1 

Can I do something remotely similar to this with the Entity Framework, or will I need to get all the rows from table2 and insert them row by row in table1? How can I handle GETDATE ()?
Tks

Answer 1

Put sql in a stored procedure and then call this stored procedure from your application - I would just use a simple sql client to make a call to execute proc, but there is no reason why you cannot map it to EF if you really wanted to, and then call it from EF.

You can return it if you want / need.

Answer 2

No, this will not work in EF. EF will load all the selected data from the database into your application, materializing them as objects and inserting these objects in turn into the second table. EF cannot perform batch operations at all .

Answer 3

If you can use the new version of CTP5 Feature EF4, now it allows you to execute raw SQL queries and commands using the SqlQuery and SqlCommand methods in DbContext.Database.

Answer 4

No, EF4 does not support bulk operations.

In your scenario, I will create a user-defined table type in your database that mimics the dbo.Table1 table.

Create a stored procedure that accepts this UDT and performs the insert:

 INSERT INTO dbo.Table1 (Reg1, Reg2, Reg3, Reg4, Reg5, Reg6, Reg7, Reg8) SELECT Reg1, Reg2, Reg3, Reg4, Reg5, @Reg6, GETDATE(), @Reg8 FROM @UdtPassedIn 

And call it from regular ADO.NET.