entity framework與ADO.NET 2.0效率

拿entity framework與原始的ADO.NET 2.0對比看看。

我一直很猶豫該不該在專案使用entity framework，因為用ADO.NET 2.0，搭配codesmith開發效率也是挺高的，並且ADO.NET對團隊的素質要求相對沒entity framework那麼高，在公司資源有限，無法招攬些開發能力較高的程式設計師的情況下，是否該用entity framework呢？

這篇文章沒有要得出什麼結論，純粹是我學習entity framework的過程中的一些試驗筆記。

建了個表，對應的實體：

public class User
{
    [Key]
    public int UserId { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
}

1.執行：

    static void Main(string[] args)
    {
        using(MyContext mc = new MyContext())
        {
            User u = new User { Name = "test3", Address = "some address" };

            Console.WriteLine(DateTime.Now.ToLongTimeString());
            //mc.Configuration.AutoDetectChangesEnabled = false;
            mc.Users.Add(u);
            //mc.Configuration.AutoDetectChangesEnabled = true;
            Console.WriteLine(DateTime.Now.ToLongTimeString());
            mc.SaveChanges();
            Console.WriteLine(DateTime.Now.ToLongTimeString());
        }
    }

看起來，Add方法比較耗時；

2.把AutoDetectChangesEnabled設定為false執行：

    static void Main(string[] args)
    {
        using(MyContext mc = new MyContext())
        {
            User u = new User { Name = "test3", Address = "some address" };

            Console.WriteLine(DateTime.Now.ToLongTimeString());
            mc.Configuration.AutoDetectChangesEnabled = false;
            mc.Users.Add(u);
            mc.Configuration.AutoDetectChangesEnabled = true;
            Console.WriteLine(DateTime.Now.ToLongTimeString());
            mc.SaveChanges();
            Console.WriteLine(DateTime.Now.ToLongTimeString());
        }
    }

看起來效果差不多。

3.如果insert 10000行資料：

    static void Main(string[] args)
    {
        using(MyContext mc = new MyContext())
        {
            try
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString());
                //mc.Configuration.AutoDetectChangesEnabled = false;
                for (int i = 0; i < 10000; i++)
                {
                    User u = new User { Name = "test" + i.ToString(), Address = "some address" };
                    mc.Users.Add(u);
                }
            }
            finally
            {
                //mc.Configuration.AutoDetectChangesEnabled = true;
            }
            Console.WriteLine(DateTime.Now.ToLongTimeString());
            mc.SaveChanges();
            Console.WriteLine(DateTime.Now.ToLongTimeString());
        }
    }

看起來，Add方法特別耗時,注意：如果這裡使用mc.Users.AddRange(XXX)去insert10000行資料，速度就會比較快；

4.把AutoDetectChangesEnabled設定為false執行insert 10000行資料：

    static void Main(string[] args)
    {
        using(MyContext mc = new MyContext())
        {
            try
            {
                Console.WriteLine(DateTime.Now.ToLongTimeString());
                mc.Configuration.AutoDetectChangesEnabled = false;
                for (int i = 0; i < 10000; i++)
                {
                    User u = new User { Name = "test" + i.ToString(), Address = "some address" };
                    mc.Users.Add(u);
                }
            }
            finally
            {
                mc.Configuration.AutoDetectChangesEnabled = true;
            }
            Console.WriteLine(DateTime.Now.ToLongTimeString());
            mc.SaveChanges();
            Console.WriteLine(DateTime.Now.ToLongTimeString());
        }
    }

速度快很多。

5.普通ADO.NET insert 10000行資料：

    static void Main(string[] args)
    {
        using(MyContext mc = new MyContext())
        {
            string sql = "insert into Users(Name,Address) values (@Name,@Address) ";
            Console.WriteLine(DateTime.Now.ToLongTimeString());
            for (int i = 0; i < 10000; i++)
            {

                System.Data.SqlClient.SqlParameter[] paras = {

                 new System.Data.SqlClient.SqlParameter("@Name ",  System.Data.SqlDbType.NVarChar),
                 new System.Data.SqlClient.SqlParameter("@Address ",  System.Data.SqlDbType.NVarChar)
                };
                paras[0].Value = "test" + i.ToString();
                paras[0].Value = "some address";
                SqlHelper.sqlToExecute(sql, paras);
            }
            Console.WriteLine(DateTime.Now.ToLongTimeString());
        }
    }

速度確實是最快的。

---

以上是insert的，現在試試update 10000 行資料:

    static void Main(string[] args)
    {
        using(MyContext mc = new MyContext())
        {
            try
            {
                var lst = mc.Users.ToList();//資料庫有10000行資料
                Console.WriteLine(DateTime.Now.ToLongTimeString());
                //mc.Configuration.AutoDetectChangesEnabled = false;
                foreach (var a in lst)
                {
                    a.Address = a.Address + "1000";

                }
             
            }
            finally
            {
                //mc.Configuration.AutoDetectChangesEnabled = true;
            }
            Console.WriteLine(DateTime.Now.ToLongTimeString());
            mc.SaveChanges();
            Console.WriteLine(DateTime.Now.ToLongTimeString());
        }
    }

看起來，update的速度是很讓人滿意的。

---

那麼delete呢？

    static void Main(string[] args)
    {
        using(MyContext mc = new MyContext())
        {
            try
            {
                var lst = mc.Users.ToList();
                Console.WriteLine(DateTime.Now.ToLongTimeString());
                //mc.Configuration.AutoDetectChangesEnabled = false;
                foreach (var a in lst)
                {
                    mc.Users.Remove(a);
                }
                 //如果直接用mc.Users.RemoveRange(lst);，也會快的
            }
            finally
            {
                //mc.Configuration.AutoDetectChangesEnabled = true;
            }
            Console.WriteLine(DateTime.Now.ToLongTimeString());
            mc.SaveChanges();
            Console.WriteLine(DateTime.Now.ToLongTimeString());
        }
    }

看起來特別慢；

然而如果加上mc.Configuration.AutoDetectChangesEnabled = false，速度就上來了：

    static void Main(string[] args)
    {
      
        using(MyContext mc = new MyContext())
        {
            try
            {
                var lst = mc.Users.ToList();
                Console.WriteLine(DateTime.Now.ToLongTimeString());
                mc.Configuration.AutoDetectChangesEnabled = false;
                foreach (var a in lst)
                {
                    mc.Users.Remove(a);
                }
             
            }
            finally
            {
                mc.Configuration.AutoDetectChangesEnabled = true;
            }
            Console.WriteLine(DateTime.Now.ToLongTimeString());
            mc.SaveChanges();
            Console.WriteLine(DateTime.Now.ToLongTimeString());
        }
    }