0

I have an one-to-many relationship defined with EntityFramework code-first. Something like: BigEntity that contains a SmallEntitiesList (list o of SmallEntity).

Whenver I update the SmallEntities list of that object, and I perform a dbContext.SaveChanges(), I can see in the SQL logger that Entity Framework inserts those items by making a roundtrip to database for each one.

So the log is looking something like this:

Log for inserting SmallEntities

Each of these inserts looks like this:

DECLARE
  updatedRowid ROWID;
BEGIN


INSERT INTO SOME_TABLE(...)
VALUES (...)

RETURNING ROWID INTO updatedRowid;
OPEN '' /* @outParameter */ FOR SELECT
     SOME_TABLE 
FROM SOME_TABLE 
WHERE ROWID = updatedRowid;
END;

Is there a way to make entity framework to behave differently and make these inserts making a trip to database for each one?

UPDATE: Already tried BulkInsert (there is no support for Oracle DevArt, which is what I am using).

user2997779
  • 297
  • 1
  • 16

3 Answers3

1

if you are using EF(version 6) 6.1.3 then you can achieve this with EntityFramework.BulkInsert extension provided . It will insert the list of object in single call which will improve application performance very much . Form more information check this out .

https://efbulkinsert.codeplex.com/

Yashveer Singh
  • 1,914
  • 2
  • 15
  • 23
  • Unless you fixed it, this won't work with the graph hierarchy. It'll only insert one level. You also need to be careful with the Bulk Insert options as if you aren't careful, it can bypass a lot of rules on you. – Daniel Lorenz Jan 16 '17 at 20:09
  • @DanielLorenz sorry I didnt get your point . Can you please tell me where I can read more about what you said . Also the question was to insert bulk insert for single Entity and I think BulkInsert is good for that . – Yashveer Singh Jan 16 '17 at 20:12
  • "I have an one-to-many relationship defined with EntityFramework code-first" This means he is saving a parent entity and its children entities at the same time. Your bulk insert solution will only insert the parent entity and not the child ones automatically. Thus, you'd have to loop through the tree manually which would could cause issues in the future. – Daniel Lorenz Jan 16 '17 at 20:13
  • @DanielLorenz and did you have any any issues with BulkInsert ? – Yashveer Singh Jan 16 '17 at 20:15
  • I think you are missing the point. If he just used BulkInsert in one line of code, it wouldn't insert his child entities. Also, if he doesn't know what those BulkInsert options do, it may skip validation rules when inserting. I tend to use the built in Sql Bulk Copy directly when I know i'm only working with one level of entities. If I work with multiple levels, I try to avoid inserting multiple parents with multiple children at the same time to avoid huge delays. – Daniel Lorenz Jan 16 '17 at 20:19
  • @Yashveer Singh I've tried using bulkInsert. As far as I researched, it doesn't support Oracle devArt provider (which is exactly the one that I am using). I've tried also other extensions, like entity-framework extensions (http://entityframework-extensions.net/), but I currently encounter some issue (same provider issue like the BulkInsert). – user2997779 Jan 16 '17 at 21:10
  • @DanielLorenz Stil, no problem that it doesn't support relations, I can work with that and insert the items separately, but there is no support for devArt Oracle provider. – user2997779 Jan 16 '17 at 21:20
0
foreach(var item in ListItems)
{
  context.Entry(item).State = System.Data.EntityState.Added;
  context.Add(item);
}

context.SaveChanges();

OR

foreach (var item in objectList)
{
  context.YourObject.AddObject(item );
}

context.SaveChanges(SaveOptions.None);

OR

Research - BulkInsert();

TdAth
  • 26
  • 4
  • Both of your solutions are doing the same, there is no change in the result the same number of trips to database are being made for each entity. – user2997779 Jan 17 '17 at 07:56
  • Check this out. http://stackoverflow.com/questions/36209302/would-it-be-best-to-call-context-savechanges-only-once – TdAth Jan 26 '17 at 10:22
  • Or just use a stored procedure :) – TdAth Jan 26 '17 at 10:23
0

Disclaimer: I'm the owner of Entity Framework Extensions

This library support all major providor including:

  • Oracle DevArt
  • Oracle DataAccess
  • Oracle DataAccessManaged

This library allows you to perform all bulk operations you need for your scenarios:

  • Bulk SaveChanges
  • Bulk Insert
  • Bulk Delete
  • Bulk Update
  • Bulk Merge

Example

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);

// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);

// Customize Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});
Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60