1

So here is a link of the same question asked around 11 years ago. It's old and doesn't clear what I am looking for.

When exactly does EF open and close a database connection?

Is it opened when we create a DbContext object or is it opened when we first make a request?

Is it closed when we hit SaveChanges() or is it closed when we either manually dispose the object or use it inside using() {}?

I have gone through Microsoft online resources and couldn't find what I was looking for.

Thank you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mfs
  • 3,984
  • 6
  • 32
  • 51

2 Answers2

0

You can use either one of these.

Use using(). It will open at beginning of using() block and dispose at the end of using() block.

using (var db = new YourContext())
{
    db.User.Add(user);
    db.SaveChanges();
}

Dependency injection in Startup.cs. It opens after controller is called and will dispose after function is called.

Startup.cs

services.AddDbContext<YourContext>(options =>
{
    options.UseSqlServer("your connection string");
    options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
});

HomeController.cs

private readonly YourContext _db;
public HomeController(YourContext db)
{
    _db = db;
}
public IActionResult Index()
{
   var users = _db.Users.AsQueryable();
   return View(users);
}
Asherguru
  • 1,687
  • 1
  • 5
  • 10
  • `It will open at beginning of using() block` That is not true. Do a DB trace and see for yourself. – mjwills Aug 03 '20 at 01:30
0

The underlying connection is opened right before a query or non-query execution, and it is closed right after it's done, or as long as the transaction is going. The DbContext must be disposed of properly. This is how you get the best out of the connection pool. This is what we all should've done back when ADO.NET was king.

insane_developer
  • 789
  • 5
  • 6
  • A query expects data back, like an html GET request. Insert, Update and Delete are known as non-queries. As long as you see IQueryable as the return type, the query execution is being deferred. Calling ToList or similar returns IEnumerable, which means the query is being executed. SaveChanges takes care of the non-queries. – insane_developer Aug 03 '20 at 01:07