CODEWITHSUNDEEP
c#entity-frameworkado.netsql-server-2008-r2.net-4.5
Amit Rai Sharma
Amit Rai Sharma

Reputation: 4225

Replacing ADO.NET with Entity Framework. Is Entity Framework suitable for following scenario?

Question 1: Should we use Entity Framework in a multi-user environment to implement a functionality which performs multiple updates (100 rows) on a table?

Question 2: Are we taking a right decision to use EF in following scenario?

Scenario: Stock Count webpage contains a editable gridview control which allows users to update 100 rows at a time.

Background: Stock Count is used to record stock quantities at period closure. The period closure for all organisation happens on Monday. There are around 10 live organisation and on average each organisation has at least 100+ site which does the stock closure on Monday at same time.

A similar screen which is based on ADO.NET is working fine. However the team is trying to introduce Entity Framework on the new screen. We did method level stress test and didn't got promising results for EF. We ran the tests for 2 minutes and simulated a user load from 5 users to 35 users.

Here are the results:

ADO.NET uses a stored procedure to make one database update at a time.

5 Users - Number of test per second per user - 80
35 Users - Number of test per second per user - 150

EF uses SaveChanges() method, which internally make one database update at a time.

5 Users - Number of test per second per user - 43
35 Users  - Number of test per second per user - 43

EF code:

Approach 1

context.Configuration.AutoDetectChangesEnabled = false;
context.Set<LocationData>().AddRange(data); //data is a collection of LocationData
foreach (var locationData in data)
{
    var entry = context.Entry(locationData);
    entry.State = EntityState.Modified;
}
context.SaveChanges();

Code profiler details for approach 1

enter image description here

Approach 2

context.Configuration.AutoDetectChangesEnabled = false;
foreach (var locationData in data)
{
    context.Set<LocationData>().Attach(data); 
    var entry = context.Entry(locationData);
    entry.State = EntityState.Modified;
}
context.SaveChanges();

Code profiler details for approach 2

enter image description here

SQL Query generated by Entity Framework

exec sp_executesql N'
UPDATE [dbo].[LocationData]
SET [LocationId] = @0, [ClientId] = @1, [ProductId] = @2, 
[SupplierProductId] = @3, [MenuId] = @4, [MenuNumber] = @5, [CaskNumber] = @6
WHERE ([Id] = @7)',N'@0 bigint,@1 bigint,@2 bigint,@3 bigint,@4 bigint, @5 bigint,@6 nvarchar(64),@7 bigint',
@0=22371851,@1=22371851,@2=22371851,@3=22371851,@4=22371851,@5=22371851,@6=N'Cask0091',@7=22371851

SQL Query generated by ADO.NET

Update LocationData
set 
 LocationId = @LocationId, 
 ClientId= @ClientID,  
 ProductId= @ProductId, 
 SupplierProductId=@SupplierProductId,  MenuId= @MenuId, 
 MenuNumber= @MenuNumber, 
 CaskNumber= @CaskNumber
WHERE   LocationData.Id = @Id

These test were not executed under a transaction.

I have used following for the development: Visual Studio 2012 Ultimate, Sql Server R2, Entity Framework 6.0.2, .NET 4.5,C#

Upvotes: 3

Views: 2034

Answers (1)

user817530
user817530

Reputation:

Nope. Entity Framework simplifies large model architecture, and it auto-generates SQL to do the hard "complex" bits. If you're using EF for a simple read/write utility-type purpose you are better off with ADO.NET. If you're looking to standardize and simplify the way a group of developers use your SQL Server, you should use entity framework.

Upvotes: 1

Related Questions