Timeout exception running Linq Statement

c# entity-framework linq sql sql-server-2008

Question

This very recently began to occur. Months after this statement started working, I kept getting the timeout error listed below. When I run the identical statement directly through the SSMS, it returns immediately. The table has 44 000 records and has 5 index columns, with state as one of them.

select distinct(state) from [ZipCodeDatabase]

The linq statement below is being executed by me.

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

I keep getting a timeout problem when I run this linq command, and I'm not sure why as it used to function fine.

I made use of theGet() function, a generic repo function, but perhaps I am overlooking anything there that is adding to the wait time.

Get Method:

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 mistake

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)

1
3
12/23/2013 11:23:48 PM

Accepted Answer

Just to choose different states, you're materializing each and every row in the database. Instead of materializing everything and then having, let the database perform the work for you and just return the states. Do it, Net.

Include this in ZipCodeRepository.

public IEnumerable<string> GetStates()
{
    return dbSet.OrderBy(e => e.State).Select(e => e.State).Distinct();
}

Observe that the source of the materialization (and a significant amount of overhead) is theToList() .

Keep the collection as an IQueryable until you're ready to use the results, in general. The query will really be run for you by EF after you actually enumerate the collection.

As an alternative, if you take away theToList() modify the return type of the calls andGet purpose to beIQueryable<TEntity> It ought to make the problem go away.

2
12/23/2013 11:44:28 PM

Popular Answer

ZZZ_tmp


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow