CODEWITHSUNDEEP
c#sqllinq
Rob
Rob

Reputation: 3574

List<int> : Linq get list per item

I'm trying to get this query in Linq to (Microsoft)SQL.

SELECT        MAX(ID) AS MyValue
FROM            Table
WHERE    "list contains 1 or 2 or 3"

How can I do this? The only examples I see around are only resulting in the max on the entire column and not per group (Example where TogetherId = 1)

I'd like to get a result like: Rows:

TogetherId | Id
1 | 1
1 | 2
1 | 16
2 | 7
3 | 8
3 | 9

Result:

TogetherId | Id
1 | 16
2 | 7
3 | 9

How can I do this with Linq?

List<int> myList; // Consider the list to be populated.

I'm already using this piece for the Where -> where myList.Contains(MyTable.ID)

I only want to select 1 column to a String-list in C#.

Upvotes: 0

Views: 134

Answers (5)

Sameer
Sameer

Reputation: 2171

List<int> myList = new List<int>();
myList.Add(1);
myList.Add(2);

List<String> myList1 = MyTable.Where(x=> myList.Contains(x.TogetherId)).GroupBy(x => x.TogetherId)
                            .Select(x => Convert.ToString(x.OrderByDescending(y => y.Id).First().Id))
                            .ToList();

Upvotes: 0

Grundy
Grundy

Reputation: 13381

and variant with query syntax

myList = (from x in yourList
         group x by x.TogetherId into g
         select new {
             TogetherId = g.Key, 
             Max = g.Max(a=>a.Id)
         }).ToList()

Upvotes: 0

Kamil Budziewski
Kamil Budziewski

Reputation: 23087

Something like this?

myList = yourList.GroupBy(x => x.TogetherId)
                 .Select(x => x.OrderByDescending(y => y.Id).First())
                 .ToList();

Upvotes: 2

Jamiec
Jamiec

Reputation: 136114

You are going to want something like a GroupBy followed by a Max:

 var newList = myList.GroupBy(x => x.TogetherId, x => x.Id)
            .Select(x => new {
                  TogetherId = x.Key,
                  MaxId = x.Max()
             })
            .ToList();

Live example: http://rextester.com/LROWSF91311

Upvotes: 2

usr
usr

Reputation: 171178

list
.GroupBy(x => x.TogetherId)
.Select(g => new { TogetherId = g.Key, Max = g.Max(x => x.Id) });

Upvotes: 0

Related Questions