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 Get( Expression> filter = null, Func, IOrderedQueryable> orderBy = null, string includeProperties = "") //params Expression

Accepted Answer

You're materializing every row in the database just to select distinct states. Let the database do the heavy lifting for you and simply return the states rather than materializing everything and then having .Net do it. Add this to your ZipCodeRepository ``` public IEnumerable GetStates() { return dbSet.OrderBy(e => e.State).Select(e => e.State).Distinct(); } ``` Note that what's causing the materialization (and a ton of overhead) is the `ToList()`. In general, keep the collection as an IQueryable until you're actually ready to to use the results. The moment you actually enumerate the collection EF will actually perform the query for you anyway. Alternatively, if you remove the `ToList()` calls and change the return type of the `Get` function to be `IQueryable` that should also resolve the issue.

Timeout exception running Linq Statement

Solution

Problem