Entity Framework Core – Contains Check Via Temp Tables

In this article:

Entity Framework Core – Contains Check Via Temp Tables
Pawel Gerr is architect consultant at Thinktecture. He focuses on backends with .NET Core and knows Entity Framework inside out.

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.

Free
Newsletter

Current articles, screencasts and interviews by our experts

Don’t miss any content on Angular, .NET Core, Blazor, Azure, and Kubernetes and sign up for our free monthly dev newsletter.

Related Articles
.NET
Roslyn Source Generators: Logging – Part 11
In previous part we lerned how to pass parameters to a Source Generator. In this article we need this knowledge to pass futher parameters to implement logging.
29.08.2023
.NET
Roslyn Source Generators: Configuration – Part 10
In this article we will see how to pass configuration parameters to a Roslyn Source Generator to control the output or enable/disable features.
29.08.2023
.NET
Roslyn Source Generators: Reduction of Resource Consumption in IDEs – Part 9
In this article we will see how to reduce the resource consumption of a Source Generator when running inside an IDE by redirecting the code generation to RegisterImplementationSourceOutput.
29.08.2023