CODEWITHSUNDEEP
sql-serverentity-frameworkentity-framework-4
Pascal
Pascal

Reputation: 2984

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

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

Upvotes: 7

Views: 4575

Answers (4)

RPM1984
RPM1984

Reputation: 73123

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.

Upvotes: 0

Clicktricity
Clicktricity

Reputation: 4209

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.

Upvotes: 0

E.J. Brennan
E.J. Brennan

Reputation: 46879

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.

Upvotes: 2

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

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.

Upvotes: 1

Related Questions