如何优化 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 ：

    class Program

    {

        static void Main(string[] args)

        {

            Conts conts = new Conts();

            List<NpaDatas> data = conts.NpaInfo.NpaDatas

                .OrderByDescending(x => x.DataMonth)

                .GroupBy(x => x.DataMonth).Select(x => x.FirstOrDefault()).ToList();

            //To get the second latest use followng

            NPaDatas results = data.Skip(1).FirstOrDefault();

        }

    }

    public class NpaDatas

    {

        public string NpaInfoId { get; set; }

        public DateTime DataMonth { get; set; }

        public PV PartnerPv { get; set; }

        public PV PerformanceBonusLevelId { get; set; }

        public PV GroupPv { get; set; }

    }

    public class PV

    {

        //data not specified 

    }

    public class Conts

    {

        public NpaInfo NpaInfo { get; set; }

    }

    public class NpaInfo

    {

        public string ID { get; set; }

        public List<NpaDatas> NpaDatas { get; set; }

    }