Entity Framework Core – Contains Check Via Temp Tables

In diesem Artikel:

pg
Pawel Gerr ist Architekt und Consultant bei Thinktecture. Er hat sich auf .NET Core Backends spezialisiert und kennt Entity Framework von vorne bis hinten.

Motivation

One of the main queries in my projects is to select multiple database records having a collection of some kind of identifiers. Most of the time it works very well using the method Contains().

				
					List<Guid> ids = ...;

var customers = dbContext.Customers.Where(c => ids.Contains(c.Id));
				
			

The query above is translated by the Entity Framework Core (EF Core) to IN clause: WHERE Id IN ('id_1', ... 'id_n')

Most of the time using an IN clause is not a problem but when having a big collection of ids then the transmission of a huge SQL statement and parsing of it will have negative impact on the performance. This is just one use case of many.

In the second use case we have a collection of tuples, say a collection of (customerId, productId). In this case the method Contains can’t help us, we have to use a JOIN but joining a query with an in-memory-collection (like a List<>) will lead to downloading all data from database because the JOIN will be done in memory.

				
					List<(Guid customerId, Guid productId)> tuples = ...;

// can cause performance issues
var orderItems = ctx.OrderItems.Join(tuples,
                                     i => new { i.Order.CustomerId, i.ProductId },
                                     t => new {
                                                CustomerId = t.customerId, 
                                                ProductId = t.productId
                                              },
                                     (i, t) => i);
				
			

In both cases it would be better to Bulk Insert the ids into a temp table to perform a JOIN with it.

Solution

Use the following links to try out the demos:

Before using a temp table with EF Core we have to introduce it to our DbContext using the extension method ConfigureTempTable with desired column type(s).
The extension method returns an instance of QueryTypeBuilder for further configuration but most of the time there is no need for that.

Remarks: For different column types we have to make multiple calls of ConfigureTempTable. For example one temp table with Guid, the other with Guid? (i.e. nullable Guid), the third with a string, etc.

				
					public class DemoDbContext : DbContext
{
   ...

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      ...

      modelBuilder.ConfigureTempTable<Guid>();        // for use case 1
      modelBuilder.ConfigureTempTable<Guid, Guid>();  // for use case 2
   }
}

				
			

After introduction of the temp tables we now may use them as often as needed.

The solution for 1st use case:

				
					List<Guid> ids = ...;

var tempTableQuery = await ctx.BulkInsertTempTableAsync(ids);
var customers = await ctx.Customers
                         .Join(tempTableQuery, c => c.Id, t => t.Column1, (c, t) => c)
                         .ToListAsync();
				
			

Remarks: Each call of BulkInsertTempTableAsync is using a different temp table name by default so there are no conflicts when using this method multiple times. For more control over temp table creation and the bulk insert via SqlBulkCopy provide an instance of type SqlBulkInsertOptions as the 2nd parameter.

The solution for the 2nd use case:

				
					List<(Guid customerId, Guid productId)> tuples = ...;

var tempTableQuery = await ctx.BulkInsertTempTableAsync(tuples);
var orderItems = await ctx.OrderItems.Join(tempTableQuery,
                                           i => new { i.Order.CustomerId, i.ProductId },
                                           t => new {
                                                      CustomerId = t.Column1, 
                                                      ProductId = t.Column2
                                                    },
                                           (i, t) => i)
                          .ToListAsync();
				
			

The NuGet package will be released in the near future, at the moment I’m gathering feedback.

Kostenloser
Newsletter

Aktuelle Artikel, Screencasts, Webinare und Interviews unserer Experten für Sie

Verpassen Sie keine Inhalte zu Angular, .NET Core, Blazor, Azure und Kubernetes und melden Sie sich zu unserem kostenlosen monatlichen Dev-Newsletter an.

Newsletter Anmeldung
Diese Artikel könnten Sie interessieren
Database Access with Sessions
.NET
kp_300x300

Data Access in .NET Native AOT with Sessions

.NET 8 brings Native AOT to ASP.NET Core, but many frameworks and libraries rely on unbound reflection internally and thus cannot support this scenario yet. This is true for ORMs, too: EF Core and Dapper will only bring full support for Native AOT in later releases. In this post, we will implement a database access layer with Sessions using the Humble Object pattern to get a similar developer experience. We will use Npgsql as a plain ADO.NET provider targeting PostgreSQL.
15.11.2023
Old computer with native code
.NET
kp_300x300

Native AOT with ASP.NET Core – Overview

Originally introduced in .NET 7, Native AOT can be used with ASP.NET Core in the upcoming .NET 8 release. In this post, we look at the benefits and drawbacks from a general perspective and perform measurements to quantify the improvements on different platforms.
02.11.2023
.NET
kp_300x300

Optimize ASP.NET Core memory with DATAS

.NET 8 introduces a new Garbage Collector feature called DATAS for Server GC mode - let's make some benchmarks and check how it fits into the big picture.
09.10.2023
.NET CORE
pg

Incremental Roslyn Source Generators: High-Level API – ForAttributeWithMetadataName – Part 8

With the version 4.3.1 of Microsoft.CodeAnalysis.* Roslyn provides a new high-level API - the method "ForAttributeWithMetadataName". Although it is just 1 method, still, it addresses one of the biggest performance issue with Source Generators.
16.05.2023
AI
favicon

Integrating AI Power into Your .NET Applications with the Semantic Kernel Toolkit – an Early View

With the rise of powerful AI models and services, questions come up on how to integrate those into our applications and make reasonable use of them. While other languages like Python already have popular and feature-rich libraries like LangChain, we are missing these in .NET and C#. But there is a new kid on the block that might change this situation. Welcome Semantic Kernel by Microsoft!
03.05.2023
.NET
sg

.NET 7 Performance: Regular Expressions – Part 2

There is this popular quote by Jamie Zawinski: Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems."

In this second article of our short performance series, we want to look at the latter one of those problems.
25.04.2023