Entity Framework 通过主键查询提高效率

Entity Framework 通过主键查询提高效率

第一部分：重复查询单个实体

第一种：Linq To Entitiess

代码如下：

  static void Main(string[] args)
        {
            DateTime time1;
            DateTime time2;
            time1 = DateTime.Now;
            NorthwindEntities context = new NorthwindEntities();
            for (int i = 0; i < 1000; i++)
            {
                var data = (from c in context.Customers where c.CustomerID == “ALFKI” select c).FirstOrDefault();
                string addr = data.Address;
            }
            time2 = DateTime.Now;
            Console.WriteLine((time2-time1).ToString());
        }

查询使用时间为6.2秒左右

第二种：使用Entity SQL

  static void Main(string[] args)
        {
            DateTime time1;
            DateTime time2;
            time1 = DateTime.Now;
            NorthwindEntities context = new NorthwindEntities();
            for (int i = 0; i < 1000; i++)
            {
                var data = context.Customers.Where(“it.CustomerID=@Id”, new System.Data.Objects.ObjectParameter(“Id”, “ALFKI”)).FirstOrDefault();
                string addr = data.Address;
            }
            time2 = DateTime.Now;
            Console.WriteLine((time2-time1).ToString());
        }

查询使用时间为6.2秒左右

第三种：使用EntityKey 来查询

static void Main(string[] args)
        {
            DateTime time1;
            DateTime time2;
            time1 = DateTime.Now;
            NorthwindEntities context = new NorthwindEntities();
            for (int i = 0; i < 1000; i++)
            {
                var data = context.GetObjectByKey(new System.Data.EntityKey(“NorthwindEntities.Customers”, “CustomerID”, “ALFKI”)) as Customers;
                string addr = data.Address;
            }
            time2 = DateTime.Now;
            Console.WriteLine((time2-time1).ToString());
        }

查询使用时间为1秒，没错是一秒

总结：

前两种方法查询所使用的时间都差不多为6.2秒，时间比较长，但是使用第三种方法查询数据仅仅使用了1秒，为什么会相差那么多，区别在于前两种方法每次查询都要从数据库中查找，1000次查询每次的查询时间都一样，但是第三种方法只有第一次是从数据库中查数据，后面的999次都是在context通过主键直接取数据，速度当然会快很多啦，EF4.0之前的实体当含有外键引用时是自动生成对象引用的，而EF4.0现在多了一个外键ID，这样我们可以很方便的通过外键来查询数据

第二部分：查询不同是实体

第一种：Linq To Entitiess

static void Main(string[] args)
        {
            DateTime time1;
            DateTime time2;
            NorthwindEntities context = new NorthwindEntities();
            //先将Customers所有的主键查询出来
            List<string> keys = context.Customers.Select(c => c.CustomerID).Distinct().ToList();
            //和上面的context以作区别
            NorthwindEntities  context_ = new NorthwindEntities();
             time1 = DateTime.Now;
            foreach (var key in keys)
            {
                var data = context_.Customers.Where(c => c.CustomerID == key).FirstOrDefault();
                string addr = data.Address;
            }
            time2 = DateTime.Now;
            Console.WriteLine((time2 – time1).ToString());
        }

查询使用时间0.68秒左右

第二种：使用Entity SQL

static void Main(string[] args)
        {
            DateTime time1;
            DateTime time2;
            NorthwindEntities context = new NorthwindEntities();
            //先将Customers所有的主键查询出来
            List<string> keys = context.Customers.Select(c => c.CustomerID).Distinct().ToList();
            //和上面的context以作区别
            NorthwindEntities  context_ = new NorthwindEntities();
            time1 = DateTime.Now;
            foreach (var key in keys)
            {
                var data = context_.Customers.Where(“it.CustomerID=@Id”, new System.Data.Objects.ObjectParameter(“Id”, key)).FirstOrDefault();
                string addr = data.Address;
            }
            time2 = DateTime.Now;
            Console.WriteLine((time2 – time1).ToString());
        }

查询使用时间0.5秒左右

第三种：使用EntityKey 来查询

  static void Main(string[] args)
        {
            DateTime time1;
            DateTime time2;
            NorthwindEntities context = new NorthwindEntities();
            //先将Customers所有的主键查询出来
            List<string> keys = context.Customers.Select(c => c.CustomerID).Distinct().ToList();
            //和上面的context以作区别
            NorthwindEntities  context_ = new NorthwindEntities();
            time1 = DateTime.Now;
            foreach (var key in keys)
            {
                var data = context_.GetObjectByKey(new System.Data.EntityKey(“NorthwindEntities.Customers”, “CustomerID”, key)) as Customers;
                string addr = data.Address;
            }
            time2 = DateTime.Now;
            Console.WriteLine((time2 – time1).ToString());
        }

查询时间为0.18秒左右

 public static class DbContextExtension
    {
        public static DbContext GetDbContext(this ObjectContext objectContext)
        {
            return new DbContext(objectContext, true);
        }

        public static ObjectContext GetObjectContext(this DbContext dbContext)
        {
            ObjectContext context = ((IObjectContextAdapter)dbContext).ObjectContext;
            return context;
        }
    }

 

总结：

通过比较第三种方法仍然比前两种来的快，查询效率更高

使用EntityKey来查询数据比其他两种方法来的更快，而且不是快一点点，而是相差有好几倍，这里的比较并没有使用存储过程来比较查询，但是从这里我们可以看出，在平常的应用中如果知道实体的主键尽量用主键来查询

转载于:https://www.cnblogs.com/hbzzws/articles/2658217.html