(C# ASP.NET Core LINQ) Where, Include and ToList | This is an explanation of Where, Include and the ToList methods from a practical point of view. We have explained when to use which one and how to use. We have also explained how a SQL JOIN can be performed by using the "Include" function of EF Core.

Parveen, Oct 01, 2021

Our working set

Let us suppose our model is

public class DoctorProfile
{

  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int ID { get; set; }

  // name of a doctor 
  public String Name { get; set; }

  // fees charged by a doctor 
  public int Fees { get; set; }
  //~------- FOREIGN KEYS ---------//

  // foreign key from a table called Login 
  [ForeignKey("Login")]
  public String DoctorEMail { get; set; }

  // foreign key from login table 
  public LoginInfo Login { get; set; }
}

In the following discussion we have assumed that

that we have already configured our database
that our DbContext instance is _ctx
that our DbSet<DoctorProfile> is called Doctors

Video Explanation

Please watch the following youtube video:

How to use Where()?

If you want to find a set of records from a table based on some conditions on the properties, then the right way is to use the Where() method.

The following code searches for all doctors with fees more than 300.


var alldoctors = _ctx.Doctors.Where(q => q.Fees > 300);

// the above code is a SELECT query  
// but it is not executed yet 

// it can be run by .ToList()  
var listOfDoctors = alldoctors.ToList();

// (1) foreach can be run on this list 
foreach (var doc in listOfDoctors)
{
  // each doctor available here 
}

// (2) OR we can run a foreach directly 
// on the query 
foreach (var doc in alldoctors)
{
  // each doctor available here 
}

How to JOIN the Foreign Key Table?

If you observed the above model, it contains a foreign key from a related table.

Following code will fail with a null pointer exception

foreach (var doc in alldoctors)
{

  // each doctor available here 
  var login = doc.Login;

  // fails because "login" 
  // is null here 
  String email = login.EMailID;

}

It fails because EF Core will not perform a JOIN unless it is specifically told to do that. This is called Lazy Loading.

The above problem can be correcting by using Include from EF Core as below:


var alldoctors = _ctx.Doctors
                  .Include(p => p.Login)
                  .Where(q => q.Fees > 300);

// the above code is a SELECT JOIN query  
// but it is not executed yet 

// execute and iterate 
foreach (var doc in alldoctors)
{
  // each doctor available here 
  var login = doc.Login;

  // works now! 
  String email = login.EMailID;
}

This Blog Post/Article "(C# ASP.NET Core LINQ) Where, Include and ToList" by Parveen is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.