none
Bulk Insertion deletion queries using Entity Framework RRS feed

  • General discussion

  • Hi All,

    I am developing a HR system which uses Entity Framework. The previous version of this system uses ADO.Net and has very complex and huge queries of all types. One of the task that i want to achieve in new system is to write the query that insert or delete data in bulk without looping or any other funny logic

    For Example:

    INSERT INTO tableB (Column1, Column2, Column3) VALUES (SELECT Column1, Column2, Column3 FROM tableA)

    The above simple SQL query is simple to execute with ADO.Net but is it also simple with Entity Framework.

    Also if i want to execute queries with dozens of JOINs and calculations than is it easy to write it using LINQ..? Please give me an example.

    Thanx ALL...

    Thursday, November 17, 2011 6:29 AM

All replies

  • Hi,

    Currently there isn't any support for bulk work in EF. EF isn't intended to do that work type of work. (I have seen it mentioned in some blog posts that it is thought of, but it isn't stated that it will come in any near future (read: .NET 4.5)) If you need to do like your example there, you could do this in a stored procedure which you maps to a function in your EF model. An other option is to use ObjectContext.ExecuteStoreCommand and use classic SQL (this will however make your code quite static to DMBSes that supports your query).

    If you want to use LINQ to define complex queries, you can do this, however the time it takes for EF to generate the correct store command for this takes long time. But to counter that you can use Compiled Queries (http://msdn.microsoft.com/en-us/library/bb896297.aspx). It's a bit difficult to give an example right out of nowhere on that.

    I hope this answers some of your questions! Feel free to ask more :)


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, November 17, 2011 5:57 PM
  • On 11/17/2011 1:29 AM, Raza Haider wrote:
    > Hi All,
    >
    > I am developing a HR system which uses Entity Framework. The previous
    > version of this system uses ADO.Net and has very complex and huge
    > queries of all types. One of the task that i want to achieve in new
    > system is to write the query that insert or delete data in bulk without
    > looping or any other funny logic
    >
    > For Example:
    >
    > *INSERT INTO tableB (Column1, Column2, Column3) VALUES (SELECT Column1,
    > Column2, Column3 FROM tableA)*
    >
    > The above simple SQL query is simple to execute with ADO.Net but is it
    > also simple with Entity Framework.
    >
    > Also if i want to execute *queries with dozens of JOINs and
    > calculations* than is it easy to write it using *LINQ*..? Please give me
    > an example.
    >
     
    That's why EF has Entity-SQL (read only).
     
     
    Maybe you can use the backdoor on bulk insert and delete.
     
     
    Thursday, November 17, 2011 6:22 PM