Timeout exception running Linq Statement

Question

This just started happening. This statement was working for months, now I just keep getting the timeout error below. when I execute the same statement directly on the SSMS it comes back in a second. the table has 44k records and is index on 5 columns state being one of them.

select distinct(state) from [ZipCodeDatabase]

I am running the following linq statement

states = ZipCodeRepository.Get(orderBy: z => z.OrderBy(o => o.State)).Select(z => z.State).Distinct().ToList();

When I run this linq statement I am continually getting a timeout error and have no idea y since it was working correctly before.

I included the Get() function which is a generic repo function, but maybe I am missing something there, that is causing the delay.

Get Function:

public virtual IEnumerable<TEntity> Get(
Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
string includeProperties = "") //params Expression<Func<TEntity, object>>[] includes
{
    IQueryable<TEntity> query = dbSet;
    if (filter != null)
    {
        query = query.Where(filter);
    }
    foreach (var includeProperty in includeProperties.Split
        (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
    {
        query = query.Include(includeProperty);
    }

    if (orderBy != null)
    {
        return orderBy(query).ToList();
    }
    else
    {
        return query.ToList();
    }
}

The timeout error:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryCloseInternal(Boolean closeReader) at System.Data.SqlClient.SqlDataReader.Close() at System.Data.Common.DbDataReader.Dispose(Boolean disposing) at System.Data.Common.DbDataReader.Dispose() at System.Data.Common.Internal.Materialization.Shaper1.Finally() at System.Data.Common.Internal.Materialization.Shaper1.SimpleEnumerator.Dispose() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at ClientsToProfitsKendoUI.DAL.GenericRepository1.Get(Expression1 filter, Func2 orderBy, String includeProperties)

Answer 1

Your get method always calls ToList, which materializes the query at that point. Meaning that it will grab all 44k records from the database before doing any filtering.

You're also doing the ordering by state before actually selecting any data, which is a useless exercise in ordering arbitrary data.

From your Get method, remove the ToList calls. And optimize your query by first selecting the State, then calling Distinct and then ordering. This allows the database to do its thing optimally.

If you need the list fully materialized, call ToList at the very end. But until that, keep using the IQueryable which your query returns. That way you can let Entity Framework offload as much as possible to the database.