/Dapper.Tests.Performance/LegacyTests.cs

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.Diagnostics;

using System.Linq;



using Belgrade.SqlClient;

using Dapper.Contrib.Extensions;

using Dapper.Tests.Performance.Dashing;

using Dapper.Tests.Performance.EntityFramework;

using Dapper.Tests.Performance.EntityFrameworkCore;

using Dapper.Tests.Performance.NHibernate;

using Dashing;

using DevExpress.Xpo;

using DevExpress.Data.Filtering;

using Massive;

using Microsoft.EntityFrameworkCore;

using NHibernate.Criterion;

using ServiceStack.OrmLite;

using ServiceStack.OrmLite.Dapper;

using System.Configuration;

using System.Threading.Tasks;

#if NET4X

using System.Data.Linq;

using Dapper.Tests.Performance.Linq2Sql;

using Dapper.Tests.Performance.Xpo;

using NHibernate.Linq;

using Susanoo;

#endif



namespace Dapper.Tests.Performance

{

    public class LegacyTests

    {

        private class Test

        {

            public Test(Action<int> iteration, string name)

            {

                Iteration = iteration;

                Name = name;

            }



            public Test(Func<int, Task> iterationAsync, string name)

            {

                IterationAsync = iterationAsync;

                Name = name;

            }



            public Action<int> Iteration { get; set; }

            public Func<int, Task> IterationAsync { get; set; }

            public string Name { get; set; }

            public Stopwatch Watch { get; set; }

        }



        private class Tests : List<Test>

        {

            public void Add(Action<int> iteration, string name)

            {

                Add(new Test(iteration, name));

            }



            public void AsyncAdd(Func<int, Task> iterationAsync, string name)

            {

                Add(new Test(iterationAsync, name));

            }



            public async Task RunAsync(int iterations)

            {

                // warmup 

                foreach (var test in this)

                {

                    test.Iteration?.Invoke(iterations + 1);

                    if (test.IterationAsync != null) await test.IterationAsync(iterations + 1).ConfigureAwait(false);

                    test.Watch = new Stopwatch();

                    test.Watch.Reset();

                }



                var rand = new Random();

                for (int i = 1; i <= iterations; i++)

                {

                    foreach (var test in this.OrderBy(ignore => rand.Next()))

                    {

                        test.Watch.Start();

                        test.Iteration?.Invoke(i);

                        if (test.IterationAsync != null) await test.IterationAsync(i).ConfigureAwait(false);

                        test.Watch.Stop();

                    }

                }



                Console.WriteLine("|Time|Framework|");

                foreach (var test in this.OrderBy(t => t.Watch.ElapsedMilliseconds))

                {

                    var ms = test.Watch.ElapsedMilliseconds.ToString();

                    Console.Write("|");

                    Console.Write(ms);

                    Program.WriteColor("ms ".PadRight(8 - ms.Length), ConsoleColor.DarkGray);

                    Console.Write("|");

                    Console.Write(test.Name);

                    Console.WriteLine("|");

                }

            }

        }



        public static string ConnectionString { get; } = ConfigurationManager.ConnectionStrings["Main"].ConnectionString;



        public static SqlConnection GetOpenConnection()

        {

            var connection = new SqlConnection(ConnectionString);

            connection.Open();

            return connection;

        }



#if NET4X

        private static DataClassesDataContext GetL2SContext(SqlConnection connection) =>

            new DataClassesDataContext(connection);

#endif



        private static void Try(Action action, string blame)

        {

            try

            {

                action();

            }

            catch (Exception ex)

            {

                Console.Error.WriteLine($"{blame}: {ex.Message}");

            }

        }



        public async Task RunAsync(int iterations)

        {

            using (var connection = GetOpenConnection())

            {

#pragma warning disable IDE0017 // Simplify object initialization

#pragma warning disable RCS1121 // Use [] instead of calling 'First'.

                var tests = new Tests();



                // Entity Framework Core

                Try(() =>

                {

                    var entityContext = new EFCoreContext(ConnectionString);

                    tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework Core");



                    var entityContext2 = new EFCoreContext(ConnectionString);

                    tests.Add(id => entityContext2.Posts.FromSql("select * from Posts where Id = {0}", id).First(), "Entity Framework Core: FromSql");



                    var entityContext3 = new EFCoreContext(ConnectionString);

                    tests.Add(id => entityContext3.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework Core: No Tracking");

                }, "Entity Framework Core");



                // Dapper

                Try(() =>

                {

                    var mapperConnection = GetOpenConnection();

                    tests.Add(id => mapperConnection.Query<Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Query (buffered)");

                    tests.Add(id => mapperConnection.Query<Post>("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Query (non-buffered)");

                    tests.Add(id => mapperConnection.QueryFirstOrDefault<Post>("select * from Posts where Id = @Id", new { Id = id }), "Dapper: QueryFirstOrDefault");



                    var mapperConnection2 = GetOpenConnection();

                    tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: true).First(), "Dapper: Dynamic Query (buffered)");

                    tests.Add(id => mapperConnection2.Query("select * from Posts where Id = @Id", new { Id = id }, buffered: false).First(), "Dapper: Dynamic Query (non-buffered)");

                    tests.Add(id => mapperConnection2.QueryFirstOrDefault("select * from Posts where Id = @Id", new { Id = id }), "Dapper: Dynamic QueryFirstOrDefault");



                    // dapper.contrib

                    var mapperConnection3 = GetOpenConnection();

                    tests.Add(id => mapperConnection3.Get<Post>(id), "Dapper.Contrib");

                }, "Dapper");



                // Massive

                Try(() =>

                {

                    var massiveModel = new DynamicModel(ConnectionString);

                    var massiveConnection = GetOpenConnection();

                    tests.Add(id => massiveModel.Query("select * from Posts where Id = @0", massiveConnection, id).First(), "Massive: Dynamic ORM Query");

                }, "Massive");



                // PetaPoco

                Try(() =>

                {

                    // PetaPoco test with all default options

                    var petapoco = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");

                    petapoco.OpenSharedConnection();

                    tests.Add(id => petapoco.Fetch<Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Normal");



                    // PetaPoco with some "smart" functionality disabled

                    var petapocoFast = new PetaPoco.Database(ConnectionString, "System.Data.SqlClient");

                    petapocoFast.OpenSharedConnection();

                    petapocoFast.EnableAutoSelect = false;

                    petapocoFast.EnableNamedParams = false;

                    petapocoFast.ForceDateTimesToUtc = false;

                    tests.Add(id => petapocoFast.Fetch<Post>("SELECT * from Posts where Id=@0", id).First(), "PetaPoco: Fast");

                }, "PetaPoco");



                // NHibernate

                Try(() =>

                {

                    var nhSession1 = NHibernateHelper.OpenSession();

                    tests.Add(id => nhSession1.CreateSQLQuery("select * from Posts where Id = :id")

                        .SetInt32("id", id)

                        .List(), "NHibernate: SQL");



                    var nhSession2 = NHibernateHelper.OpenSession();

                    tests.Add(id => nhSession2.CreateQuery("from Post as p where p.Id = :id")

                        .SetInt32("id", id)

                        .List(), "NHibernate: HQL");



                    var nhSession3 = NHibernateHelper.OpenSession();

                    tests.Add(id => nhSession3.CreateCriteria<Post>()

                        .Add(Restrictions.IdEq(id))

                        .List(), "NHibernate: Criteria");



                    var nhSession4 = NHibernateHelper.OpenSession();

                    tests.Add(id => nhSession4

                        .Query<Post>()

                        .First(p => p.Id == id), "NHibernate: LINQ");



                    var nhSession5 = NHibernateHelper.OpenSession();

                    tests.Add(id => nhSession5.Get<Post>(id), "NHibernate: Session.Get");

                }, "NHibernate");



                // Belgrade

                Try(() =>

                {

                    var query = new Belgrade.SqlClient.SqlDb.QueryMapper(ConnectionString);

                    tests.AsyncAdd(id => query.Sql("SELECT TOP 1 * FROM Posts WHERE Id = @Id").Param("Id", id).Map(

                        reader =>

                        {

                            var post = new Post();

                            post.Id = reader.GetInt32(0);

                            post.Text = reader.GetString(1);

                            post.CreationDate = reader.GetDateTime(2);

                            post.LastChangeDate = reader.GetDateTime(3);



                            post.Counter1 = reader.IsDBNull(4) ? null : (int?)reader.GetInt32(4);

                            post.Counter2 = reader.IsDBNull(5) ? null : (int?)reader.GetInt32(5);

                            post.Counter3 = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);

                            post.Counter4 = reader.IsDBNull(7) ? null : (int?)reader.GetInt32(7);

                            post.Counter5 = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);

                            post.Counter6 = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9);

                            post.Counter7 = reader.IsDBNull(10) ? null : (int?)reader.GetInt32(10);

                            post.Counter8 = reader.IsDBNull(11) ? null : (int?)reader.GetInt32(11);

                            post.Counter9 = reader.IsDBNull(12) ? null : (int?)reader.GetInt32(12);

                        }), "Belgrade Sql Client");

                }, "Belgrade Sql Client");



                //ServiceStack's OrmLite:

                Try(() =>

                {

                    var dbFactory = new OrmLiteConnectionFactory(ConnectionString, SqlServerDialect.Provider);

                    var db = dbFactory.Open();

                    tests.Add(id => db.SingleById<Post>(id), "ServiceStack.OrmLite: SingleById");

                }, "ServiceStack.OrmLite");



                // Hand Coded

                Try(() =>

                {

                    var postCommand = new SqlCommand()

                    {

                        Connection = connection,

                        CommandText = @"select Id, [Text], [CreationDate], LastChangeDate, 

                Counter1,Counter2,Counter3,Counter4,Counter5,Counter6,Counter7,Counter8,Counter9 from Posts where Id = @Id"

                    };

                    var idParam = postCommand.Parameters.Add("@Id", SqlDbType.Int);



                    tests.Add(id =>

                    {

                        idParam.Value = id;



                        using (var reader = postCommand.ExecuteReader())

                        {

                            reader.Read();

                            var post = new Post();

                            post.Id = reader.GetInt32(0);

                            post.Text = reader.GetNullableString(1);

                            post.CreationDate = reader.GetDateTime(2);

                            post.LastChangeDate = reader.GetDateTime(3);



                            post.Counter1 = reader.GetNullableValue<int>(4);

                            post.Counter2 = reader.GetNullableValue<int>(5);

                            post.Counter3 = reader.GetNullableValue<int>(6);

                            post.Counter4 = reader.GetNullableValue<int>(7);

                            post.Counter5 = reader.GetNullableValue<int>(8);

                            post.Counter6 = reader.GetNullableValue<int>(9);

                            post.Counter7 = reader.GetNullableValue<int>(10);

                            post.Counter8 = reader.GetNullableValue<int>(11);

                            post.Counter9 = reader.GetNullableValue<int>(12);

                        }

                    }, "Hand Coded");



                    var table = new DataTable

                    {

                        Columns =

                    {

                        {"Id", typeof (int)},

                        {"Text", typeof (string)},

                        {"CreationDate", typeof (DateTime)},

                        {"LastChangeDate", typeof (DateTime)},

                        {"Counter1", typeof (int)},

                        {"Counter2", typeof (int)},

                        {"Counter3", typeof (int)},

                        {"Counter4", typeof (int)},

                        {"Counter5", typeof (int)},

                        {"Counter6", typeof (int)},

                        {"Counter7", typeof (int)},

                        {"Counter8", typeof (int)},

                        {"Counter9", typeof (int)},

                    }

                    };

                    tests.Add(id =>

                    {

                        idParam.Value = id;

                        object[] values = new object[13];

                        using (var reader = postCommand.ExecuteReader())

                        {

                            reader.Read();

                            reader.GetValues(values);

                            table.Rows.Add(values);

                        }

                    }, "DataTable via IDataReader.GetValues");

                }, "Hand Coded");



                // DevExpress.XPO

                Try(() =>

                {

                    IDataLayer dataLayer = XpoDefault.GetDataLayer(connection, DevExpress.Xpo.DB.AutoCreateOption.SchemaAlreadyExists);

                    dataLayer.Dictionary.GetDataStoreSchema(typeof(Xpo.Post));

                    UnitOfWork session = new UnitOfWork(dataLayer, dataLayer);

                    session.IdentityMapBehavior = IdentityMapBehavior.Strong;

                    session.TypesManager.EnsureIsTypedObjectValid();



                    tests.Add(id => session.Query<Xpo.Post>().First(p => p.Id == id), "DevExpress.XPO: Query<T>");

                    tests.Add(id => session.GetObjectByKey<Xpo.Post>(id, true), "DevExpress.XPO: GetObjectByKey<T>");

                    tests.Add(id =>

                    {

                        CriteriaOperator findCriteria = new BinaryOperator()

                        {

                            OperatorType = BinaryOperatorType.Equal,

                            LeftOperand = new OperandProperty("Id"),

                            RightOperand = new ConstantValue(id)

                        };

                        session.FindObject<Xpo.Post>(findCriteria);

                    }, "DevExpress.XPO: FindObject<T>");

                }, "DevExpress.XPO");



                // Entity Framework

                Try(() =>

                {

                    var entityContext = new EFContext(connection);

                    tests.Add(id => entityContext.Posts.First(p => p.Id == id), "Entity Framework");



                    var entityContext2 = new EFContext(connection);

                    tests.Add(id => entityContext2.Database.SqlQuery<Post>("select * from Posts where Id = {0}", id).First(), "Entity Framework: SqlQuery");



                    var entityContext3 = new EFContext(connection);

                    tests.Add(id => entityContext3.Posts.AsNoTracking().First(p => p.Id == id), "Entity Framework: No Tracking");

                }, "Entity Framework");



#if NET4X

                // Linq2SQL

                Try(() =>

                {

                    var l2scontext1 = GetL2SContext(connection);

                    tests.Add(id => l2scontext1.Posts.First(p => p.Id == id), "Linq2Sql: Normal");



                    var l2scontext2 = GetL2SContext(connection);

                    var compiledGetPost = CompiledQuery.Compile((Linq2Sql.DataClassesDataContext ctx, int id) => ctx.Posts.First(p => p.Id == id));

                    tests.Add(id => compiledGetPost(l2scontext2, id), "Linq2Sql: Compiled");



                    var l2scontext3 = GetL2SContext(connection);

                    tests.Add(id => l2scontext3.ExecuteQuery<Post>("select * from Posts where Id = {0}", id).First(), "Linq2Sql: ExecuteQuery");

                }, "LINQ-to-SQL");



                // Dashing

                Try(() =>

                {

                    var config = new DashingConfiguration();

                    var database = new SqlDatabase(config, ConnectionString);

                    var session = database.BeginTransactionLessSession(GetOpenConnection());

                    tests.Add(id => session.Get<Dashing.Post>(id), "Dashing Get");

                }, "Dashing");



                //Susanoo

                Try(() =>

                {

                    var susanooDb = new DatabaseManager(connection);



                    var susanooPreDefinedCommand =

                        CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)

                            .DefineResults<Post>()

                            .Realize();



                    var susanooDynamicPreDefinedCommand =

                        CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)

                            .DefineResults<dynamic>()

                            .Realize();



                    tests.Add(Id =>

                        CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)

                            .DefineResults<Post>()

                            .Realize()

                            .Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Cache Retrieval");



                    tests.Add(Id =>

                        CommandManager.Instance.DefineCommand("SELECT * FROM Posts WHERE Id = @Id", CommandType.Text)

                            .DefineResults<dynamic>()

                            .Realize()

                            .Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Cache Retrieval");



                    tests.Add(Id => susanooDynamicPreDefinedCommand

                            .Execute(susanooDb, new { Id }).First(), "Susanoo: Dynamic Mapping Static");



                    tests.Add(Id => susanooPreDefinedCommand

                            .Execute(susanooDb, new { Id }).First(), "Susanoo: Mapping Static");

                }, "Susanoo");

#endif



                // Subsonic isn't maintained anymore - doesn't import correctly

                //Try(() =>

                //    {

                //    // Subsonic ActiveRecord 

                //    tests.Add(id => 3SubSonic.Post.SingleOrDefault(x => x.Id == id), "SubSonic ActiveRecord.SingleOrDefault");



                //    // Subsonic coding horror

                //    SubSonic.tempdbDB db = new SubSonic.tempdbDB();

                //    tests.Add(id => new SubSonic.Query.CodingHorror(db.Provider, "select * from Posts where Id = @0", id).ExecuteTypedList<Post>(), "SubSonic Coding Horror");

                //}, "Subsonic");



                //// BLToolkit - doesn't import correctly in the new .csproj world

                //var db1 = new DbManager(GetOpenConnection());

                //tests.Add(id => db1.SetCommand("select * from Posts where Id = @id", db1.Parameter("id", id)).ExecuteList<Post>(), "BLToolkit");



                Console.WriteLine();

                Console.WriteLine("Running...");

                await tests.RunAsync(iterations).ConfigureAwait(false);

#pragma warning restore RCS1121 // Use [] instead of calling 'First'.

#pragma warning restore IDE0017 // Simplify object initialization

            }

        }

    }

}