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

Question

I'm migrating an application from SqlClient to the Entity Framework 4, working with SQL Server. I have a situation where I have to copy several rows from one table to another, so I do it with an INSERT ... SELECT, as 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 accomplish something remotely similar to this with the Entity Framework, or would I have to get all of the rows from Table2, and insert them row by row in Table1? How could I handle the GETDATE()?
Tks

Search for Batch Insert, Bulk Insert or Bulk Copy in EF, although ORM is not for that — Jahan Zinedine, Jan 13 '11 at 19:14
Actually, most of the posts about batch inserts are about sending several INSERTs from the client to the server in a batch. What I want is quite different. — Pascal, Jan 13 '11 at 19:30
Take a look at http://blogs.microsoft.co.il/blogs/aviwortzel/archive/2008/05/06/implementing-sqlbulkcopy-in-linq-to-sql.aspx — Jahan Zinedine, Jan 13 '11 at 20:11

score 2 · Accepted Answer · answered Jan 13 '11 at 19:40

Put the sql in a stored procedure, and then call that stored procedure from your app - I'd just use plain sql client to make the call to execute the proc, but no reason you can't map it into your EF model if you really wanted to and then call it from EF.

You can have it return a value if you want/need to.

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

No this will not work in EF. EF will load all selected data from DB to your application materializing them as objects and insert these objects one by one to second table. EF is unable to do batch operations at all.

edited May 23 '17 at 12:15

Community

1
1

answered Jan 13 '11 at 19:33

Ladislav Mrnka

360,892
59
660
670

score 0 · Answer 3 · answered Jan 13 '11 at 21:06

0

If you can use the new CTP5 Feature release of EF4, this now allows raw SQL queries and commands to be executed via the SqlQuery & SqlCommand methods on DbContext.Database.

answered Jan 13 '11 at 21:06

Clicktricity

4,171
1
23
15

score 0 · Answer 4 · answered Jan 13 '11 at 23:09

No, EF4 does not support bulk operations.

In your scenario, i would create a User Defined Table Type in your database, which mimics the dbo.Table1 table.

Create a stored procedure which accepts that 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 that from regular ADO.NET.

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

4 Answers4