Following are some scenarios where EF will not work or perform as you would expect your data layer to:
a) Bulk Insert, Update or Delete
b) Provide locking hints in your queries
Why entity framework is not the right choice when you need bulk insert, update or delete?
One of the biggest problems with EF is that it does not support bulk queries. If you add more than one entity for insert, update or delete, the context internally will make as many round trips as the number of entities added to it for insert, update or delete. Imagine if you are dealing with thousands of records to insert, update or delete, EF will make that many round trips to SQL Server which is not good. If you write your own SQL Bulk copy query, the entire data is transferred in a single go which will greatly improve your app’s performance.
EF Example:
Demo Model:
Person is a table that I added in my DemoModel.edmx
Code for adding Person Entities:
Queries captured through SQL Server Profiler:
1) EXEC SP_EXECUTESQL N’INSERT [DBO].[PERSON]([FIRSTNAME], [MIDDLENAME], [LASTNAME], [AGE], [COUNTRY], [GENDER]) VALUES (@0, NULL, @1, @2, @3, @4) SELECT [PERSONID] FROM [DBO].[PERSON] WHERE @@ROWCOUNT > 0 AND [PERSONID] = SCOPE_IDENTITY()’,N’@0 NVARCHAR(64),@1 NVARCHAR(64),@2 INT,@3 NVARCHAR(128),@4 VARCHAR(16)’,@0=N’JOHN’,@1=N’BROWN’,@2=28,@3=N’USA’,@4=’M’
2) EXEC SP_EXECUTESQL N’INSERT [DBO].[PERSON]([FIRSTNAME], [MIDDLENAME], [LASTNAME], [AGE], [COUNTRY], [GENDER]) VALUES (@0, NULL, @1, @2, @3, @4) SELECT [PERSONID] FROM [DBO].[PERSON] WHERE @@ROWCOUNT > 0 AND [PERSONID] = SCOPE_IDENTITY()’,N’@0 NVARCHAR(64),@1 NVARCHAR(64),@2 INT,@3 NVARCHAR(128),@4 VARCHAR(16)’,@0=N’MICHELLE’,@1=N’BROWN’,@2=28,@3=N’USA’,@4=’F’
As you can see, there are two queries fired by entity framework for two person objects. The other point worth taking note is because Person has an identity column “PersonId”, EF also makes a select query to get the id back which is additional work if you really don’t need that id.
SQL Bulk Copy Example: Using same database and same table.
Bulk Copy code:
Queries Captured in SQL server profiler:
INSERT BULK PERSON ([FIRSTNAME] NVARCHAR(64) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, [MIDDLENAME] NVARCHAR(64) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, [LASTNAME] NVARCHAR(64) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, [AGE] INT, [COUNTRY] NVARCHAR(128) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS, [GENDER] VARCHAR(16) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS)
If you notice carefully, the query generated is a bulk insert in person which inserts both the rows at a single go.
Conclusion:
If you have large amounts of data on which you want to perform bulk queries, entity framework does not support that and it will make round trips to the SQL server which can have a huge performance impact. Therefore, your data access layer should have a right mix of Entity Framework and SQL Bulk queries based on your requirements.
I would prefer or suggest to use Hybrid of EF and ADO.NET .It's depend on the requirement what to use EF or ADO.NET.
LikeLike