ADO.NET Entity Framework Custom SQL Expressions for Inserts/Updates/Deletes

views:

965

answers:

+1 Q:

ADO.NET Entity Framework Custom SQL Expressions for Inserts/Updates/Deletes

Hi,

In ScottGu's blog, he shows how to override methods of Linq to SQL classes so that modifications to data can be overriden in .NET side customly.

http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx (Custom SQL Expressions for Inserts/Updates/Deletes part)

Is there a way to accomplish same functionality in EF?

+1 A:

EF allows you to map these operations to Stored Procedures on the mapping tab in the designer. You can then map the input and output values for the stored procedure to the various properties on the object.

Hope that helps,

LorenVS 2009-08-18 21:15:23

yes, but i am looking for a implementation on .net side (like examples in address)

paul simmons 2009-08-18 21:17:22

The entity framework supports this sort of concept for selecting, but not for updating, inserting and deleting (to the best of my knoweledge). If you want to use custom sql scripts for these operations, you need to use stored procedures.

LorenVS 2009-08-18 21:32:39

+3 A:

For EF 3.5 you don't really have an option, other than sprocs.

But for EF 4.0 we have added a new method to the ObjectContext called ExecuteStoreCommand(..) and related methods etc.

So you could override SaveChanges(), it is now virtual, and interograte the ObjectStateManager looking for ObjectStateEntries for the type(s) you are interested in, that are in the EntityState you are interested in (i.e. Inserted) etc.

Once found you could then Execute commands directly against the database using the new ExecuteStoreCommand() method. Then by calling AcceptChanges() on the ObjectStateEntry you stop the EF from trying to flush changes to the database that you have already handled.

Then you can let the EF do the rest of the changes for you by calling base.SaveChanges().

I know this is not ideal. But it is the best workaround that I can think of

Alex

Alex James 2009-08-19 05:55:28

thanks. just to sum up and clarify for myself; let me split my question into 2: 1) is it possible to send custom native sql to database with ef? 2) is it possible to override some methods and customize data update/insert/delete process? answer for 1) not for 3.5, but for 4 with executestorecommand(). answer for 2) override SaveChanges and do what you want. however for 3.5 cannot execute native sql, but do anything else. for 4 do everything. correct?

paul simmons 2009-08-19 17:16:53

Yeap basically.Although in fact you can probably execute native SQL in 3.5 too. because you can get the StoreConnection from the EntityConnection that resides under the ObjectContext. The only thing you have to worry about is transactions (both in 3.5 and 4.0) but EF does work well with TransactionScope so even that is possible.

Alex James 2009-08-19 22:53:26

ansaurus

tags:

views:

answers:

ADO.NET Entity Framework Custom SQL Expressions for Inserts/Updates/Deletes

related questions