C# – Entity Framework Timeouts

asp.netcconnection-stringentity-frameworkentity-framework-4

I am getting timeouts using the Entity Framework (EF) when using a function import that takes over 30 seconds to complete. I tried the following and have not been able to resolve this issue:

I added Default Command Timeout=300000 to the connection string in the App.Config file in the project that has the EDMX file as suggested here.

This is what my connection string looks like:

<add 
    name="MyEntityConnectionString" 
    connectionString="metadata=res://*/MyEntities.csdl|res://*/MyEntities.ssdl|
       res://*/MyEntities.msl;
       provider=System.Data.SqlClient;provider connection string=&quot;
       Data Source=trekdevbox;Initial Catalog=StarTrekDatabase;
       Persist Security Info=True;User ID=JamesTKirk;Password=IsFriendsWithSpock;
       MultipleActiveResultSets=True;Default Command Timeout=300000;&quot;"
    providerName="System.Data.EntityClient" />

I tried setting the CommandTimeout in my repository directly like so:

private TrekEntities context = new TrekEntities();

public IEnumerable<TrekMatches> GetKirksFriends()
{
    this.context.CommandTimeout = 180;
    return this.context.GetKirksFriends();
}

What else can I do to get the EF from timing out? This only happens for very large datasets. Everything works fine with small datasets.

Here is one of the errors I'm getting:

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. —> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

OK – I got this working and it's silly what happened. I had both the connection string with Default Command Timeout=300000 and the CommandTimeout set to 180. When I removed the Default Command Timeout from the connection string, it worked. So the answer is to manually set the CommandTimeout in your repository on your context object like so:

this.context.CommandTimeout = 180;

Apparently setting the timeout settings in the connection string has no effect on it.

Best Answer

There is a known bug with specifying default command timeout within the EF connection string.

http://bugs.mysql.com/bug.php?id=56806

Remove the value from the connection string and set it on the data context object itself. This will work if you remove the conflicting value from the connection string.

Entity Framework Core 1.0:

this.context.Database.SetCommandTimeout(180);

Entity Framework 6:

this.context.Database.CommandTimeout = 180;

Entity Framework 5:

((IObjectContextAdapter)this.context).ObjectContext.CommandTimeout = 180;

Entity Framework 4 and below:

this.context.CommandTimeout = 180;

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

C# – Fastest Way of Inserting in Entity Framework

To your remark in the comments to your question:

"...SavingChanges (for each record)..."

That's the worst thing you can do! Calling SaveChanges() for each record slows bulk inserts extremely down. I would do a few simple tests which will very likely improve the performance:

Call SaveChanges() once after ALL records.
Call SaveChanges() after for example 100 records.
Call SaveChanges() after for example 100 records and dispose the context and create a new one.
Disable change detection

For bulk inserts I am working and experimenting with a pattern like this:

using (TransactionScope scope = new TransactionScope())
{
    MyDbContext context = null;
    try
    {
        context = new MyDbContext();
        context.Configuration.AutoDetectChangesEnabled = false;

        int count = 0;            
        foreach (var entityToInsert in someCollectionOfEntitiesToInsert)
        {
            ++count;
            context = AddToContext(context, entityToInsert, count, 100, true);
        }

        context.SaveChanges();
    }
    finally
    {
        if (context != null)
            context.Dispose();
    }

    scope.Complete();
}

private MyDbContext AddToContext(MyDbContext context,
    Entity entity, int count, int commitCount, bool recreateContext)
{
    context.Set<Entity>().Add(entity);

    if (count % commitCount == 0)
    {
        context.SaveChanges();
        if (recreateContext)
        {
            context.Dispose();
            context = new MyDbContext();
            context.Configuration.AutoDetectChangesEnabled = false;
        }
    }

    return context;
}

I have a test program which inserts 560.000 entities (9 scalar properties, no navigation properties) into the DB. With this code it works in less than 3 minutes.

For the performance it is important to call SaveChanges() after "many" records ("many" around 100 or 1000). It also improves the performance to dispose the context after SaveChanges and create a new one. This clears the context from all entites, SaveChanges doesn't do that, the entities are still attached to the context in state Unchanged. It is the growing size of attached entities in the context what slows down the insertion step by step. So, it is helpful to clear it after some time.

Here are a few measurements for my 560000 entities:

commitCount = 1, recreateContext = false: many hours (That's your current procedure)
commitCount = 100, recreateContext = false: more than 20 minutes
commitCount = 1000, recreateContext = false: 242 sec
commitCount = 10000, recreateContext = false: 202 sec
commitCount = 100000, recreateContext = false: 199 sec
commitCount = 1000000, recreateContext = false: out of memory exception
commitCount = 1, recreateContext = true: more than 10 minutes
commitCount = 10, recreateContext = true: 241 sec
commitCount = 100, recreateContext = true: 164 sec
commitCount = 1000, recreateContext = true: 191 sec

The behaviour in the first test above is that the performance is very non-linear and decreases extremely over time. ("Many hours" is an estimation, I never finished this test, I stopped at 50.000 entities after 20 minutes.) This non-linear behaviour is not so significant in all other tests.