using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Objects;
using System.Diagnostics;
using System.Linq;
using System.Web;
using System.Web.Caching;

namespace EfSecondLevelCaching.Core
{
    public static class EfHttpRuntimeCacheProvider
    {
        #region Methods (6)

        // Public Methods (2) 

        public static IList<TEntity> ToCacheableList<TEntity>(
                            this IQueryable<TEntity> query,
                            int durationMinutes = 15,
                            CacheItemPriority priority = CacheItemPriority.Normal)
        {
            return query.Cacheable(x => x.ToList(), durationMinutes, priority);
        }

        /// <summary>
        /// Returns the result of the query; if possible from the cache, otherwise
        /// the query is materialized and the result cached before being returned.
        /// The cache entry has a one minute sliding expiration with normal priority.
        /// </summary>
        public static TResult Cacheable<TEntity, TResult>(
                            this IQueryable<TEntity> query,
                            Func<IQueryable<TEntity>, TResult> materializer,
                            int durationMinutes = 15,
                            CacheItemPriority priority = CacheItemPriority.Normal)
        {
            // Gets a cache key for a query.
            var queryCacheKey = query.GetCacheKey();

            // The name of the cache key used to clear the cache. All cached items depend on this key.
            var rootCacheKey = typeof(TEntity).FullName;

            // Try to get the query result from the cache.
            printAllCachedKeys();
            var result = HttpRuntime.Cache.Get(queryCacheKey);
            if (result != null)
            {
                debugWriteLine("Fetching object '{0}__{1}' from the cache.", rootCacheKey, queryCacheKey);
                return (TResult)result;
            }

            // Materialize the query.
            result = materializer(query);

            // Adding new data.
            debugWriteLine("Adding new data: queryKey={0}, dependencyKey={1}", queryCacheKey, rootCacheKey);
            storeRootCacheKey(rootCacheKey);
            HttpRuntime.Cache.Insert(
                    key: queryCacheKey,
                    value: result,
                    dependencies: new CacheDependency(null, new[] { rootCacheKey }),
                    absoluteExpiration: DateTime.Now.AddMinutes(durationMinutes),
                    slidingExpiration: Cache.NoSlidingExpiration,
                    priority: priority,
                    onRemoveCallback: null);

            return (TResult)result;
        }

        /// <summary>
        /// Call this method in `public override int SaveChanges()` of your DbContext class 
        /// to Invalidate Second Level Cache automatically.
        /// </summary>        
        public static void InvalidateSecondLevelCache(this DbContext ctx)
        {
            var changedEntityNames = ctx.ChangeTracker
                                      .Entries()
                                      .Where(x => x.State == EntityState.Added ||
                                                  x.State == EntityState.Modified ||
                                                  x.State == EntityState.Deleted)
                                      .Select(x => ObjectContext.GetObjectType(x.Entity.GetType()).FullName)
                                      .Distinct()
                                      .ToList();

            if (!changedEntityNames.Any()) return;

            printAllCachedKeys();
            foreach (var item in changedEntityNames)
            {
                item.removeEntityCache();
            }
            printAllCachedKeys();
        }
        // Private Methods (4) 

        private static void debugWriteLine(string format, params object[] args)
        {
            if (!Debugger.IsAttached) return;
            Debug.WriteLine(format, args);
        }

        private static void printAllCachedKeys()
        {
            if (!Debugger.IsAttached) return;
            debugWriteLine("Available cached keys list:");
            int count = 0;
            var enumerator = HttpRuntime.Cache.GetEnumerator();
            while (enumerator.MoveNext())
            {
                if (enumerator.Key.ToString().StartsWith("__")) continue; // such as __System.Web.WebPages.Deployment
                debugWriteLine("queryKey: {0}", enumerator.Key.ToString());
                count++;
            }
            debugWriteLine("count: {0}", count);
        }

        private static void removeEntityCache(this string rootCacheKey)
        {
            if (string.IsNullOrWhiteSpace(rootCacheKey)) return;
            debugWriteLine("Removing items with dependencyKey={0}", rootCacheKey);
            // Removes all cached items depend on this key.
            HttpRuntime.Cache.Remove(rootCacheKey);
        }

        private static void storeRootCacheKey(string rootCacheKey)
        {
            // The cacheKeys of a cacheDependency that are not already in cache ARE NOT inserted into the cache 
            // on the Insert of the item in which the dependency is used.
            if (HttpRuntime.Cache.Get(rootCacheKey) != null)
                return;

            HttpRuntime.Cache.Add(
                rootCacheKey,
                rootCacheKey,
                null,
                Cache.NoAbsoluteExpiration,
                Cache.NoSlidingExpiration,
                CacheItemPriority.Default,
                null);
        }

        #endregion Methods
    }
}

var data = context.Products.AsQueryable().Cacheable(x => x.FirstOrDefault());

using System.Data.Entity;
using EfSecondLevelCaching.Core;
using EfSecondLevelCaching.Test.Models;

namespace EfSecondLevelCaching.Test.DataLayer
{
    public class ProductContext : DbContext
    {
        public DbSet<Product> Products { get; set; }

        public override int SaveChanges()
        {
            this.InvalidateSecondLevelCache();
            return base.SaveChanges();
        }        
    }
}

using System.Data.Entity;
using System.Linq;
using EfSecondLevelCaching.Core;
using EfSecondLevelCaching.Test.DataLayer;
using EfSecondLevelCaching.Test.Models;
using System;

namespace EfSecondLevelCaching
{
    public static class TestUsages
    {
        public static void RunQueries()
        {
            using (ProductContext context = new ProductContext())
            {
                var isActive = true;
                var name = "Product1";

                // reading from db
                var list1 = context.Products
                                   .OrderBy(one => one.ProductNumber)
                                   .Where(x => x.IsActive == isActive && x.ProductName == name)
                                   .ToCacheableList();

                // reading from cache
                var list2 = context.Products
                                   .OrderBy(one => one.ProductNumber)
                                   .Where(x => x.IsActive == isActive && x.ProductName == name)
                                   .ToCacheableList();

                // reading from cache
                var list3 = context.Products
                                   .OrderBy(one => one.ProductNumber)
                                   .Where(x => x.IsActive == isActive && x.ProductName == name)
                                   .ToCacheableList();

                // reading from db
                var list4 = context.Products
                                   .OrderBy(one => one.ProductNumber)
                                   .Where(x => x.IsActive == isActive && x.ProductName == "Product2")
                                   .ToCacheableList();
            }

            // removes products cache
            using (ProductContext context = new ProductContext())
            {
                var p = new Product()
                {
                    IsActive = false,
                    ProductName = "P4",
                    ProductNumber = "004"
                };
                context.Products.Add(p);
                context.SaveChanges();
            }

            using (ProductContext context = new ProductContext())
            {
                var data = context.Products.AsQueryable().Cacheable(x => x.FirstOrDefault());
                var data2 = context.Products.AsQueryable().Cacheable(x => x.FirstOrDefault());
                context.SaveChanges();
            }
        }
    }
}

Adding new data: queryKey=72AF5DA1BA9B91E24DCCF83E88AD1C5F, dependencyKey=EfSecondLevelCaching.Test.Models.Product

Available cached keys list:
queryKey: EfSecondLevelCaching.Test.Models.Product
queryKey: 72AF5DA1BA9B91E24DCCF83E88AD1C5F
count: 2

Fetching object 'EfSecondLevelCaching.Test.Models.Product__72AF5DA1BA9B91E24DCCF83E88AD1C5F' from the cache.

Available cached keys list:
queryKey: EfSecondLevelCaching.Test.Models.Product
queryKey: 72AF5DA1BA9B91E24DCCF83E88AD1C5F
count: 2

Fetching object 'EfSecondLevelCaching.Test.Models.Product__72AF5DA1BA9B91E24DCCF83E88AD1C5F' from the cache.

Available cached keys list:
queryKey: EfSecondLevelCaching.Test.Models.Product
queryKey: 72AF5DA1BA9B91E24DCCF83E88AD1C5F
count: 2

Adding new data: queryKey=11A2C33F9AD7821A0A31003BFF1DF886, dependencyKey=EfSecondLevelCaching.Test.Models.Product

Available cached keys list:
queryKey: 72AF5DA1BA9B91E24DCCF83E88AD1C5F
queryKey: 11A2C33F9AD7821A0A31003BFF1DF886
queryKey: EfSecondLevelCaching.Test.Models.Product
count: 3

Removing items with dependencyKey=EfSecondLevelCaching.Test.Models.Product
Available cached keys list:
count: 0
Available cached keys list:
count: 0

Adding new data: queryKey=02E6FE403B461E45C5508684156C1D10, dependencyKey=EfSecondLevelCaching.Test.Models.Product

Available cached keys list:
queryKey: 02E6FE403B461E45C5508684156C1D10
queryKey: EfSecondLevelCaching.Test.Models.Product
count: 2


Fetching object 'EfSecondLevelCaching.Test.Models.Product__02E6FE403B461E45C5508684156C1D10' from the cache.

SELECT * FROM table
ORDER BY NEWID()

using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;

namespace Sample
{
    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<User> Users { get; set; }
    }

    public class Configuration : DbMigrationsConfiguration<MyContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
        }

        protected override void Seed(MyContext context)
        {
            context.Users.Add(new User { Name = "User 1", Age = 20 });
            context.Users.Add(new User { Name = "User 2", Age = 25 });
            context.Users.Add(new User { Name = "User 3", Age = 30 });
            context.Users.Add(new User { Name = "User 4", Age = 35 });
            context.Users.Add(new User { Name = "User 5", Age = 40 });
            base.Seed(context);
        }
    }

    public static class Test
    {
        public static void RunTests()
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>());

            using (var context = new MyContext())
            {
               var randomListOfUsers =
                        context.Users
                               .Where(person => person.Age >= 25 && person.Age < 40)
                               .OrderBy(person => Guid.NewGuid())
                               .ToList();

               foreach (var person in randomListOfUsers)
                   Console.WriteLine("{0}:{1}", person.Name, person.Age);
            }
        }
    }
}

.OrderBy(person => Guid.NewGuid())

ORDER BY NEWID()

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[Name] AS [Name], 
[Project1].[Age] AS [Age]
FROM ( SELECT 
NEWID() AS [C1], ------ Guid created here
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Age] AS [Age]
FROM [dbo].[Users] AS [Extent1]
WHERE ([Extent1].[Age] >= 25) AND ([Extent1].[Age] < 40)
)  AS [Project1]
ORDER BY [Project1].[C1] ASC  ------ Used for sorting here

USE [WideWorldImporters];
GO

SET STATISTICS IO ON;
GO


/*
Query with a hash join
*/
SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Warehouse].[StockItems] [si]
    JOIN [Sales].[OrderLines] [ol]
    ON [si].[StockItemID] = [ol].[StockItemID];
GO

ALTER TABLE [Warehouse].[StockItems] ADD  CONSTRAINT [PK_Warehouse_StockItems] PRIMARY KEY CLUSTERED
(
   [StockItemID] ASC
)

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCX_Sales_OrderLines] ON [Sales].[OrderLines]
(
[OrderID],
[StockItemID],
[Description],
[Quantity],
[UnitPrice],
[PickedQuantity]
)

CREATE NONCLUSTERED INDEX [IX_OrderLines_StockItemID]
ON [Sales].[OrderLines](
[StockItemID] ASC,
[PickedQuantity] ASC,
[OrderID])
ON [PRIMARY];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol]
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID]
OPTION
(RECOMPILE);
GO

CREATE NONCLUSTERED INDEX [IX_OrderLines_StockItemID_Compressed]
ON [Sales].[OrderLines](
[StockItemID] ASC,
[PickedQuantity] ASC,
[OrderID])
WITH (DATA_COMPRESSION = PAGE)
ON [PRIMARY];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol]
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID]
OPTION
(RECOMPILE);
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol] WITH (INDEX (IX_Sales_OrderLines_Perf_20160301_02))
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol] WITH (INDEX (IX_OrderLines_StockItemID))
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol] WITH (INDEX (IX_OrderLines_StockItemID_Compressed))
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID];
GO

SELECT COUNT(*)
FROM [Sales].[Orders];

SELECT COUNT_BIG (*)
FROM [Sales].[Orders];

CREATE FUNCTION dbo.CountProductsSold (
@SalesPersonID INT
) RETURNS INT

AS

BEGIN
    DECLARE @SoldCount INT;

    SELECT @SoldCount = COUNT(DISTINCT [ol].[StockItemID])
    FROM [Sales].[Orders] [o]
        JOIN [Sales].[OrderLines] [ol]
        ON [o].[OrderID] = [ol].[OrderID]
    WHERE [o].[SalespersonPersonID] = @SalesPersonID

    RETURN (@SoldCount);

END

SELECT
    [FullName] AS [SalesPerson],
    [dbo].[CountProductsSold]([PersonID]) AS [NumberOfProductsSold]
FROM [Application].[People]
WHERE [IsSalesperson] = 1;

SELECT
    [fs].[last_execution_time],
    [fs].[execution_count],
    [fs].[total_logical_reads]/[fs].[execution_count] [AvgLogicalReads],
    [fs].[max_logical_reads],
    [t].[text],
    [p].[query_plan]
FROM sys.dm_exec_function_stats [fs]
CROSS APPLY sys.dm_exec_sql_text([fs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([fs].[plan_handle]) [p];

 CREATE PRIMARY XML INDEX invoice_idx ON xmlInvoice3(invoice)
 SELECT * FROM sys.internal_tables

 SELECT * FROM sys.xml_index_nodes_325576198_256000

 sp_spaceused 'xmlInvoice3'

 --DROP INDEX invoice_idx ON xmlInvoice3

 SELECT * FROM xmlInvoice
WHERE invoice.exist('/Invoice[@InvoiceId = "1003"]') = 1

SELECT * FROM xmlInvoice3
WHERE invoice.exist('/Invoice[@InvoiceId = "1003"]') = 1

 CREATE XML INDEX invoice_path_idx ON xmlInvoice3(invoice)
 USING XML INDEX invoice_idx FOR PATH

 CREATE XML INDEX invoice_value_idx ON xmlInvoice3(invoice)
 USING XML INDEX invoice_idx FOR VALUE
 
 CREATE XML INDEX invoice_prop_idx ON xmlInvoice3(invoice)
 USING XML INDEX invoice_idx FOR PROPERTY

 SELECT * FROM xmlInvoice3
WHERE invoice.exist('/Invoice//CustomerName[text() = "Vahid"]') = 1

 SELECT * FROM xmlInvoice3
WHERE invoice.exist('/Invoice//CustomerName[. = "Vahid"]') = 1