Delving into IQueryable

Welcome everyone to a new post where today we’re going to see the importance of the IQueryable interface. If you’ve used LINQ in the past, chances are you’ve worked with it, even if you didn’t know it!

As always, this post is part of the Entity Framework course, and in some cases, it may be important to understand the context.

1 - What is the IQueryable interface?

The first thing we need to understand is: what exactly is the IQueryable interface? Basically, it is an interface that inherits from IEnumerable and allows us to "build up" the query bit by bit until we translate it into SQL.

2 - Why is the IQueryable interface important?

As I explained in the previous point, it lets us build the query that will be executed step by step. This is very, very important in certain scenarios, because it enables us to assemble a query based on different filters or steps in our application.

The easiest way to see this is when we have pagination in our API.

To write this post, I modified the seed to include 50 users:

public class UserSeed : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.HasQueryFilter(a => !a.IsDeleted);

        builder.HasData(
            BuildUsers()
        );
    }

    private List<User> BuildUsers()
    {
        List<User> users = new List<User>();
        foreach (int index in Enumerable.Range(1, 50))
        {
            users.Add(new User
                { Email = $"example{index}@mail.com", Id = index, UserName = $"user{index}", IsDeleted = false });
        }

        return users;
    }
}

From here, what we need to understand is that we have a database with 50 users, which will help us understand the IQueryable interface.

Let’s talk about pagination again. The simplest way to paginate is by having the page number and the number of items you want to retrieve:

var result = _context.Users
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize);

The important thing here is to understand that both the .Take() and .Skip() methods return an IQueryable, and this is important because as long as we use IQueryable we’re NOT actually hitting the database yet.

This opens up more possibilities for filtering. For example, now we can add another common filter, basically, in a column (in our case, the email column), we can filter by it.

var result = _context.Users
    .Where(a=>a.Email.Contains(emailFilter))
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize);

As you can see, our where also works with IQueryable, so up until now, we are not querying the database yet.

In our particular case, we’ll call the database when we execute .ToListAsync(), but it could also be executed by calling First, Single, etc.

2.1 - Beware of subqueries

When using IQueryable we need to be careful when performing multiple operations against the database. For example, in the following scenario, which might seem quite normal:

IQueryable<User> users =  _context.Users;
foreach (User user in users)
{
    ICollection<Wokringexperience> experiences =  user.Wokringexperiences;
}

What we really have here is an N+1 query situation, which can be very negative for your application, and of course, for performance.

This happens because the initial foreach makes the call since users is IQueryable, but once inside the foreach, it executes. And then, inside each iteration, we're calling WorkingExperiences which are related and reside in another database table, so that’s another SQL call each time.