Executing database queries the Entity Framework way

When using Entity Framework, we usually believe that when executing the actual Linq query will result in a call to the database.  A call that will bring the results we are asking for. This is not the behavior of the Entity Framework.

Entity Framework will make the actual call to the database in the following situations:

1. When enumerating through a collection
2. When calling the ToList method
3. When calling the Single/SingleOrDefault or the First/FirstOrDefault methods

Enumerating through a collection

Suppose that we have Customer table in the database. The table has a customer id, a first name, a last name and a state fields. Your task is to display in a console application all the customer from the New York state. The code will look something like this:

var nyCustomers = context.Customers.Where(c=>c.State == "NY");

foreach(var customer in nyCustomers)
{
    Console.WriteLine($"Customer: {customer.FirstName} {customer.LastName}");
}

We can think that the call to the database will be done on the line Customers.Where(c=>c.State == “NY”), but the actual call will be done in the first attempt of the foreach block.

Calling ToList() Method

The next piece of code is basically the same as the above code, but with a minor change, calling the ToList() method after Customers.Where(c=>c.State == “NY”).

var nyCustomers = context.Customers.Where(c=>c.State == "NY").ToList();
foreach(var customer in nyCustomers)
{
    Console.WriteLine($"Customer: {customer.FirstName} {customer.LastName}");
}

The actual call to the database will occur in the Customers.Where(c=>c.State == “NY”).ToList(). It will create a list of customer prior of the iteration in the foreach block;

Calling the Single/SingleOrDefault or the First/FirstOrDefault methods

We can use the Single(), SingleOrDefault(), First() or FirstOrDefault() methods to get a single record from the database. When calling either one of them, we are making the call to the database.

var nyCustomers = context.Customers.Where(c=>c.State == "NY");
var customerToEdit = nyCustomer.FirstOrDefault(c=>c.Id == 123);

The actual call to the database will be done in the line nyCustomer.FirstOrDefault(c=>c.Id == 123).

Conclusion

A question that could present is: what happen in the above code if when calling the Where() method, we use also a ToList() method? Is there will be two calls for the database, one for the ToList() method and another for the FirstOrDefault() method?

var nyCustomers = context.Customers.Where(c=>c.State == "NY").ToList();
var customerToEdit = nyCustomer.FirstOrDefault(c=>c.Id == 123);

In this case the actual call to the database will be done on the ToList() method. The FirstOrDefault() method will act as an in memory filter of the result. This behavior is good when there is a small amount of records returned in the ToList() method. But it will impact the performance of the application if the ToList() method returns a huge amount of records. In this case, is better to call the FirstOrDefault() method directly.