1

Generally the work with EF for an looks like this:

Some Web controller calls for a method that has something like this:

using (var ctx = new EntityContext())
{
    ...
    return something;
}

But I guess that in highly loaded application, that has several thousand requests per minute, it might create the problems. So my question is: maybe it makes sense to manually open connection and keep it alive? If yes, can anybody share the proper piece of code for such the task?

Thanks in advance

ken2k
  • 48,145
  • 10
  • 116
  • 176
J. Doe
  • 91
  • 4

2 Answers2

3

No, don't try to keep opened connections alive. It just won't work, see https://stackoverflow.com/a/9416275/870604.

You're building an ASP.Net MVC application, so the pattern to follow to work with Entity Framework is quite simple in this use case: instantiate a new context for each new controller instance (for example using dependency injection). As a new controller instance is created for each user request, you'll have one new fresh EF context for each request, which is highly desirable.

Don't worry about creating a lot of context instances, it won't create a new DB connection each time thanks to connection pooling.

If you want your application to be scalable, your best option is to also use the async pattern if your version of Entity Framework supports it. See http://blogs.msdn.com/b/webdev/archive/2013/09/18/scaffolding-asynchronous-mvc-and-web-api-controllers-for-entity-framework-6.aspx

EDIT: Have a look to this overview of the unit of work pattern with ASP.Net MVC and EF.

Community
  • 1
  • 1
ken2k
  • 48,145
  • 10
  • 116
  • 176
  • Thanks for the answer. I have one more question: how should I handle the situation in EF when several controller instances (of different users) will try to update the same record in the database? Which type of transaction is better to use ?(serializable is not the best option) and what should I lock in the code: the whole dbcontext? – J. Doe Feb 19 '15 at 10:54
  • @J.Doe That's the role of the concurrency control system. There are several patterns available, one for example is the [optimistic concurrency pattern](https://msdn.microsoft.com/en-us/data/jj592904.aspx). Basically, when you try to update something in the DB, EF queries the data and ensures it's still in the same state it was previously. Generally you add a column in your table (for example the timestamp of the last row update), and EF will check this value didn't change between the time you loaded your entity and the time you tried to update in. Try to avoid locks. – ken2k Feb 19 '15 at 13:49
  • I read the article you mentioned, but now I'm more confused. I'm not sure which method to use: db wins, client wins, or anything else...As I mentioned earlier, I have a pool of requests and I implement these requests in Tasks (multithreading) so it's possible that several threads will work on the same record of the database and in this scenario, I 'm not sure which method would be the best – J. Doe Feb 21 '15 at 09:34
0

After closing tag of using element, it automatically close the connection. So you don't want to worry about it. But if you really want to open connection manually, try following (I couldn't test it. May be you need to give connection settings).

ctx.Database.Connection.Open();
Robert
  • 5,278
  • 43
  • 65
  • 115
Nat
  • 1
  • 1