如何优化 LINQ DB 请求？

Question

我有下一个代码，采用 DB 中元素的 Last 和 Pre-Last 参数：

var result =  _context.Contacts.Where(conts => conts.CreatorUserId == _userManager.GetUserId(this.User))

     .Select(conts => new

     {

         conts.ID,

         conts.Mobile,

         conts.Email,

         conts.Facebook,

         conts.StateId,

         conts.CreatorUserId,

         conts.Birthday,

         conts.Description,

         conts.Name,

         conts.Photo,

         conts.SecondName,

         Tags = conts.Tags.Select(d=> d.UserTag.Emoji),

         NpaId = conts.NpaInfo.NpaId,

         PartnerPvPrev = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Skip(1).Take(1).Select(x => x.MyPV).FirstOrDefault(),

         GroupPvPrev = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Skip(1).Take(1).Select(x => x.GroupPV).FirstOrDefault(),

         LevelPrev = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Skip(1).Take(1).Select(x => x.PerformanceBonusLevelId).FirstOrDefault(),

         PartnerPv = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Take(1).Select(x => x.MyPV).FirstOrDefault(),

         Level = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Take(1).Select(x => x.PerformanceBonusLevelId).FirstOrDefault(),// conts.NpaInfo.PerformanceBonusLevelId,

         GroupPv = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Take(1).Select(x => x.GroupPV).FirstOrDefault(),

         EntryDate = conts.NpaInfo.EntryDate,

         ExpirationDate = conts.NpaInfo.ExpirationDate

     });

事实上部分：

PartnerPvPrev = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Skip(1).Take(1).Select(x => x.MyPV).FirstOrDefault(),

GroupPvPrev = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Skip(1).Take(1).Select(x => x.GroupPV).FirstOrDefault(),

LevelPrev = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Skip(1).Take(1).Select(x => x.PerformanceBonusLevelId).FirstOrDefault()

Answer 1

您应该首先选择重复部分并在后续查询中使用这些部分。这在查询语法中要容易得多，使用let关键字：var result = from conts in _context.Contacts    where conts.CreatorUserId == _userManager.GetUserId(this.User)    let monthsSorted = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth)    let firstMonth = monthsSorted.FirstOrDefault()    let prevMonth = monthsSorted.Skip(1).FirstOrDefault()    select new    {        conts.ID,        conts.Mobile,        conts.Email,        conts.Facebook,        conts.StateId,        conts.CreatorUserId,        conts.Birthday,        conts.Description,        conts.Name,        conts.Photo,        conts.SecondName,        Tags = conts.Tags.Select(d=> d.UserTag.Emoji),        NpaId = conts.NpaInfo.NpaId,        PartnerPvPrev = prevMonth.MyPV,        GroupPvPrev = prevMonth.GroupPV,        LevelPrev = prevMonth.PerformanceBonusLevelId,        PartnerPv = firstMonth.MyPV,        GroupPv = firstMonth.GroupPV,        Level = firstMonth.PerformanceBonusLevelId,        EntryDate = conts.NpaInfo.EntryDate,        ExpirationDate = conts.NpaInfo.ExpirationDate    };

Answer 2

做这样的事情将显着改善查询。var result = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Take(2).ToArray();PartnerPvPrev = result.Skip(1).Take(1).Select(x => x.MyPV).FirstOrDefault();  GroupPvPrev = result.Skip(1).Take(1).Select(x => x.GroupPV).FirstOrDefault();    LevelPrev = result.Skip(1).Take(1).Select(x => x.PerformanceBonusLevelId).FirstOrDefault();    PartnerPv = result.Take(1).Select(x => x.MyPV).FirstOrDefault();        Level = result.Take(1).Select(x => x.PerformanceBonusLevelId).FirstOrDefault();      GroupPv = result.Take(1).Select(x => x.GroupPV).FirstOrDefault();

Answer 3

您应该使用 GroupBy ：&nbsp; &nbsp; class Program&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; static void Main(string[] args)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Conts conts = new Conts();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<NpaDatas> data = conts.NpaInfo.NpaDatas&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .OrderByDescending(x => x.DataMonth)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .GroupBy(x => x.DataMonth).Select(x => x.FirstOrDefault()).ToList();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; //To get the second latest use followng&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NPaDatas results = data.Skip(1).FirstOrDefault();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; public class NpaDatas&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string NpaInfoId { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public DateTime DataMonth { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public PV PartnerPv { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public PV PerformanceBonusLevelId { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public PV GroupPv { get; set; }&nbsp; &nbsp; }&nbsp; &nbsp; public class PV&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; //data not specified&nbsp;&nbsp; &nbsp; }&nbsp; &nbsp; public class Conts&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public NpaInfo NpaInfo { get; set; }&nbsp; &nbsp; }&nbsp; &nbsp; public class NpaInfo&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; public string ID { get; set; }&nbsp; &nbsp; &nbsp; &nbsp; public List<NpaDatas> NpaDatas { get; set; }&nbsp; &nbsp; }