ToList() taking to long when converting an IEnumerable with multiple objects

Question

I have the following classes:

public class CountryVM
    {
        #region Properties
        public int CountryID { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        #endregion

        #region Constructors
        /// <summary>
        /// Constructor
        /// </summary>
        public CountryVM()
        {

        }

        /// <summary>
        /// Constructor that creates a View Model based of an Entity object
        /// </summary>
        /// <param name="countryCode">Fills data to View Model</param>
        public CountryVM(Country_Code countryCode)
        {
            if (countryCode != null)
            {
                CountryID = countryCode.Country_Code_ID;
                Code = countryCode.Country_Code1;
                Name = countryCode.Country_Name;
            }
        }
        #endregion
    }


public class StateVM
    {
        #region Properties
        public int StateID { get; set; }
        public int CountryID { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }

        public CountryVM Country { get; set; }
        #endregion

        #region Constructors
        /// <summary>
        /// Constructor
        /// </summary>
        public StateVM()
        {

        }

        /// <summary>
        /// Constructor that creates a View Model based of an Entity object
        /// </summary>
        /// <param name="stateCode">Fills data to View Model</param>
        public StateVM(State_Code stateCode)
        {
            if (stateCode != null)
            {
                StateID = stateCode.State_Code_ID;
                CountryID = stateCode.Country_Code_ID;
                Code = stateCode.State_Code1;
                Name = stateCode.State_Name;

                Country = new CountryVM(stateCode.Country_Code);
            }
        }
        #endregion
    }

Both Country_Code and State_Code are tables from my tables converted to Entity objects.

I run the following lines of code:

    IEnumerable<State_Code> entityList = _stateRepository.GetAllStateCodes();
    IEnumerable<StateVM> viewModelList = entityList.Select(s => new StateVM(s));
    viewModelList = viewModelList.ToList();

When running

viewModelList = viewModelList.ToList(); 

it takes between 1 to 3 seconds. I was experimenting with it and I removed the:

Country = new CountryVM(stateCode.Country_Code);

from the StateVM object, and it would run flawlessly.

So, I guess the reason it takes too long it's because once StateVM is instantiated, a CountryVM will be instantiated inside.

Is there way that I can improve the performance?

Answer 1

Is there way that I can improve the performance?

The best way to improve performance is to create a view that has just the columns you actually need, including any joins. This will allow the database to work more efficiently and gives you the option to use covering indexes for even better performance.

The second best option is to use .Include, as explained in Jonathon Chase's answer.

Answer 2

Running ToList() will always enumerate the collection. What you're running into is all of the lazily loaded properties that link to other tables executing the generated SQL statements to pick up the attached data properties. If you do not need them, you should filter down your collection until you are sure you have the minimal amount of data required to go forward. If you know you will need them, you can use eager loading to instead load everything in the original request, rather than on an object to object to property to property basis. Take a look at ObjectQuery.Include

Answer 3

If you use Visual Studio 2013 Premium (or later equivalent), keep an eye on the Diagnostic Tools debugger window whilst your code is executing. The trace will give you a good idea of what is happening when you retrieve your entities.

A common mistake is to rely on the default lazy-loading behaviour of Entity Framework when entities have relationships to other entities that will navigated. In your case, it is likely that N+1 SELECT statements are executed when you exercise the query -- one to get all the state codes, and one for each country code (both State_Code and Country_Code appear to be entities of their own).

Please refer to Entity Framework Loading for extra tips. You can also search for "Entity Framework N+1", as this is a common cause of performance issues.