I have a message table in the following scheme:
Id | osID |Interessado | Remetente | Destinatario | Msg
--------------------------------------------------
1 | 2 |João | João | Maria |bla bla bla...
2 | 2 |João | Maria | João |ble ble ble...
3 | 3 |Erik | Erik | Mark |O Amadeu ganhou a promoção...
4 | 3 |Erik | Mark | Erik |Eu sabia...
5 | 3 |Erik | Mark | Erik |Vou pedir as contas...
6 | 4 |Jake | Jake | Mara |As oito da noite lá em casa...
The scenario I need is for every logged-in user to upload their conversations, so I have the following query:
var mensagens = from p in db.Chats
orderby p.Id descending
where p.Remetente == MinhaPessoa.Usuario || p.Destinatario == MinhaPessoa.Usuario
select p;
This query returns all records if I am the sender or recipient. Now, how do I group this by Service Order (osID) and Interested, and that the last message is displayed?