When are queries executed on DbContext

Question

I am trying to understand the performance impact of having one DbContext class vs multiple when using EF6 framework.

For example, if we have a simple DbContext such as this:

public class MainDbContext : DbContext
{
    public DbSet<Car> Cars { get; set; }

    public void AddCar(Car car)
    {
        Cars.Add(car);
        SaveChanges();
    }
}

Now let us say I have a service that uses the said DbContext in the following way:

public class CarService
{
    public List<Car> Cars { get; private set; }
    public CarService()
    {
        var dbContext = new MainDbContext();
        Cars = dbContext.Cars.ToList();
    }
}

At what point in time did the DbContext go to the database and retrieved all cars that are stored in the database? Was it when I called var dbContext = new MainDbContext(); or was it when I called Cars = dbContext.Cars.ToList();?

If former, if I had a DbContext that contains 100 tables, will all 100 tables be queried when the DbContext is created?

Answer 1

No. The query happens once you enumerate a table. Even in your second example, it still does not connect to the database.

It will connect when you enumerate, for example:

dbContext.Cars.ToList();

Or

foreach (Car c in dbContext.Cars)

Or bind the table to a UI control.

However when you make a where, order by, then by, join etc.. e.g.

var result = dbContext.Cars.Where(c => c.Id == 35).OrderBy(c => c.Name);

You will not connect to the database. You are only preparing the logical sequence of operations to translate into an SQL query.

---

AFTER THE QUESTION UPDATE

Now, your second example, with the ToList(), enumerate the results and connects to the database to get the data.