-4

My table Chats

id | sender   | receiver     | message      | date
1  |        1 |            2 | Hello        | 2015-12-08 20:00
2  |        2 |            1 | Hey          | 2015-12-08 20:10
3  |        2 |            1 | You there?   | 2015-12-08 21:00
4  |        1 |            3 | Yes          | 2015-12-08 21:15
5  |        4 |            1 | Hey buddy    | 2015-12-08 22:00

My controller

...
[HttpPost]
public async Task<JsonResult> ChatList(int person_id)
{
IEnumerable<Chat> chats = db.Chats.Where(p=>(p.sender==person_id||p.receiver==person_id));
return Json(chats);
}

How do I get the latest message from each conversation?

Zhasulan Alen
  • 1
  • 3
  • What results are you expecting from the sample data, and why? – mjwills Aug 06 '18 at 12:34
  • Possible duplicate of [LINQ - Select Records with Max Property Value per Group](https://stackoverflow.com/questions/43804896/linq-select-records-with-max-property-value-per-group) – DavidG Aug 06 '18 at 12:37
  • Possible duplicate of [Select values with max date for each ID](https://stackoverflow.com/questions/45939168/select-values-with-max-date-for-each-id) – Drag and Drop Aug 06 '18 at 12:46
  • Your real problem is that you don't have a 'conversation identifier' here, so all you know are the sender and receiver IDs define a conversation. You need to group by something but you don't have a consistent thing to group on. – DavidG Aug 06 '18 at 12:53

1 Answers1

1
var result = db.Chats.GroupBy(c => (Math.Min(c.id1, c.id2), Math.Max(c.id1, c.id2)))
    .Where(g => g.Key.Item1 == person_id || g.Key.Item2 == person_id)
    .Select(g => g.OrderBy(c => c.date))
    .Select(g => g.Last().message);

For efficiency you can replace OrderBy with an Aggregate that would fetch the latest element in O(n), but this illustrates the concept I think.

V0ldek
  • 9,623
  • 1
  • 26
  • 57
  • Which one of these wouldn't? I'm unsure of `Math.Min` and `Math.Max`, but you could rewrite that into an equivalent expression using only `? :` operators if needed. – V0ldek Aug 06 '18 at 13:01
  • That's the bit I mean, it might do, I've never had to do a group by using something like this. – DavidG Aug 06 '18 at 14:23
  • Me neither, that's why I'm unsure. Anyway `(c.id1 < c.id2 ? c.id1 : c.id2, c.id1 < c.id2 ? c.id2 : c.id1)` will surely work, but for an answer `Min` and `Max` are much more readable :) – V0ldek Aug 06 '18 at 14:29
  • As we are nitpicking, `.SelectMany(g => g.OrderByDescending(d => d.Date).Take(1))` should be more efficient – Drag and Drop Aug 06 '18 at 14:35