How Where Conditions Work in Entity Framework Core

Khalid Abuhakmeh

Entity Framework Core is a popular data access library in the .NET space that makes it easier to write, execute, and process SQL statements and the resulting data. If you’re new to the .NET space, this blog post will help you understand how to create filtered result sets using the Where operator and how to get around some everyday situations you’ll find in most EF Core code bases.

Getting Started

To follow along, I’ve created a Database class with seed data. The following DbContext definition will help us execute some basic Where scenarios and better understand what’s happening inside the EF Core abstraction.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EFCorePlayground.Models;

public class Database: DbContext
{
    public DbSet<Advocate> Advocates => Set<Advocate>();
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlite("Data Source=app.db")
            .EnableSensitiveDataLogging()
            .LogTo(
                Console.WriteLine,
                new[] { DbLoggerCategory.Database.Command.Name },
                LogLevel.Information);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Advocate>()
            .HasData([
                new() { Id = 1, Name = "Maarten Balliauw", Products = [ "IntelliJ IDEA", "Rider", "ReSharper" ], Technologies = [".NET", "Java"]},
                new() { Id = 2, Name = "Matthias Koch", Products = ["ReSharper", "Rider", "Qodana", "Team City"], Technologies = [".NET", "CI" ]},
                new() { Id = 3, Name = "Rachel Appel", Products = ["ReSharper", "Rider"], Technologies = [".NET", "JavaScript"]},
                new() { Id = 4, Name = "Matt Ellis", Products = ["Rider", "Gateway"], Technologies = [".NET", "Unreal", "Unity", "Java"]},
                new() { Id = 5, Name = "Khalid Abuhakmeh", Products = ["Rider", "RustRover"], Technologies = [".NET", "Rust"]}
            ]);
    }
}

[Table("Advocates")]
public class Advocate
{
    [Key]
    public int Id { get; set; }
    [MaxLength(250)]
    public string Name { get; set; } = "";
    public string[] Products { get; set; } = [];
    public string[] Technologies { get; set; } = [];
}

You can find the code and all queries in this GitHub Repository.

If you want to use a custom DbContext implementation, I suggest adding the following logging code to your implementation’s OnConfiguring method.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlite("Data Source=app.db")
        .EnableSensitiveDataLogging()
        .LogTo(
            Console.WriteLine,
            new[] { DbLoggerCategory.Database.Command.Name },
            LogLevel.Information);
}

The configuration will filter all logs only to include the SQL statements EF Core generates and logs them to the current console’s standard output. You can see these logs in Rider’s Run tool window.

Be sure to remove the call to LogTo when using your DbContext in production environments. The Console is single-threaded and notorious for causing performance issues in production.

The Where Operator

EF Core uses the LINQ syntax as a domain-specific language (DSL) within C# codebases as a starting point for translating your queries into SQL. The LINQ interface provides a common abstraction for constructing queries regardless of the underlying data storage choice. It’s important to note that while the abstraction can feel magical, it is anything but. What you write is the starting point for translation. So, it’s essential to be intentional about your queries and consider how EF Core may translate the code.

Let’s start with the most basic query, the humble ToList, and discuss what happens.

var all = db.Advocates.ToList();

The resulting SQL is, not surprisingly, a SELECT statement with no filtering at all.

SELECT "a"."Id", "a"."Name", "a"."Products", "a"."Technologies"
FROM "Advocates" AS "a"

While dependent on your context, this is typically a practice that can lead to issues of over-fetching, especially on tables with a large number of records.

Let’s add a Where operator and look at the resulting SQL.

var maarten = db
    .Advocates
    .Where(a => a.Id == 1)
    .ToList();

In this case, we use a LINQ expression known as a predicate to create a logical operation only to return a record with an Id of 1.

SELECT "a"."Id", "a"."Name", "a"."Products", "a"."Technologies"
FROM "Advocates" AS "a"
WHERE "a"."Id" = 1

Congratulations. You just filtered your first EF Core query. That was easy, right? The predicate passes in the EF Core model, allowing you to filter on any exposed property. These properties coincide with columns in your database.

If you take anything away from this post, it should be that EF Core and your models are representations of an underlying database. They are not so much C# objects as proxies for a schema in a data storage engine. Getting into this mindset helps you avoid the costly mistakes of using untranslatable .NET methods in constructing queries.

Let’s get into some more advanced scenarios.

And and Or Operations

When building queries, you’ll likely have more than one filtering operation. You can break down most filtering operations into two categories: AND and OR. 

With AND operations, all predicates compound and must be satisfied to match on a record. The AND operation is the default behavior of the Where operator in EF Core.

var riderAndResharper = db
    .Advocates
    .Where(a => a.Products.Contains("Rider"))
    .Where(a => a.Products.Contains("ReSharper"))
    .ToList();

The LINQ code produces the following SQL.

SELECT "a"."Id", "a"."Name", "a"."Products", "a"."Technologies"
FROM "Advocates" AS "a"
WHERE 'Rider' IN (
    SELECT "p"."value"
    FROM json_each("a"."Products") AS "p"
) AND 'ReSharper' IN (
    SELECT "p0"."value"
    FROM json_each("a"."Products") AS "p0"
)

We used two Where operators, and EF Core translated our LINQ expression into a single SQL statement with AND combining the two conditions.

What about an OR operation? How do we construct those queries? Well, we use the C# logical operator of||.

var dotnetOrRust = db
    .Advocates
    .Where(a => 
        a.Technologies.Contains("Rust") ||
        a.Technologies.Contains(".NET")
    )
    .ToList();

We have a single Where call, but the predicate is slightly more complex. The C# code translates to the following SQL.

SELECT "a"."Id", "a"."Name", "a"."Products", "a"."Technologies"
FROM "Advocates" AS "a"
WHERE 'Rust' IN (
    SELECT "t"."value"
    FROM json_each("a"."Technologies") AS "t"
) OR '.NET' IN (
    SELECT "t0"."value"
    FROM json_each("a"."Technologies") AS "t0"
)

Nice! We can also use this similar construct with the && operator to get the same result as our first query.

var dotnetOrRustWithAnd = db
    .Advocates
    .Where(a => 
        a.Technologies.Contains("Rust") &&
        a.Technologies.Contains(".NET")
    )
    .ToList();

We’ve been using EF Core to translate C# to SQL, but what happens when we want to access database-specific functionality? Let’s examine that next.

Using Database Functions

While EF Core has a lot of translations for C# code into SQL, there are times when you might want to call a particular database function. For example, many folks use the LIKE function to do fuzzy searches in a table. 

Luckily, the EF class exposes database functions for a Where operation.

var mAdvocates = db
    .Advocates
    .Where(a => EF.Functions.Like(a.Name, "M%"))
    .ToList();

EF Core and LINQ are only here to help you translate C# code into SQL and the EF.Functions.Like method call is representative of the LIKE database function.

SELECT "a"."Id", "a"."Name", "a"."Products", "a"."Technologies"
FROM "Advocates" AS "a"
WHERE "a"."Name" LIKE 'M%'

What happens when you can’t do everything in a SQL call?

In-Memory Filtering

Throughout this post, we’ve been Materializing results. Materialization is the process of executing SQL, retrieving the results, and mapping them into object instances you can use during the lifetime of your application. So, if we have objects in memory, we should be able to do additional filtering in memory, right? Of course!

bool ProductEndsWithEr(string product)
    => product.EndsWith("er");

var erProducts = db
    .Advocates
    .ToList() // execute SQL (all records)
    // in memory now
    .Where(a => a.Products.Any(ProductEndsWithEr))
    .ToList();    

Here, we materialize our initial dataset using the first call to ToList, and then use a C# method to filter our collection further.

Using in-memory filters is a valuable technique when logic is impossible to translate into SQL, but be mindful to filter your result set as much as possible using SQL. Doing so will reduce the data transferred from the database into your application’s memory and improve performance.

Conditional Filtering

Here is a bonus section about the strength of C#’s extension methods and how they can help you build custom functionality on top of the existing LINQ syntax. 

One of my favorite extension methods is the If operation. I use this method to apply a Where operator based on other conditions in my codebase.

var condition = Random.Shared.Next(1, 10) > 5;

var conditional = db
    .Advocates
    .If(condition, q => 
        q.Where(a => a.Name.StartsWith("K"))
    )
    .ToList();

Extension methods are typically applied to the IQueryable interface to ensure that we’re dealing with EF Core and that it will translate our operations into SQL.

using System.Linq.Expressions;

namespace EFCorePlayground;

public static class QueryableExtensions
{
    public static IQueryable<TSource> If<TSource>(
        this IQueryable<TSource> source,
        bool condition,
        Func<IQueryable<TSource>, IQueryable<TSource>> queryable)
    {
        return condition ? queryable(source) : source;
    }
}

Extension methods can help you control how an SQL statement is built before it executes against your data storage engine.

If you’re working with user input, this can be a powerful tool in empowering users to build their queries while you maintain control of the resulting SQL statements. For example, you can add search and filter conditions to your query based on arguments passed into your ASP.NET Core Minimal API. 

Conclusion

Entity Framework Core is a powerful tool for building database-powered .NET applications, and understanding the basics can help set you up for success. This post covered the basics of filtering data and how you may want to use these operations more efficiently in your solutions.

If you have any tips, tricks, or questions, please feel free to leave us a comment.

_{image credit: Hannes Richter}

Subscribe to a monthly digest curated from the .NET Tools blog:

Subscribe form

By submitting this form, I agree to the JetBrains Privacy Policy Notification icon

By submitting this form, I agree that JetBrains s.r.o. ("JetBrains") may use my name, email address, and location data to send me newsletters, including commercial communications, and to process my personal data for this purpose. I agree that JetBrains may process said data using third-party services for this purpose in accordance with the JetBrains Privacy Policy. I understand that I can revoke this consent at any time in my profile. In addition, an unsubscribe link is included in each email.

Submit

Thanks, we've got you!