How to create and use temp table in Entity Framework relying only on C#/LINQ

Question

Entity Framework is my first choice of O/RMs. We can write our queries against DB using C#/LINQ. We can even create our model in C# and easily migrate it into DB. No custom data access-code, no mixing data-access code with a huge SQL string, ...

However, sometimes I was faced against bad performances due to bad query generated by Entity Framework, including an error like

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query.

Suggestions like here are helpful, but we are limited as we can't create temp table entities in Entity Framework context.

So, I started asking myself how to introduce temp tables in Entity Framework?

Is there any easy way to do this?

Answer 1

Of course, there is a way to introduce temporary tables in Entity Framework context.

But, before I started explaining how to use temp tables in Entity Framework context I want to explain what was my goal in order to achieve such a feature.

So, let's start...

My goal was to add an entity that represents temp table from DB and write a LINQ query to load the data into it. Later on, in code, I wanted to use it in LINQ as I'm using any other entity that represents a permanent table.

Seems like steps to achieve it are very straightforward looking from T-SQL perspective:

1. create a temp table
2. load the data into it
3. use it later in the code

but from C# it's not so trivial because I had to find out:

1. how to get translated from LINQ T-SQL query with all the parameters
2. how to declare entity that represents temp table in DB and distinguish it from other entities
3. how to load data into temp entity
4. how to get parameter order from LINQ query being used to load the data into temp table
5. how to handle dependencies between temp tables
6. how to attach prepared "temp query" into DbContext
7. ....

Anyway, after some digging through the Entity Framework 6 source code and searching on the Internet I found what do I need and, finally, I've got proof and concept followed by the first version.

Here is a very simple usage:

1. declare a temp entity

   [Table("#tempAddress")]
   public class AddressTempTable : ITempTable
   {
       [Key]
       [TempFieldTypeAttribute("int")]
       public int AddressId { get; set; }
   
       [TempFieldTypeAttribute("varchar(200)")]
       public string StreetName { get; set; }
   }
   

2. add an entity that represents temp table in DbSet collection

   public DbSet<AddressTempTable> AddressesTempTable { get; set; }
   

3. load the data into it

   var tempAddressQuery = entityContext.Addresses.Select(a => new AddressTempTableDto { AddressId = a.AddressId , StreetName = a.StreetName });
   

4. Attach and use it

   var addressList = entityContext
      .WithTempTableExpression<EntityContext>(tempAddressQuery)
      .AddressesTempTable.Join(entityContext.Addresses,
      (a) => a.AddressId,
      (aa) => aa.AddressId,
      (at, a) => new 
      {
           AddressId = at.AddressId,
           StreetName = a.StreetName
       }).ToList();
   

I published my solution in a form of Nuget package called - EF6TempTableKit.

Here is a very basic example.

Source code is available here .