加快LINQ /实体结果的回报 [英] Speed up return of linq/entity result
问题描述
Unfortunaltly我坚持认为没有采取考虑到会发生什么,一旦系统有千万条记录的系统。
我遇到的问题是code目前正在写主要的GetList方法实际上从数据库返回的每个记录和过滤是在getfull方法所做的那样。
我tryed但是通过传递调整的id到的GetList方法改变这个当我尝试做。凡(A => a.Id == journalid)
我得到这个错误
无法隐式转换类型'System.Linq.IQueryable为'System.Data.Objects.ObjectQuery
公共调整GetFull(INT ID){
VAR的结果=的GetList(ID).FirstOrDefault(JA => ja.Id == ID);
如果(结果== NULL){
抛出新的InvalidOperationException异常(
的String.Format(ID为调整= {0}没有找到。ID)
);
}
返回LoadFullActivities(结果);
}
私人IQueryable的<调节>的GetList(INT journalid){
返回的GetList(journalid,原始,活动,
Original.Employee,Original.Employee.EmployeeType,
Original.Employee.WorkType,Original.Employee.Department,
Original.Activities,Original.Status,
Original.Approver,状态);
} 私人IQueryable的<调节>的GetList(INT journalid,则params字符串[]包括){
透射电镜变种presult = dataContext.AdjustmentSet;
的foreach(VAR财产包括){
透射电镜presult = TEM presult.Include(属性)。凡(A => a.Id == journalid);
}
透射电镜返回presult;
}
这会修正这个错误。
私人的IQueryable<调节>的GetList(INT journalid,则params字符串[]包括){
透射电镜变种presult = dataContext.AdjustmentSet.AsQueryable();
的foreach(VAR财产包括){
透射电镜presult =((&的ObjectQuery lt;调节>)TEM presult).INCLUDE(财产);
}
透射电镜返回presult.Where(A => a.Id == journalid);
}
那些含减慢查询。由于查询是会得到只有一行(和做其他的连接因为包括)我不认为性能是如此之慢。为了提高业绩指标增加为每个外键。我想a.Id早已聚集索引。
Unfortunaltly I am maintaining a system that didnt take consideration into what will happen once the system has millions of records.
The issue I am having is the way the code is currently written mainly the GetList method actually returns every record from the database and the filtering is done at the getfull method.
I tryed changing this by passing the id of the adjustment to the getlist method however when i try to do .Where(a=>a.Id == journalid) i get this error
Cannot implicitly convert type 'System.Linq.IQueryable to 'System.Data.Objects.ObjectQuery
public Adjustment GetFull(int id) {
var result = GetList(id).FirstOrDefault(ja => ja.Id == id);
if(result == null) {
throw new InvalidOperationException(
String.Format(" Adjustment with Id={0} not found.", id)
);
}
return LoadFullActivities(result);
}
private IQueryable<Adjustment> GetList(int journalid) {
return GetList(journalid,"Original", "Activities",
"Original.Employee", "Original.Employee.EmployeeType",
"Original.Employee.WorkType", "Original.Employee.Department",
"Original.Activities", "Original.Status",
"Original.Approver", "Status");
}
private IQueryable<Adjustment> GetList(int journalid, params string[] includes) {
var tempResult = dataContext.AdjustmentSet;
foreach (var property in includes) {
tempResult = tempResult.Include(property).Where(a=>a.Id == journalid) ;
}
return tempResult;
}
This would fix the error
private IQueryable<Adjustment> GetList(int journalid, params string[] includes) {
var tempResult = dataContext.AdjustmentSet.AsQueryable();
foreach (var property in includes) {
tempResult = ((ObjectQuery<Adjustment>)tempResult).Include(property);
}
return tempResult.Where(a=>a.Id == journalid);
}
Those Includes slow down the query. Since the query is going to get only one row (and do other joins because of the includes) I don't think the performance is so slow. To increase the performance add indexes for each foreign key. I guess a.Id has already a clustered index.
这篇关于加快LINQ /实体结果的回报的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!