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();
        }

 

转载于:https://www.cnblogs.com/xust/articles/3069257.html