Improving the performance of slow Entity Framework queries

I personally find that 95% of my Entity Framework queries before completely fine, it's only a small percentage that I need to tweak with this methodology. These queries are the ones that require a lot of related data to the main model that I am querying.

An example query with Entity Framework

Here is an example of a slow performing query with Entity Framework. I'm starting at a Customer model and I need to also retrieve data that is related one-to-one with the customer, but more importantly, one-to-many with the customer and to make matters even worse there are further one-to-many of the already one-to-many! It's a lot of data, but it's the necessary evil of working with a large database.

The example code below is performing LINQ statements against my database. I have an extensive framework of code that allows me to execute a function called Get that excepts my filter criteria and a list of models that I want to include. Here is an example with a lot of joins.

var customers = Get(c => c.AccountId == 7, c => c.Settings, c => c.EmailSettings, c => c.Albums.Select(a => a.Photos));

I could go on with more tables, but this query will execute with a lot of LEFT JOINS including sub selects. It can end up quite nasty.

Converting to a stored procedure

Rather than letting Entity Framework build an unnecessarily complicated query because of the amount of data I need, I am going to write a stored procedure that returns the same data with (what I like to call) tighter, more specific queries. Sometimes Entity Framework performs left joins when I know they can be inner joins. Writing your own queries (on the very rare occasions that I need to) help solve this problem.

CREATE PROCEDURE [dbo].[usp_GetFullCustomers]
	@AccountId bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT 
		*
	FROM Customers WHERE AccountId = @AccountId
	SELECT
		s.*
	FROM Settings s
	INNER JOIN Customers c ON c.Id = s.CustomerId
	WHERE c.AccountId = @AccountId
	SELECT
		s.*
	FROM EmailSettings s
	INNER JOIN Customers c ON c.Id = s.CustomerId
	WHERE c.AccountId = @AccountId
	SELECT
		a.*
	FROM Albums a
	INNER JOIN Customers c ON c.Id = a.CustomerId
	WHERE c.AccountId = @AccountId
	SELECT
		p.*
	FROM Photos p
	INNER JOIN Albums a ON a.Id = p.AlbumId
	INNER JOIN Customers c ON c.Id = a.CustomerId
	WHERE c.AccountId = @AccountId
END

This stored procedure will return all of this data about all customers for a specific account ID. You may notice that I perform one select per object. In this stored procedure 5 result sets will performed. I'll show you how this is handled next.

Mapping your data with ObjectContext.Translate

With my stored procedure created, I need to write some code that executes the stored procedure. With the results of the procedure I use ObjectContext.Translate to map the data to my Entity Framework objects.

        public List GetFullCustomers(long accountId)
        {
            var customers = new List();
            using (var cmd = Context.Database.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "[dbo].[usp_GetFullCustomers]";
                cmd.Parameters.Add(new SqlParameter("AccountId", accountId));
                bool shouldCloseConnection = true;
                try
                {
                    if (db.Database.Connection.State != ConnectionState.Open)
                        db.Database.Connection.Open();
                    else
                        shouldCloseConnection = false;
                    using (var reader = cmd.ExecuteReader())
                    {
                        var objectContext = ((IObjectContextAdapter)db).ObjectContext;
                        customers = objectContext.Translate(reader, "Customers",
                            MergeOption.AppendOnly).ToList();
                        if (customers.Any())
                        {
                            reader.NextResult();
                            objectContext.Translate(reader, "Settings",
                                MergeOption.AppendOnly).ToList();
                            reader.NextResult();
                            objectContext.Translate(reader, "EmailSettings",
                                MergeOption.AppendOnly).ToList();
                            reader.NextResult();
                            objectContext.Translate(reader, "Albums",
                                MergeOption.AppendOnly).ToList();
                            reader.NextResult();
                            objectContext.Translate(reader, "Photos",
                                MergeOption.AppendOnly)
                                .ToList();
                        }
                    }
                }
                finally
                {
                    if (shouldCloseConnection)
                        db.Database.Connection.Close();
                }
            }
            return customers;
        }

I've taken a lot of liberties with the above code as hopefully your Entity Framework implementation is configured with a variety of models that you understand how this theory could be applied to your code.

The key process that is happening is I am mapping each result of the stored procedure using objectContext.Translate(reader, "Albums", MergeOption.AppendOnly).ToList(); to convert it to my Entity Framework model.

Please remember this is mostly theory based code and I do not recommend writing all of your own EF queries, just the ones that require an immense of data that EF does not know how to fetch intelligently. Good luck.

Published on Apr 18, 2019

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users | Optimization | Entity Framework Tutorials For Beginners and Professionals