Entity Framework- Is it the solution for all data access requirements?

With latest entity Framework (EF) releases and with the entire world moving to Object Relational Mappings for all the data access needs, it is quite normal for a developer to think that EF is the right choice before starting to design the data access layer. The latest release by Microsoft has made it easy for the developers to code and get started with Entity Framework. It is when someone starts writing the actual code that they realize that just EF may not actually be the right choice for all requirements.

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:

private static void EFDemo()

        {

            DemoEntities context = new DemoEntities();

            context.People.Add(new Person

            {

                FirstName = “John”,

                LastName = “Brown”,

                Age = 28,

                Country = “USA”,

                Gender = “M”

            });

            context.People.Add(new Person

            {

                FirstName = “Michelle”,

                LastName = “Brown”,

                Age = 28,

                Country = “USA”,

                Gender = “F”

            });

            context.SaveChanges();

        }

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:

private static void BulkCopyDemo(string connectionString)

        {

            using (SqlConnection connection = new SqlConnection(connectionString))

            {

                connection.Open();

                DataTable personTable = CreateTableStructureUsingTable(“Person”, connection);

                AddPersonRow(new Person

                {

                    FirstName = “John”,

                    LastName = “Brown”,

                    Age = 28,

                    Country = “USA”,

                    Gender = “M”

                }, personTable);

                AddPersonRow(new Person

                {

                    FirstName = “Michelle”,

                    LastName = “Brown”,

                    Age = 28,

                    Country = “USA”,

                    Gender = “F”

                }, personTable);

                SqlBulkCopy inserter = new SqlBulkCopy(connection);

                inserter.DestinationTableName = “Person”;

                inserter.WriteToServer(personTable);

            }

        }

        private static void AddPersonRow(Person p, DataTable personTable)

        {

            var row = personTable.NewRow();

            row[“FirstName”] = p.FirstName;

            row[“LastName”] = p.LastName;

            row[“Age”] = p.Age;

            row[“Country”] = p.Country;

            row[“Gender”] = p.Gender;

            personTable.Rows.Add(row);

        }

        private static DataTable CreateTableStructureUsingTable(string table, SqlConnection connection)

        {

            DataTable dt = new DataTable();

            using (SqlCommand command = new SqlCommand())

            {

                command.Connection = connection;

                command.CommandType = CommandType.Text;

                command.CommandText = “select TOP 0 * from “+ table;

                using (SqlDataReader dataReader = command.ExecuteReader())

                {

                    dt.Load(dataReader);

                }

            }

            return dt;

        }

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.