CODEWITHSUNDEEP
c#linq
Manjit Singh
Manjit Singh

Reputation: 255

How to get only the max string length column row from a table using linq?

I have a table like the following image with two column "cas" and "pref" and you can see there are common values in "cas" column so i want to group by "cas" and fetch only the rows those have max string length in the "pref" column using the Linq in c#.

Input Table:

enter image description here

Required Output:

enter image description here

I am using following query to get the upper required output in sql but how to do it by LINQ?

select cas, (select top 1 pref from pdf_tab b where b.cas = a.cas order by LEN(pref) desc )
       from pdf_tab a group by cas

Upvotes: 1

Views: 2006

Answers (5)

ipavlu
ipavlu

Reputation: 1649

I presumne that the data structure is like this:

class Data
{
   public int cas {get;set;
   public string pref {get;set;}
}  

and some record:

List<Data> records;

because you said C# and LINQ, it has to be that way:).

From there it is simple:

List<Data> newRecords =
records
.GrouBy(data => data.cas)
.Select(gData => gData
                 .OrderBy(x => x?.pref?.Length ?? 0)
                 .LastOrDefault())
.Where(data => data != null)
.ToList()
 ;

Upvotes: 1

Raluca Pandaru
Raluca Pandaru

Reputation: 345

You can order all your data from the table descending by the length of the Pref property, group them by Cas value and then get only the first element from each group.

var result = from p in table
             orderby p.Pref.Length descending
             group p by p.Cas into g
             select g.First();

Upvotes: 0

Ashley John
Ashley John

Reputation: 2453

var results = from p in tablename
          group p by p.cas into g
          order by g.Key
          select new { cas = g.Key, maximum = 
g.First(r => r.pref.Length == g.Max(y => y.pref.Length)) };

Upvotes: 0

Marcus
Marcus

Reputation: 8659

This should work:

var results = yourTable.GroupBy(x => x.cas).Select(g => g.OrderByDescending(og => og.pref.Length).First());

Upvotes: 1

public_static_void
public_static_void

Reputation: 66

This will return longest element from each group:

      var results = from p in tablename
      group p by p.cas into g
      select g.OrderByDescending(e=>e.pref.Length).First()

Upvotes: 1

Related Questions