{"id":474374,"date":"2024-06-26T14:36:38","date_gmt":"2024-06-26T13:36:38","guid":{"rendered":"https:\/\/blog.jetbrains.com\/?post_type=dotnet&p=474374"},"modified":"2024-06-26T14:36:43","modified_gmt":"2024-06-26T13:36:43","slug":"how-where-conditions-work-in-entity-framework-core","status":"publish","type":"dotnet","link":"https:\/\/blog.jetbrains.com\/en\/dotnet\/2024\/06\/26\/how-where-conditions-work-in-entity-framework-core","title":{"rendered":"How Where Conditions Work in Entity Framework Core"},"content":{"rendered":"\n

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\u2019re new to the .NET space, this blog post will help you understand how to create filtered result sets using the Where<\/code> operator and how to get around some everyday situations you\u2019ll find in most EF Core code bases.<\/p>\n\n\n

\n
\n Explore JetBrains .NET tools<\/a>\n <\/div>\n <\/div>\n\n\n\n\n\n\n\n

Getting Started<\/h2>\n\n\n\n

To follow along, I\u2019ve created a Database<\/code> class with seed data. The following DbContext<\/code> definition will help us execute some basic Where<\/code> scenarios and better understand what\u2019s happening inside the EF Core abstraction.<\/p>\n\n\n\n

using System.ComponentModel.DataAnnotations;\nusing System.ComponentModel.DataAnnotations.Schema;\nusing Microsoft.EntityFrameworkCore;\nusing Microsoft.Extensions.Logging;\n\nnamespace EFCorePlayground.Models;\n\npublic class Database: DbContext\n{\n    public DbSet<Advocate> Advocates => Set<Advocate>();\n    \n    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)\n    {\n        optionsBuilder\n            .UseSqlite(\"Data Source=app.db\")\n            .EnableSensitiveDataLogging()\n            .LogTo(\n                Console.WriteLine,\n                new[] { DbLoggerCategory.Database.Command.Name },\n                LogLevel.Information);\n    }\n\n    protected override void OnModelCreating(ModelBuilder modelBuilder)\n    {\n        modelBuilder.Entity<Advocate>()\n            .HasData([\n                new() { Id = 1, Name = \"Maarten Balliauw\", Products = [ \"IntelliJ IDEA\", \"Rider\", \"ReSharper\" ], Technologies = [\".NET\", \"Java\"]},\n                new() { Id = 2, Name = \"Matthias Koch\", Products = [\"ReSharper\", \"Rider\", \"Qodana\", \"Team City\"], Technologies = [\".NET\", \"CI\" ]},\n                new() { Id = 3, Name = \"Rachel Appel\", Products = [\"ReSharper\", \"Rider\"], Technologies = [\".NET\", \"JavaScript\"]},\n                new() { Id = 4, Name = \"Matt Ellis\", Products = [\"Rider\", \"Gateway\"], Technologies = [\".NET\", \"Unreal\", \"Unity\", \"Java\"]},\n                new() { Id = 5, Name = \"Khalid Abuhakmeh\", Products = [\"Rider\", \"RustRover\"], Technologies = [\".NET\", \"Rust\"]}\n            ]);\n    }\n}\n\n[Table(\"Advocates\")]\npublic class Advocate\n{\n    [Key]\n    public int Id { get; set; }\n    [MaxLength(250)]\n    public string Name { get; set; } = \"\";\n    public string[] Products { get; set; } = [];\n    public string[] Technologies { get; set; } = [];\n}\n<\/pre>\n\n\n\n
You can find the code and all queries in this GitHub Repository<\/strong><\/a>.<\/strong><\/p>\n\n\n\n
If you want to use a custom DbContext<\/code> implementation, I suggest adding the following logging code to your implementation\u2019s OnConfiguring<\/code> method.<\/p>\n\n\n\n
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)\n{\n    optionsBuilder\n        .UseSqlite(\"Data Source=app.db\")\n        .EnableSensitiveDataLogging()\n        .LogTo(\n            Console.WriteLine,\n            new[] { DbLoggerCategory.Database.Command.Name },\n            LogLevel.Information);\n}<\/pre>\n\n\n\n
The configuration will filter all logs only to include the SQL statements EF Core generates and logs them to the current console\u2019s standard output. You can see these logs in Rider\u2019s Run <\/strong>tool window.<\/p>\n\n\n\n
\"The<\/figure>\n\n\n\n
Be sure to remove the call to LogTo<\/code> when using your DbContext<\/code> in production environments. The Console<\/code> is single-threaded and notorious for causing performance issues in production.<\/p>\n\n\n\n
The Where Operator<\/h2>\n\n\n\n
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\u2019s important to note that while the abstraction can feel magical, it is anything but. What you write is the starting point for translation<\/strong>. So, it\u2019s essential to be intentional about your queries and consider how EF Core may translate the code.<\/p>\n\n\n\n
Let\u2019s start with the most basic query, the humble ToList<\/code>, and discuss what happens.<\/p>\n\n\n\n
var all = db.Advocates.ToList();<\/pre>\n\n\n\n
The resulting SQL is, not surprisingly, a SELECT<\/code> statement with no filtering at all.<\/p>\n\n\n\n
SELECT \"a\".\"Id\", \"a\".\"Name\", \"a\".\"Products\", \"a\".\"Technologies\"\nFROM \"Advocates\" AS \"a\"<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
Let\u2019s add a Where<\/code> operator and look at the resulting SQL.<\/p>\n\n\n\n
var maarten = db\n    .Advocates\n    .Where(a => a.Id == 1)\n    .ToList();<\/pre>\n\n\n\n
In this case, we use a LINQ expression known as a predicate<\/strong> to create a logical operation only to return a record with an Id<\/code> of 1<\/code>.<\/p>\n\n\n\n
SELECT \"a\".\"Id\", \"a\".\"Name\", \"a\".\"Products\", \"a\".\"Technologies\"\nFROM \"Advocates\" AS \"a\"\nWHERE \"a\".\"Id\" = 1<\/pre>\n\n\n\n
Congratulations. You just filtered your first EF Core query. That was easy, right? The predicate<\/strong> passes in the EF Core model, allowing you to filter on any exposed property. These properties coincide with columns in your database.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Let\u2019s get into some more advanced scenarios.<\/p>\n\n\n\n
And<\/code> and Or<\/code> Operations<\/h2>\n\n\n\n
When building queries, you\u2019ll likely have more than one filtering operation. You can break down most filtering operations into two categories: AND<\/code> and OR<\/code>. <\/p>\n\n\n\n
With AND<\/code> operations, all predicates compound and must be satisfied to match on a record. The AND<\/code> operation is the default behavior of the Where<\/code> operator in EF Core.<\/p>\n\n\n\n
var riderAndResharper = db\n    .Advocates\n    .Where(a => a.Products.Contains(\"Rider\"))\n    .Where(a => a.Products.Contains(\"ReSharper\"))\n    .ToList();<\/pre>\n\n\n\n
The LINQ code produces the following SQL.<\/p>\n\n\n\n
SELECT \"a\".\"Id\", \"a\".\"Name\", \"a\".\"Products\", \"a\".\"Technologies\"\nFROM \"Advocates\" AS \"a\"\nWHERE 'Rider' IN (\n    SELECT \"p\".\"value\"\n    FROM json_each(\"a\".\"Products\") AS \"p\"\n) AND 'ReSharper' IN (\n    SELECT \"p0\".\"value\"\n    FROM json_each(\"a\".\"Products\") AS \"p0\"\n)\n<\/pre>\n\n\n\n
We used two Where<\/code> operators, and EF Core translated our LINQ expression into a single SQL statement with AND combining the two conditions.<\/p>\n\n\n\n
What about an OR<\/code> operation? How do we construct those queries? Well, we use the C# logical operator of||<\/code>.<\/p>\n\n\n\n
var dotnetOrRust = db\n    .Advocates\n    .Where(a => \n        a.Technologies.Contains(\"Rust\") ||\n        a.Technologies.Contains(\".NET\")\n    )\n    .ToList();<\/pre>\n\n\n\n
We have a single Where<\/code> call, but the predicate is slightly more complex. The C# code translates to the following SQL.<\/p>\n\n\n\n
SELECT \"a\".\"Id\", \"a\".\"Name\", \"a\".\"Products\", \"a\".\"Technologies\"\nFROM \"Advocates\" AS \"a\"\nWHERE 'Rust' IN (\n    SELECT \"t\".\"value\"\n    FROM json_each(\"a\".\"Technologies\") AS \"t\"\n) OR '.NET' IN (\n    SELECT \"t0\".\"value\"\n    FROM json_each(\"a\".\"Technologies\") AS \"t0\"\n)\n<\/pre>\n\n\n\n
Nice! We can also use this similar construct with the && operator to get the same result as our first query.<\/p>\n\n\n\n
var dotnetOrRustWithAnd = db\n    .Advocates\n    .Where(a => \n        a.Technologies.Contains(\"Rust\") &&\n        a.Technologies.Contains(\".NET\")\n    )\n    .ToList();<\/pre>\n\n\n\n
We\u2019ve been using EF Core to translate C# to SQL, but what happens when we want to access database-specific functionality? Let\u2019s examine that next.<\/p>\n\n\n\n
Using Database Functions<\/h2>\n\n\n\n
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<\/code> function to do fuzzy searches in a table. <\/p>\n\n\n\n
Luckily, the EF<\/code> class exposes database functions for a Where<\/code> operation.<\/p>\n\n\n\n
var mAdvocates = db\n    .Advocates\n    .Where(a => EF.Functions.Like(a.Name, \"M%\"))\n    .ToList();<\/pre>\n\n\n\n
EF Core and LINQ are only here to help you translate C# code into SQL and the EF.Functions.Like<\/code> method call is representative of the LIKE<\/code> database function.<\/p>\n\n\n\n
SELECT \"a\".\"Id\", \"a\".\"Name\", \"a\".\"Products\", \"a\".\"Technologies\"\nFROM \"Advocates\" AS \"a\"\nWHERE \"a\".\"Name\" LIKE 'M%'<\/pre>\n\n\n\n
What happens when you can\u2019t do everything in a SQL call?<\/p>\n\n\n\n
In-Memory Filtering<\/h2>\n\n\n\n
Throughout this post, we\u2019ve been Materializing<\/strong> 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!<\/p>\n\n\n\n
bool ProductEndsWithEr(string product)\n    => product.EndsWith(\"er\");\n\nvar erProducts = db\n    .Advocates\n    .ToList() \/\/ execute SQL (all records)\n    \/\/ in memory now\n    .Where(a => a.Products.Any(ProductEndsWithEr))\n    .ToList();    <\/pre>\n\n\n\n
Here, we materialize our initial dataset using the first call to ToList<\/code>, and then use a C# method to filter our collection further.<\/p>\n\n\n\n
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\u2019s memory and improve performance.<\/p>\n\n\n\n
Conditional Filtering<\/h2>\n\n\n\n
Here is a bonus section about the strength of C#\u2019s extension methods and how they can help you build custom functionality on top of the existing LINQ syntax. <\/p>\n\n\n\n
One of my favorite extension methods is the If<\/code> operation. I use this method to apply a Where<\/code> operator based on other conditions in my codebase.<\/p>\n\n\n\n
var condition = Random.Shared.Next(1, 10) > 5;\n\nvar conditional = db\n    .Advocates\n    .If(condition, q => \n        q.Where(a => a.Name.StartsWith(\"K\"))\n    )\n    .ToList();<\/pre>\n\n\n\n
Extension methods are typically applied to the IQueryable<\/code> interface to ensure that we\u2019re dealing with EF Core and that it will translate our operations into SQL.<\/p>\n\n\n\n
using System.Linq.Expressions;\n\nnamespace EFCorePlayground;\n\npublic static class QueryableExtensions\n{\n    public static IQueryable<TSource> If<TSource>(\n        this IQueryable<TSource> source,\n        bool condition,\n        Func<IQueryable<TSource>, IQueryable<TSource>> queryable)\n    {\n        return condition ? queryable(source) : source;\n    }\n}\n<\/pre>\n\n\n\n
Extension methods can help you control how an SQL statement is built before it executes against your data storage engine.<\/p>\n\n\n\n
If you\u2019re 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. <\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
If you have any tips, tricks, or questions, please feel free to leave us a comment.<\/p>\n\n\n\n
image credit: Hannes Richter<\/a><\/em><\/sub><\/p>\n","protected":false},"author":1079,"featured_media":474390,"comment_status":"closed","ping_status":"closed","template":"","categories":[4992],"tags":[211,6927,201],"cross-post-tag":[],"acf":[],"_links":{"self":[{"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/dotnet\/474374"}],"collection":[{"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/dotnet"}],"about":[{"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/types\/dotnet"}],"author":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/users\/1079"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/comments?post=474374"}],"version-history":[{"count":9,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/dotnet\/474374\/revisions"}],"predecessor-version":[{"id":487627,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/dotnet\/474374\/revisions\/487627"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/media\/474390"}],"wp:attachment":[{"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/media?parent=474374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/categories?post=474374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/tags?post=474374"},{"taxonomy":"cross-post-tag","embeddable":true,"href":"https:\/\/blog.jetbrains.com\/en\/wp-json\/wp\/v2\/cross-post-tag?post=474374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}