Convert sql to LINQ query for selection of multiple max columns in c#

Question

SELECT MAX(sectionid) AS SectionId,MAX(displayorder) AS DisplayOrder,propertyid AS PropertyId,1 AS IsSpecSection FROM (
    SELECT mp.SectionId ,mp.DisplayOrder ,mp.PropertyId  FROM 
   ModelProperties mp 
    INNER JOIN PropertySections PS ON mp.SectionId = 
     ps.SectionId 
WHERE ps.IsSpecSection = 1  )s 
GROUP BY propertyid

I want to convert above query into LINQ, able to do it for selection of single max column but not for multiple.

you have working SQL code; why do you want to convert it to LINQ?; also - possible duplicate: Return multiple aggregate columns in LINQ — Marc Gravell
– Marc Gravell, Commented Nov 28, 2017 at 9:43
Please never just post SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (to entities?), and show your own first efforts so we can see where specifically you need help. — Gert Arnold
– Gert Arnold, Commented Nov 28, 2017 at 13:01

muthu · Accepted Answer · 2017-11-28 10:24:17Z

0

I haven't tested the code you have to modify the code as you need

using (var dbContext = new YourEntityName())
            {
                var result = (from mp in dbContext.ModelProperties
                              join ps in dbContext.PropertySections on mp.SectionId equals ps.SectionId
                              where ps.IsSpecSection = 1
                              group a by new { propertyid } into g
                              select sectionid , MAX(displayorder)AS DisplayOrder,propertyid AS PropertyId, 1 AS IsSpecSection).ToList();

}

Max value in Linq select Within Innerjoin

answered Nov 28, 2017 at 10:24

muthu

113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhilash Ravindran C K · Accepted Answer · 2017-11-28 13:37:42Z

You can use this code,

var list=(from mp in ModelProperties
          join ps in PropertySections on mp.SectionId equals ps.SectionId 
          where ps.IsSpecSection == 1
          group  new { mp, ps } by new { mp.PropertyId } into mgrp
          from grp in mgrp.DefaultIfEmpty()  
          select new
          { 
             grp.mp.SectionId, 
             grp.mp.PropertyId,
             grp.mp.DisplayOrder,
             grp.ps.IsSpecSection
          }).OrderByDescending(x=>x.SectionId).First();

This query helps you to retrieve ModelProperties rows that has matching SectionId in PropertySections and IsSpecSection has the value 1. Matching rows are then grouped by PropertyId. OrderByDescending sort the retrieved results in descending order of SectionId. First() retrieve the rows that has maximum SectionId for each PropertySections as the rows are sorted in descending order of SectionId.

Collectives™ on Stack Overflow

Convert sql to LINQ query for selection of multiple max columns in c#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related