EF6TempTableKit - how to deal with temporary tables in Entity Framework 6

Entity Framework is my favorite ORM. Beginning with a first Entity Framework version, developers are motivated to write queries against DB in a special set of technologies called LINQ.
T-SQL queries had written in stored procedures we moved into C#/LINQ to deal with data. No stored procedures, no weird string concatenation to build a dynamic query, everything in one place. Seems like everything is fine. But you are limited. You can't add table and index hints, you can't "force" entity framework to build for you query as you want.

I've found myself many times fixing bad performance on the generated T-SQL query. Usually, we start by execution plans, analyzing statistics, indexes, ending with rewriting C#/LINQ that makes a better query for us. But at some point, you are completely blocked. Some developers in such a situation are ready to rewrite C#/LINQ code back in stored procedures to get all needed T-SQL benefits.

In my case, fixing bad query which has DISTINCT in SELECT clause can be optimized by storing data in a temporary table and running SELECT DISTINCT against it. But, here is a problem - temp tables are not supported in Entity Framework.

Ok. But, how to deal with temporary tables in Entity Framework?

That question was a great motivation to see how can we handle temporary tables in Entity Framework. Before I started my little investigation and experiment, I had noticed that people are asking for such a feature.

Searching on the Internet I found some solutions, but none of these were wrapped into some Nuget package, tested, documented, easy to integrate... So I decided to try to see how can we use temp tables like we use permanent tables - in Entity Framework 6.

My goal was to write a simple extension that doesn't affect default Entity Framework behavior, where developers can keep their job in C#/LINQ with no building the dynamic strings.

The first thing I found are Entity Framework interceptors where you can intercept already generated T-SQL code and change it as you want. The right place to inject another T-SQL code.
The second thing is how to get T-SQL code from C#/LINQ query. Searching on the Internet I found a very useful method that is convenient for such a purpose. Drilling down through the Entity Framework source code I found more useful things needed for my project. All I need is to wrap all these, including some utilities, attributes and other custom code into something meaningful.

EF6TempTableKit - how to use temporary tables in your EF6 context

Introducing from a very simple to complex xUnit test scenarios, including integration tests I was ready to publish EF6TempTableKit which can deal with a temporary table in Entity Framework 6.

Additionally, it supports:

• clustered index
• nonclustered indexes
• reusing the same temporary tables under the same connection

How to use in code is demonstrated here, in a one-file example:

using EF6TempTableKit;
using EF6TempTableKit.Attributes;
using EF6TempTableKit.DbContext;
using EF6TempTableKit.Extensions;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;

namespace EF6TempTableKitOneFileExample
{
    class Program
    {
        static void Main(string[] args)
        {
            GetAddressFromTempTable();
        }

        private static void GetAddressFromTempTable()
        {
            using (var entityContext = new EntityContext())
            {
                var tempAddressQuery = entityContext.Addresses.Select(a => new AddressTempTableDto { AddressId = a.AddressId , StreetName = a.StreetName });

                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();

                foreach (var address in addressList)
                {
                    Console.WriteLine("AddressID = " + address.AddressId + " " + "StreetName = " + address.StreetName);
                }
            }

            Console.ReadLine();
        }
    }

    #region Context

    [DbConfigurationType(typeof(EF6TempTableKitDbConfiguration))]
    public class EntityContext : DbContext, IDbContextWithTempTable
    {
        public EntityContext() : base("EntityContext")
        {
            TempTableContainer = new TempTableContainer();
        }

        public TempTableContainer TempTableContainer { get; set; }
        public DbSet<AddressTempTable> AddressesTempTable { get; set; }
        public DbSet<Address> Addresses { get; set; }
    }

    #endregion

    #region Entities

    public class Address
    {
        public int AddressId { get; set; }
        public string StreetName { get; set; }
    }

    #region Temp entities

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

        [TempFieldTypeAttribute("varchar(200)")]
        public string StreetName { get; set; }
    }

    [NotMapped]
    public class AddressTempTableDto : AddressTempTable
    {
    }

    #endregion

    #endregion

    #region Helpers

    public class EntityContextInitializer : DropCreateDatabaseIfModelChanges<EntityContext>
    {
        protected override void Seed(EntityContext entityContext)
        {
            var addressList = new List<Address>();
            for (var i = 1; i < 50; i++)
            {
                addressList.Add(new Address() { AddressId = i, StreetName = "Street_" + i.ToString() });
            }

            entityContext.Addresses.AddRange(addressList);
            entityContext.SaveChanges();
        }
    }

    #endregion
}

Details and source code, as well, are published here

zblago / EF6TempTableKitOneFileExample

EF6TempTableKit - one file example

Very basic, simple, one-file Ef6TempTableKit demo.

Open your Visual Studio and press F5 is all you need to do.

Behind the scene you will get

1. DB on your local SQL Server
2. Demo data

Generated code looks like

View on GitHub

If you want to know more about it and how to integrate it into your existing project here are the details.