LINQ to Entities 使用之 ToList() 导致内存溢出问题
/// <summary> /// 根据关键字Id获取关键字对象 /// </summary> /// <param name="nick">nick</param> /// <param name="campaignId">campaignId</param> /// <param name="adgroupId">adgroupId</param> /// <param name="keywordId">keywordId</param> /// <param name="day">day</param> /// <returns>record</returns> public DCKeyword GetKeywordByKeywordId(string nick, long campaignId, long adgroupId, long keywordId, DateTime day) { //if (campaignId == 0 || adgroupId == 0 || keywordId == 0) return null; //var query = from m in _context.DCKeywords.ToList() //ToList() 应该慎用,经过测试当表数据量达到 300万
(测试时表数据刚好有300万)时,会吃光服务器内存,导致内存溢出异常. // where m.nick.Trim() == nick.Trim() && m.campaign_id == campaignId // && m.adgroup_id == adgroupId && m.keyword_id == keywordId // && m.data_time.Value.ToShortDateString().Equals(day.ToShortDateString()) // select m;
//解决方法一: 先缩小数据范围,再ToList(),再用 lambda 表达式筛选. if (campaignId == 0 || adgroupId == 0 || keywordId == 0) return null; var query = (from m in _context.DCKeywords where m.nick.Trim() == nick.Trim() && m.campaign_id == campaignId && m.adgroup_id == adgroupId && m.keyword_id == keywordId select m).ToList() .Where(q => q.data_time.Value.ToShortDateString().Equals(day.ToShortDateString())); //解决方法二:使用 Expression 表达式对象构造查询条件 //Expression<Func<DCKeyword, bool>> where = q => q.data_time.Value.ToShortDateString().Equals(day.ToShortDateString()); return query.SingleOrDefault(); }