How to select last record in a LINQ GroupBy clause

linq get latest record by date
linq group by
linq group by select
entity framework get most recent record
linq query with group by and where clause
linq group by multiple columns
linq group by order by
linq average group by

I have the following simple table with ID, ContactId and Comment.

I want to select records and GroupBy contactId. I used this LINQ extension method statement:

Mains.GroupBy(l => l.ContactID)
 .Select(g => g.FirstOrDefault())
 .ToList()

It returns record 1 and 4. How can I use LINQ to get the ContactID with the highest ID? (i.e. return 3 and 6)

You can order you items

Mains.GroupBy(l => l.ContactID)
.Select(g=>g.OrderByDescending(c=>c.ID).FirstOrDefault()) 
.ToList()

Get the last record of group using LINQ, Depending on what you want you can change OrderBy or even can use First or Last in this. list .OrderByDescending(a => a.ValueA) . LINQ: How to get the latest/last record with a group by clause LINQ: How to get the latest/last record with a group by clause Sometimes, we need to select a record that has the maximum or minimum value from a group.

Use OrderByDescending on the items in the group:

Mains.GroupBy(l => l.ContactID)
    .Select(g => g.OrderByDescending(l => l.ID).First())
    .ToList();

Also, there is no need for FirstOrDefault when selecting an item from the group; a group will always have at least one item so you can use First() safely.

LINQ: How to get the latest/last record with a group by clause , Sometimes, we need to select a record that has the maximum or minimum value from a group. Some examples are: We have a PersonOrders  Group by in linq with sum In above example if you notice, we have used sum function to get the sum of score, here is the piece of code again var groupScores = from techStream in studentsStream group techStream by techStream.StreamId into studentGroup select new { Stream = studentGroup.Key, GroupScore = studentGroup.Sum(x => x.Score), };

Perhaps selecting with Max instead of OrderByDescending could result into improving of performance (I'm not sure how it's made inside so it needs to be tested):

var grouped = Mains.GroupBy(l => l.ContactID);
var ids = grouped.Select(g => g.Max(x => x.Id));
var result = grouped.Where(g => ids.Contains(g.Id));

As I assume it could result into a query that will take MAX and then do SELECT * FROM ... WHERE id IN ({max ids here}) which could be significantly faster than OrderByDescending.

Feel free to correct me if I'm not right.

[SOLVED], var query = from p in db.Payments where p.Status == false group p by p.​CompanyID into op select new { CompanyID = op.Key  @amin, not really, for First, it gets translated into SELECT Top (1) So you don't really have to iterate the query. The above query in answer is getting translated into something like SELECT TOP 1 Gram from tblGold Order by Gram DESC;. With Last/LastOrDefault the expression can't translate into under laying data source language (SQL).

OrderByDescending

Mains.GroupBy(l => l.ContactID)
.Select(g=>g.OrderByDescending(c=>c.ID).FirstOrDefault()) 
.ToList()

is your best solution

It orders by the highest ID descending, which is pretty obvious from the name.

How to Get the First or Last Value in a Group Using Group By in SQL , How to Get the First or Last Value in a Group Using Group By in SQL 7 | 1 | withdraw | 2019-08-03 16:15:09+03 | {"delta_balance": -50} (7 rows) ERROR: column "event.data" must appear in the GROUP BY clause or be  You should sort your records based on the ID instead of ScheduleId and also filter the records that has the empty Filename: objContext.SchedulesAndFiles .Where(x => x.ScheduleId == scheduleId && x.Filename != "") .OrderByDescending(x => x.ID) .First().Filename;

You could use MoreLinq like this for a shorter solution:

Main.OrderByDescending(i => i.Id).DistinctBy(l => l.ContactID).ToList();

[Solved] How to select last record of group of records in SQL server , Column 'tbl_child.sh_ghab' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. In this case the key is a string, the student's last name. It is also possible to use a substring for the key. The grouping operation uses the default equality comparer for the type. Paste the following method into the StudentClass class. Change the calling statement in the Main method to sc.GroupBySingleProperty().

Linq and SQL - group max date - show latest entry, tblFactoryID,f.tblFactoryUnitCount from tblfactory as f join. (. select [​tblFactoryName], max(tblFactoryExportDate)as tblFactoryExportDate from tblFactory. group by  [SOLVED] - Linq: Select Most Recent Record of Each Group. Status can be assigned false because it is already filtered by Where clause in the early clauses.

How to select last record in a LINQ GroupBy clause, GroupBy(l => l.ContactID) .Select(g => g.FirstOrDefault()) .ToList() It returns record 1 and 4. How can I use LINQ to get the ContactID with the highest ID? LINQ Last() Method. In LINQ, Last() method is used to return last element from the list / collection and this LINQ Last() method will throw an exception in case if the list / collection returns no elements. Following is the syntax of using LINQ Last() method to get last element from the list using LINQ Last() method.

MySQL MAX() function with group by, SELECT cate_id, MAX( book_price) FROM book_mast GROUP BY cate_id; '​GROUP BY ' clause have grouped “cate_id's”. <div class="container"> <div class="row"> <div class="col-md-12"> <h2>Extracting all cate_id's  The group clause causes a separate group to be created for each set of persons with the same last name and the same city. group person by new {name = person.surname, city = person.city}; Use a named type if you must pass the query variable to another method.

Comments
  • Despite this query will return correct data, it results in compilation into K+1 SQL statements, where K is number of keys (use LINQpad to see it). I faced same problem today and solved it this way: ` var lastCommentIDs = Mains.GroupBy(k => k.ContactID, e => e.ID, (k, e) => new { k, lastID = e.Max(x => x)}); var result = lastCommentIDs.Join(Mains, o => o.lastID, i => i.ID, (o, i) => new { ContactID = o.k, LastComment = i.Comment).ToList(); `
  • Hi, @Dmitrij. Thanks for feedback. Linqpad is using Linq to SQL, did you tried Linq to entities with this?
  • Hi, @tym32167. yes, I had an attempt to jump into EF, not sure wha exactly was wrong, but EF produced ugliest and absolutely inefficient SQL queries out of LINQ statements. Probably I did something wrong, not sure but the fact is: LINQ2SQL produces more predictable queries than EF. (at least I understand what will happen when I'm executing paticular query using LINQ2SQL)
  • fater than OrderBy