1

I have next code, taking Last and Pre-Last params of elemensts in DB:

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

In fact the part :

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

converts to something like this in 1 request (show you only part for one block):

 SELECT TOP(1) [t0].[MyPV]
    FROM (
        SELECT [x0].[MyPV], [x0].[DataMonth]
        FROM [NpaDatas] AS [x0]
        WHERE [conts.NpaInfo].[ID] = [x0].[NpaInfoId]
        ORDER BY [x0].[DataMonth] DESC
        OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
    ) AS [t0]
    ORDER BY [t0].[DataMonth] DESC
) AS [PartnerPvPrev], (
    SELECT TOP(1) [t1].[GroupPV]
    FROM (
        SELECT [x1].[GroupPV], [x1].[DataMonth]
        FROM [NpaDatas] AS [x1]
        WHERE [conts.NpaInfo].[ID] = [x1].[NpaInfoId]
        ORDER BY [x1].[DataMonth] DESC
        OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
    ) AS [t1]
    ORDER BY [t1].[DataMonth] DESC
) AS [GroupPvPrev], (
    SELECT TOP(1) [t2].[PerformanceBonusLevelId]
    FROM (
        SELECT [x2].[PerformanceBonusLevelId], [x2].[DataMonth]
        FROM [NpaDatas] AS [x2]
        WHERE [conts.NpaInfo].[ID] = [x2].[NpaInfoId]
        ORDER BY [x2].[DataMonth] DESC
        OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
    ) AS [t2]
    ORDER BY [t2].[DataMonth] DESC
) AS [LevelPrev]

I dont want to repeat the same part multiple times:

conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Skip(1).Take(1)

So, if I do like this :

PrevBuffer = conts.NpaInfo.NpaData.OrderByDescending(x => x.DataMonth).Skip(1).Take(1).Select(                               
     param => new
     {

         param.MyPV,
         param.GroupPV,
         param.PerformanceBonusLevelId
     }
     ),

It will display many separate requests in output for each row like:

SELECT [x0].[MyPV], [x0].[GroupPV], [x0].[PerformanceBonusLevelId]
FROM [NpaDatas] AS [x0]
WHERE @_outer_ID1 = [x0].[NpaInfoId]
ORDER BY [x0].[DataMonth] DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

The same is about this part :

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

Will get multiple selects like:

SELECT [d.UserTag].[Emoji]
FROM [ContactTags] AS [d]
LEFT JOIN [UserTags] AS [d.UserTag] ON [d].[UserTagId] = [d.UserTag].[ID]
WHERE @_outer_ID = [d].[ContactId]

Can it be optimized somehow?

Improve this question
3
  • Instead of two queries... Skip(1).Take(1) and then later Take(1) you could try combining to a single query of Take(2) and then perform the logic client side (there may exist a Take(2) solution that's all Db side, I don't know)... That would cut your total queries in half at the very least Commented Aug 4, 2018 at 9:50
  • You really should show a minimal reproducible example. You've pasted code that appears to be part of a new anonymous object creation. All of the hard DB works happens outside of this and that would be what we need to see to optimise the query. Commented Aug 4, 2018 at 11:17
  • Updated the code for full version. Commented Aug 4, 2018 at 11:36

3 Answers 3

Reset to default
1

Doing something like this will improve the query significantly.

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();
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

But ToArray() will fetch all fields of NpaData, I need only 6 of them (3 of Last and 3 of Prev). Why do I need select here again, in your case, if I can just Index value like : result[0].MyPv
@user2377299 - Then select the three fields before the .ToArray(). And I kept the original code for simplicity.
@Enigmativity Can you explain a bit how it's better than the solution in question itself?
The .ToArray() forces the query execution so that there is only one query sent to the DB. The 6 following lines all then work from the data in memory.
1

You should select the repetitive parts first and use these in the subsequent query. This is much easier in query syntax, using the let keyword:

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
    };
Improve this answer

3 Comments

It does multiple selects too for each of that 6 values. Like : SELECT [x].[MyPV] FROM [NpaDatas] AS [x] WHERE [conts.NpaInfo].[ID] = [x].[NpaInfoId] ORDER BY [x].[DataMonth] DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
OK, apparently this is LINQ-to-SQL (please always use specific tags). In Entity Framework this would make a significant difference. I'm afraid your only option is not to collect all data in one query because LINQ-to-SQL will always do this inefficiently.
I've looked into this once more and really, this your only option --or your original query, but then at least my query is easier to read. With LINQ-to-SQL you'll always get a subquery per conts.NpaInfo.NpaData property you want to have in the result. I've looked into alternatives with getting collections of 2 conts.NpaInfo.NpaData items (Take(2)) and flatten the result later, but this always produces N+1 queries. If the current query performs reasonably, go for it, otherwise find another way to collect the data.
0

You should use a 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; }
    }
Improve this answer

10 Comments

Sorry, maybe there is some misunderstand. NpaInfo has multiple NpaData. And each NpaData has Level,PartnerPv,GrouPv. I need to fetch the Last and pre-Last items. NpaData does not contains both 6 fields.
I updated my code based on the SQL you posted but you SQL doesn't look right. I don't understand why you need to filter for ID. I suspect you should have a List<NpaInfo> and each should only have the same ID. Also the SQL posted has extra closing parenthesis which means you didn't post the entire SQL.
Contact has one NpaInfo. NpaInfo has many NpaData with different dates. I need 3 parms from each (Last and PreLast) NpaData of NpaInfo of Contacts created by current user. SQL - shorted only with problem part that duplicates.
So you argre with me that the ID of NpaInfo and the NapDatta will be the same so you do not need to filter for ID? Is there anything wrong with my Query?
NpaInfo.ID != NapData.ID - two different objects. Where do I filter by ID ?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.