1. Should i close it after every query?
.Net does that for you so let it handle it, that's a garbage collector task. So don't bother disposing your objects manually, this is a good answer by Jon Skeet: https://stackoverflow.com/a/1998600/544283. However you could use the using(IDisposable){ } statement to force the GC to do it's work. Here is a nice article about resources reallocation: http://www.codeproject.com/Articles/29534/IDisposable-What-Your-Mother-Never-Told-You-About.
2. A connection in static class is good?
Never make a data context static! Data contexts are not thread safe or concurrent safe.
3. Is there a good design pattern for this problem?
As Belogix mentioned dependency injection and unit of work patterns are great, in fact entity framework is a unit of work itself. DI and UoW are a bit overrated though, it's not easy to implement if it's your first time handling an IoC container which if you're going that path I'd recommend Ninject. One other thing is you don't really need DI if you're not gonna run tests, the awesomeness of these patterns is to decouple, so you can test and mock without sweat.
In-short: If you're gonna run test against your code go for these patterns. If not, I'm providing you an example about how you could share your data context among the services you'd like. This is the answer to your fourth question.
4. What is the best method for making database connection (static, per request)?
Your context service:
public class FooContextService {
private readonly FooContext _ctx;
public FooContext Context { get { return _ctx; } }
public FooContextService() {
_ctx = new FooContext();
}
}
Other services:
public class UnicornService {
private readonly FooContext _ctx;
public UnicornService(FooContextService contextService) {
if (contextService == null)
throw new ArgumentNullException("contextService");
_ctx = contextService.Context;
}
public ICollection<Unicorn> GetList() {
return _ctx.Unicorns.ToList();
}
}
public class DragonService {
private readonly FooContext _ctx;
public DragonService(FooContextService contextService) {
if (contextService == null)
throw new ArgumentNullException("contextService");
_ctx = contextService.Context;
}
public ICollection<Dragon> GetList() {
return _ctx.Dragons.ToList();
}
}
Controller:
public class FantasyController : Controller {
private readonly FooContextService _contextService = new FooContextService();
private readonly UnicornService _unicornService;
private readonly DragonService _dragonService;
public FantasyController() {
_unicornService = new UnicornService(_contextService);
_dragonService = new DragonService(_contextService);
}
// Controller actions
}
Second thoughts (almost an edit):
If you need your context not to create the proxies for your entities therefore not having lazy loading either, you could overload your context service as follows:
public class FooContextService {
private readonly FooContext _ctx;
public FooContext Context { get { return _ctx; } }
public FooContextService() : this(true) { }
public FooContextService(bool proxyCreationEnabled) {
_ctx = new FooContext();
_ctx.Configuration.ProxyCreationEnabled = proxyCreationEnabled;
}
}
NOTE:
- If you set the proxy creation enabled to false you will not have lazy loading out of the box.
- If you have api controllers you don't want to deal with any full blown object graph.
EDIT:
Some reading first:
Get this done:
(_context as IObjectContextAdapter).ObjectContext.Connection.Open();
This is a great article about Managing Connections and Transactions.
Entity framework exposes EntityConnection through the Connection property. Read as: public sealed class EntityConnection : DbConnection.
Considerations for managing connections: (taken from previous link)
- The object context will open the connection if it is not already open before an operation. If the object context opens the connection during an operation, it will always close the connection when the operation is complete.
- If you manually open the connection, the object context will not close it. Calling Close or Dispose will close the connection.
- If the object context creates the connection, the connection will always be disposed when the context is disposed.
- In a long-running object context, you must ensure that the context is disposed when it is no longer required.
Hope it helps.
