加快LINQ /实体结果的回报 [英] Speed up return of linq/entity result

问题描述

Unfortunaltly我坚持认为没有采取考虑到会发生什么，一旦系统有千万条记录的系统。

我遇到的问题是code目前正在写主要的GetList方法实际上从数据库返回的每个记录和过滤是在getfull方法所做的那样。

我trye​​d但是通过传递调整的id到的GetList方法改变这个当我尝试做。凡（A => a.Id == journalid）
我得到这个错误

无法隐式转换类型'System.Linq.IQueryable为'System.Data.Objects.ObjectQuery

 公共调整GetFull（INT ID）{
            VAR的结果=的GetList（ID）.FirstOrDefault（JA =＆GT; ja.Id == ID）;
            如果（结果== NULL）{
                抛出新的InvalidOperationException异常（
                    的String.Format（ID为调整= {0}没有找到。ID）
                ）;
            }
            返回LoadFullActivities（结果）;
        }
        私人IQueryable的＆lt;调节＆GT;的GetList（INT journalid）{
            返回的GetList（journalid，原始，活动，
                Original.Employee，Original.Employee.EmployeeType，
                Original.Employee.WorkType，Original.Employee.Department，
                Original.Activities，Original.Status，
                Original.Approver，状态）;
        }        私人IQueryable的＆lt;调节＆GT;的GetList（INT journalid，则params字符串[]包括）{
            透射电镜变种presult = dataContext.AdjustmentSet;
            的foreach（VAR财产包括）{
                透射电镜presult = TEM presult.Include（属性）。凡（A =＆GT; a.Id == journalid）;
            }
            透射电镜返回presult;
        }
 

解决方案

这会修正这个错误。

 私人的IQueryable＆lt;调节＆GT;的GetList（INT journalid，则params字符串[]包括）{
         透射电镜变种presult = dataContext.AdjustmentSet.AsQueryable（）;
         的foreach（VAR财产包括）{
              透射电镜presult =（（＆的ObjectQuery lt;调节＆GT;）TEM presult）.INCLUDE（财产）;
         }
         透射电镜返回presult.Where（A =＆GT; 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屋！