Entity Framework bulk inserts causing a select n+1 problem

I'm inserting a number of instances of a particular object to a database using the Entity Framework. I have a single object context to which I attach several 'Product' objects. The object I insert has no relationship to any other table in the database. It is purely a standalone entity. I am using the 'EFProf' tool to profile the application.

When I call 'SaveChanges()' to persist my 'Product' entities to SQL Server I am warned by EFProf that I have the 'Select N+1' anti-pattern. I don't see how this is possible since I am simply inserting. My understanding of 'Select N+1' is that it relates to inefficient object retrieval. I am not retrieving anything, only inserting.

When I examine the generated SQL, I see that Entity Framework has generated a select statement on my behalf which returns the Id of the newly inserted object. This select statement is performed for every entity I insert. Could this be the cause of the select N+1 problem? If so, how can I avoid this anti-pattern when inserting a number of entities of the same type in a single call to SaveChanges()?

The generated SQL is below:

insert [dbo].[Products]
  ([ProductName],
   [ProductNum],
   [Price],
   [EntryDate],
   [Description],
   [Category],
   [UnitsInStock])
values('TestProduct' /* @0 */,
   0 /* @1 */,
   0 /* @2 */,
   '2011-07-09T17:14:49.00' /* @3 */,
   'Category: Test Products - Name TestProduct' /* @4 */,
   'Test Products'开发者_如何转开发 /* @5 */,
   0 /* @6 */)


select [Id]
from   [dbo].[Products]
where  @@ROWCOUNT > 0
       and [Id] = scope_identity()

---

You cannot avoid that additional select. Once you are using autogenerated Ids in the database and your model is correctly configured for them EF will always create this additional select.

You don't need to bother. Performance of "bulk insert" with EF is so terribly bad that this additional select during each insert means nothing. What should you deal with is the processing itself because EF will create separate roundtrip to the database for each inserted record and that is the problem. If you really want to pass large number of object into database, you want to do it often (it is not one time job) and you expect some performance you should definitely look for other non-EF solution. For example already mentioned SqlBulkCopy.

If you just need to insert few instances in some operation you just let it be. That is how EF works.

---

There is no direct support for bulk inserts using the Entity Framework. You can, however, adapt it to use SQLBulkCopy. This MSDN Article is a good example.

---

If you're trying to do a bulk insert, it seems to me that SCOPE_IDENTITY() is useless, since it stores a single scalar value - not a range. I'm going to betray my ignorance of EF, but does it convert this code for to BULK INSERT command, or SQLBulkCopy, or something else?

If you're going to do one insert at a time, then checking @@ROWCOUNT first instead of putting in the where clause seems to be a better pattern in general:

IF @@ROWCOUNT = 1
BEGIN
    SELECT [Id]
        FROM [dbo].[Products]
        WHERE [Id] = SCOPE_IDENTITY();
END

Or not even bothering at all, since if the following resultset is empty, you now know what the @@ROWCOUNT was:

SELECT [Id]
    FROM [dbo].[Products]
    WHERE [Id] = SCOPE_IDENTITY();