Entity Framework Code First - Maintain a Long connection to Database

Question

Below is my Code to Query a 24 Column database using linq

  var list = (from x in db.graph_update
              where x.serial == serial_number 
                    && (x.plotdate >= date_from && x.plotdate <= date_to)
              select new {
                  x.plotdate,
                  x.Temperature
              }).ToList();

Because the Database contains a large amounts to data, the connection time to grab all this data will take a long time and usually results in an error:

500 - The request timed out.

The web server failed to respond within the specified time.

How do I maintain this long running connection so it wouldn't timeout and be able to grab all of the data?

Little bit off topic but if you run the SQL version of that code in SSMS, does it also take a long time to get the results? — JohanP, Feb 27 '17 at 04:26
@JohanP Hi, yes I have and yes it does, It took around 10 minutes to get all of the result. — Johnathan Logan, Feb 27 '17 at 04:28
You're going to have to set `Connection Timeout` property for EF as well as set the [request timeout](http://stackoverflow.com/questions/579523/how-do-i-set-the-request-timeout-for-one-controller-action-in-an-asp-net-mvc-app) for your web API — JohanP, Feb 27 '17 at 04:35
You might want to profile that query to see if you need to add some indexes to improve it. Also if it's retrieving a lot of data you might want to think about why you need so much data for a web call. — juharr, Feb 27 '17 at 04:40
Generally long running queries and large data sets should use a pattern that involves the web api queing a job to be handled by a background process, potentially on a seperate machine from the web service, and the web api immediately returning the caller a URL to poll for completion. The caller can either poll once a minute to check for completion, or let the caller register a URL that you will notify of completion. For large datasets, instead of transfering the file through the webapi, you'd drop it in a location and provide caller the URL for direct download. — AaronLS, Feb 27 '17 at 04:50
That would be one option, or you could insert a record into table representing the parameters for the request, and have a separate application running as a windows service polling the table and running any jobs. There's alot of options for doing these kinds of jobs. — AaronLS, Mar 01 '17 at 05:15
@AaronLS Thanks for the suggestions, could you please provide me a link to an example of this? — Johnathan Logan, Mar 01 '17 at 05:24

score 0 · Answer 1 · answered Feb 27 '17 at 13:23

For whatever reason your query takes that long you can profile it and get max query time and just set timeout:

Entity Framework 6:

this.context.Database.CommandTimeout = 180;

Entity Framework 5:

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

Entity Framework Code First - Maintain a Long connection to Database

1 Answers1