EF 最基础的性能优化

Select * From [Table] 查询数据最糟糕的形式（the below query should almost never make it into production )

EF存在复杂属性的时候，先查询单个，在从单个中查询数据，而不是连表

var testQuery = db.Alerts   // db is the instance of the DbContext

.Where(x => x.ID == 1)
.Include(y => y.Company);

改进方式

var testQuery = db.Alerts.Where(x => x.ID == 1)
.Select(y => new {
CreateDate = y.CreateDate,
Company = y.Company.Name
});

不要在查询之后Count，否则会加载所有的实体

var empCount = db.Employees
.Where(x => x.CreateDate < DateTime.Now.AddDays(-10)
.Count() //only executes a SQL COUNT
var emps = db.Employees
.Where(x => x.CreateDate < DateTime.Now.AddDays(-10)
var empCount = emps.Count() // returns all matching employees and then executes a count

改进方式

在做了过滤之后再Tolist。（ToList加载到内存）

Filter Before ToList()

删除方式选择：第一种加载所有属性，第二种只加载Id

var emp = db.Employees.Find(empID); //Executes a query against the database

db.Employees.Remove(emp);
db.SaveChanges(); //Executes a second hit to the database

Instead of this, create a temporary entity and use Attach() to attach it to the context and then call Remove (it is not relevant that the entity will not have all its properties loaded – all that is needed for the Remove() method is the primary key):

var emp = new Employee { ID = empID }
db.Employees.Attach(emp);
db.Employees.Remove(emp);
db.SaveChanges(); //Executes a single hit to the database

Snapshot Change Tracking(在SaveChange时跟踪变化)

Change Tracking Proxies(在运行时生成代理，当改变时通知EF)

适度关闭自动检测变化方法

• In circumstances where there are a lot of entities being tracked in memory and when you are sure that the existing entities will not be changed it may make sense to test the performance improvement from briefly turning of change tracking:

  db.Configuration.AutoDetectChangesEnabled = false;
  db.Employees.Add(new Employee {name=”jude”});
  db.Employees.SaveChanges();
  db.Configuration.AutoDetectChangesEnabled = true;

   

   

  什么时候不用延迟加载

  var employees = db.Employees;
  foreach(var emp in employees)
  {
  var address = emp.Address; //Database query is executed every time this line is hit
  }

  var employees = db.Employees .Include(x => x.Address) .ToList(); //All data loaded from database
  foreach(var emp in employees)
  {
  var address = emp.Address; //No database query executed
  }

  关闭延迟加载

  public MyContext() : base() {     this.Configuration.LazyLoadingEnabled = false; } 

  EF keeps track of in-memory entities so retrieving an entity from memory is preferable to executing a database query

  The easiest to implement example of this is to use the Find() instead of Where() when retrieving an entity by primary key.

  var emp = db.Employees.Find(empID); // Queries in memory first
  var emp = db.Employees.Where(x => x.ID == empID); //Always hits the database

  var emp = db.Employees.Local.Where(x => x.ID == empID) ;

  使用原生和使用存储过程

  In EF 4.0 and above the DbContext the SQLQuery() method to execute raw SQL:

  var emps = db.Database.SQLQuery("select email from Employees");

  Similarly use ExecuteSqlCommand() to execute a stored procedure:

  var emps = db.Database.ExecuteSqlCommand("...");

  原文：http://www.sql-server-performance.com/2012/entity-framework-performance-optimization/