Reducing complexity in Entity Framework queries with IQuyerable and Linqkit

I have added a presentation written in Norwegian that explains how one can reduce complexity in Entity Framework, using IQuyerable decomposition and Linqkit functionality. Reduce Entity Framework

Asynchronous Entity Framework operations

Sample code:

using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;

namespace TestAsyncEntityFramework6
{
   
    public class CustomHelper
    {

        public static List<Customer> SelectAll()
        {
            using (var context = new NorthWindEntities())
            {
                var query = from c in context.Customers
                    orderby c.CustomerID ascending
                    select c;
                return query.ToList(); 
            }
        }

        public static async Task<List<Customer>> SelectAllAsync()
        {
            using (var context = new NorthWindEntities())
            {
                var query = from c in context.Customers
                    orderby c.CustomerID ascending
                    select c;
                return await query.ToListAsync();
            }
        }

        public static async Task<Customer> SelectByIdAsync(string id)
        {
            using (var context = new NorthWindEntities())
            {
                var query = from c in context.Customers
                    where c.CustomerID == id
                    select c;
                Customer obj = await query.SingleOrDefaultAsync();
                return obj;
            }
        }

        public static async Task<string> InsertAsync(Customer obj)
        {
            using (var context = new NorthWindEntities())
            {
                //try
                //{
                    context.Customers.Add(obj);
                    await context.SaveChangesAsync();
                    return "Customer added successfully!";
                //}
                //catch (Exception err)
                //{
                //    return "Crash"; 
                //}
            }
        }

        public static async Task<string> UpdateAsync(Customer obj)
        {
            using (var context = new NorthWindEntities())
            {
                Customer existing = await context.Customers.FindAsync(obj.CustomerID);
                existing.CompanyName = obj.CompanyName;
                existing.Country = obj.Country;
                await context.SaveChangesAsync();
                return "Customer updated successfully"; 
            }
        }

        public static async Task<string> DeleteAsync(string id)
        {
            using (var context = new NorthWindEntities())
            {
                Customer existing = await context.Customers.FindAsync(id);
                context.Customers.Remove(existing);
                await context.SaveChangesAsync();
                return "Customer deleted successfully!"; 
            }
        }

        public static async Task<Customer> GetCustomerByIdAsync(string id)
        {
            using (var context = new NorthWindEntities())
            {
                Customer existing = await context.Customers.FindAsync(id);
                return existing; 
            }
        }

    }
}


using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestAsyncEntityFramework6
{
    class Program
    {

        static void Main(string[] args)
        {
            Stopwatch sw = Stopwatch.StartNew();
            var task = CustomHelper.SelectAllAsync();
            task.Wait();
            Console.WriteLine("Got data!");
            List<Customer> data = task.Result;
            Console.WriteLine(data.Count);
            Console.WriteLine("Async op took: " + sw.ElapsedMilliseconds);
            sw.Stop();
            sw.Start();
            //data = 
            //var data =  CustomHelper.SelectAll();
            //Console.WriteLine("Got data!");
            //Console.WriteLine(data.Count);
            //Console.WriteLine("Sync operation took: " + sw.ElapsedMilliseconds);

            var c = new Customer {CustomerID = "TEIT", Country = "Burkina Faso", CompanyName = "Tore Aurstad IT"};
            try
            {
                var task2 = CustomHelper.InsertAsync(c);
                task2.Wait();
                Console.WriteLine(task2.Result);
            }
            catch (AggregateException ae)
            {
                Console.WriteLine(ae.Message);
            }

            var c3 = CustomHelper.GetCustomerByIdAsync("TEIT");
            c3.Wait();
            Console.WriteLine(c3.Result.Country);

            c3.Result.Country = "Norway"; 

            var c4 = CustomHelper.UpdateAsync(c3.Result);
            c4.Wait();
            Console.WriteLine(c4.Result);

            var c5 = CustomHelper.DeleteAsync(c3.Result.CustomerID);
            c5.Wait();
            Console.WriteLine(c5.Result); 

            Console.WriteLine("Press any key to continue ...");
            Console.ReadKey(); 
        }

    }
}

EntityFramework.Extended library performance capabilities

EntityFramework.Extended is an additional library that can be installed with Entity Framework 6 and give additional performance capabilities with Entity Framework, such as: - Batch updates - Batch deletes - Batch queries - Query caching The following demo code shows some simple usage of EntityFramework.Extended library to use these new capabilities which the "Core" Entity Framework lacks good support for:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using EntityFramework.Caching;
using EntityFramework.Extensions;

namespace TestEntityFrameworkExtended
{

    /// <summary>
    /// Samples based on the EntityFramework.Extended Github site here: https://github.com/loresoft/EntityFramework.Extended
    /// </summary>
    /// <remarks>Nuget package page here: https://www.nuget.org/packages/EntityFramework.Extended/ Created by LoreSoft, open source
    /// Entity Framework 6 assumed to be a requirement here (TODO: Inspect, don't expect)</remarks>
    class Program
    {

        static void Main()
        {
            DemoBatchUpdates();
            DemoBatchQueries();
            DemoQueryResultCache(); 
            DemoBatchDeletion();
        }

        private static void DemoQueryResultCache()
        {
            using (var ctx = new SampleDbContext())
            {
                var vipUsers =
                    ctx.Users.Where(u => u.MiddleName == "VIPUser").FromCache(
                        CachePolicy.WithDurationExpiration(TimeSpan.FromSeconds(60)), new[]{ "Viktigperer" });
                //PrintUsers("Query cache resultat:", vipUsers);
                foreach (var i in Enumerable.Range(1, 20))
                {
                    if (i == 10)
                        CacheManager.Current.Expire("Viktigperer");
                    vipUsers = ctx.Users.Where(u => u.MiddleName == "VIPUser").FromCache();
                    Console.WriteLine("# of Vip Users: " + vipUsers.Count());
                    Thread.Sleep(1000);
                }
            }

            //Note that the FromCache call now caches the vip users indefinately (no expiration), until one explicitly expires the 
            //tagged cache (or avoid using the FromCache extension). Therefore, tagging cached results are good practice such that the
            //cache can be evicted by using CacheManager.Current.Expire. The cache tags can be set to const strings for example
            //The FromCache makes it easy to cache particular result sets that are known to be accessed by many users and therefore
            //can be cached. For example type registers in OpPlan or Theaters. The database round trip will then be avoided and the 
            //result is cached in the w3wp process server side. Reducing traffic and returning cached results will scale better, but 
            //the cache will also use more memory. Be critical to what to cache (OpPlan 4 Theaters for example can be cached and then
            //expired on demand using CacheManager.Current.Expire, reducing overall traffic between OpPlanWAS and database and also
            //pressure on the CPU) 
            using (var ctx = new SampleDbContext())
            {
                var vipUsers =
                    ctx.Users.Where(u => u.MiddleName == "VIPUser").FromCache();
            }
        }

        private static void DemoBatchQueries()
        {
            using (var ctx = new SampleDbContext())
            {
                //Building up a batch query using the future extension methods. The first call using .Value or .ToList
                //will then start all batched queries, avoiding round trips to database 
                var aQuery = ctx.Users.Where(u => u.FirstName == "Pedro" && u.MiddleName == "VIPUser").FutureFirstOrDefault();
                var bQuery =
                    ctx.Users.Where(u => u.FirstName == "Olivini").FutureFirstOrDefault();
                User a = aQuery.Value;
                User b = bQuery.Value; 
                PrintUsers("Batch query results: ", new[]{ a, b});
            }
        }

        private static void DemoBatchUpdates()
        {
            using (var ctx = new SampleDbContext())
            {
                ctx.Users.Delete(); //clear 

                ctx.Users.Add(new User
                {
                    FirstName = "Pedro",
                    LastName = "Hauginho"
                });
                ctx.Users.Add(new User
                {
                    FirstName = "Julio",
                    LastName = "Cannevaro"
                });
                ctx.Users.Add(new User
                {
                    FirstName = "Jono",
                    MiddleName = "Pedro",
                    LastName = "Salvatini"
                });
                ctx.Users.Add(new User
                {
                    FirstName = "Olivini",
                    LastName = "Hepsado"
                });
                ctx.SaveChanges();

                ctx.Users.Where(u => u.FirstName.Length <= 4).Update(u => new User {MiddleName = "VIPUser"});  //Batch update
            }

            //Important! Reading the database AFTER a batch update should create a NEW db context instance to get refreshed data!
            using (var ctx = new SampleDbContext())
            {
                PrintUsers("Batch update result: ", ctx.Users);
            }
        }

        private static void PrintUsers(string header, IEnumerable<User> users)
        {
            Console.WriteLine(Environment.NewLine + header);
            foreach (var user in users)
            {
                Console.WriteLine("{0} {1} {2}", user.FirstName, user.MiddleName, user.LastName);
            }

            Console.WriteLine("Press the Any Key to Continue ... Now where's that Any Key?");
            Console.ReadKey();
        }

        private static void DemoBatchDeletion()
        {
            using (var ctx = new SampleDbContext())
            {

                ctx.Users.Delete(); //clear (first demo of batch delete)

                ctx.Users.Add(new User
                {
                    FirstName = "Rudolf",
                    LastName = "Holgedra"
                });
                ctx.Users.Add(new User
                {
                    FirstName = "Mario",
                    LastName = "Madraminho"
                });
                ctx.Users.Add(new User
                {
                    FirstName = "Vittorio",
                    MiddleName = "Pedro",
                    LastName = "Salinas"
                });
                ctx.Users.Add(new User
                {
                    FirstName = "Fernando",
                    LastName = "Torres"
                });
                ctx.SaveChanges();

                ctx.Users.Where(u => u.FirstName == "Mario" || u.MiddleName == "Pedro").Delete(); //second batch delete 

                PrintUsers("Batch delete users result: ", ctx.Users.ToList());

            }

        }

    }

}




using System.Data.Entity;

namespace TestEntityFrameworkExtended
{
    
    public class SampleDbContext : DbContext
    {

        public DbSet<User> Users { get; set; }

    }

}


namespace TestEntityFrameworkExtended
{
    
    public class User
    {

        public int UserId { get; set; }

        public string LastName { get; set; }

        public string FirstName { get; set; }

        public string MiddleName { get; set; }

    }

}

After running the code above, one experience from the batch updates was that one should create a new db context / object context such that the data is refreshed after the batch update is performed. The batch queries will send multiple queries towards the database simultanously. Note that after a batch delete, also a new db context / object context should be instantiated. The query caching mechanism will indefinately cache all queries with the same query content with the default FromCache() overload. To expire a query cache, use CacheManager.Current.Expire("MyCacheKey"). It is a good practice to tag the cache and also set a timeout expiration, which can be set either absolute or sliding. Caching queries will mean that your serverside will spend more memory, such as the w3wp worker process running WCF services. Do not cache large result sets and also be aware when the cache should be evicted / expired such that correct, fresh data can be reloaded. Using query caching, batch updates, batch deletes and batch queries, EntityFramework.Extended gives EF a good performance boost. Entity Framework should consider incorporating these features into EF, as they are general performance enhancements. Extra note - It is not necessary to create a new DbContext if you explicitly refresh the context like for example the following sample code:

    //Alternatively force update after batch update (same for batch delete)
    ObjectContext octx = ((IObjectContextAdapter) ctx).ObjectContext; 
    octx.Refresh(RefreshMode.StoreWins, ctx.Users);