Select latest two row of chat history linq

Question

I have a ChatMessage table and i want to select user chats history as a list of latest message he/she has with others or himself like whast app.

when you open whats app you will see a list of your chats history that show the latest chat.

table schema is like this:

SenderId    ReciverId    Message
--------------------------------
1           2            hello
1           2            how are you?
2           1            hey
2           1            i'm fine
2           2            for myself
2           3            are you there?

i tried with this query:

SELECT MAX(SenderID), ReciverID, Message
FROM ChatMessages
WHERE SenderID = 2 OR ReciverID = 2
GROUP BY SenderID, ReciverID order by SenderID

the result for this query is :

    senderId    ReciverId    Message
    --------------------------------
    1           2            how are you?
    2           1            i'm fine
    2           2            for myself
    2           3            are you there?

row number 1 and 2 must be merge and just show: 2 1 i'm fine

and the result i'm looking for is:

    senderId    ReciverId    Message
    --------------------------------
    2           1            i'm fine
    2           2            for myself
    2           3            are you there?

what is the solution? and if possible in in linq or lambda?

There is no linq code in your sample, only sql. Just wondering, how are going to get the latest chat without message datetime? — Pavel Anikhouski, May 04 '20 at 19:23
i know this is sql, and i asked for linq of this query.actually in real i have datetime field but as you can see i used GroupBy Max so just one Max id will return.the problem is resualt have duplicate row of latest message (row1 and 2 in this example). — Mohammad Reza Mrg, May 04 '20 at 19:27
@MohammadRezaMrg the result displayed above is what you want? — zafar, May 04 '20 at 19:35
It's not clear what you want. Also you should show the relevant fields in the question such as datetime of message — Lotok, May 04 '20 at 19:36
@MohammadRezaMrg to be able to see the chat in the latest order you need a column which has timestamp info so that it may be ordered descending — zafar, May 04 '20 at 19:40
What is the logic behind the rows "merging"? I dont think they actually merged based on your edit, you just removed the first one. Is this something that will happen based on data or do you just want to skip a row? Also your result doesnt look correct. `Max(senderId)` wouldnt return 1 and 2, just 2 and WHERE with group by should be HAVING — Lotok, May 04 '20 at 19:40
Almost exact duplicate: https://stackoverflow.com/q/19160255/861716 — Gert Arnold, May 04 '20 at 19:41
@MohammadRezaMrg for your output you don't need anything fancy. For your output a simple linq expression like `context.ChatMessages.Where(c=>c.senderId == 2).OrderByDescending(c=>c.Timestamp).Take(3).ToList().Reverse()` should be enough — zafar, May 04 '20 at 19:46

Mohammad Reza Mrg · Accepted Answer · 2020-05-07T13:32:55.477

i got this by using groupBy and fetch 2 latest message of each partner and then use distinctby(from morelinq) to remove dublicate.

string uid =  "a90566ab-eef7-4f57-8e96-a3b7cc8ce786";               

var chatIdsQuery = _context.ChatMessages
    .Where(o => o.SenderID.Equals(uid) || o.ReciverID.Equals(uid))                    
    .Select(x => new
    {
         x.Id,
        x.ReciverID,
        x.SenderID
    })                     
    .GroupBy(g => new { g.SenderID, g.ReciverID } )
    .Select(k => new {
        Id = k.Max(x => x.Id),
        PartnerId = k.Key.SenderID.Equals(uid) ? k.Key.ReciverID : k.Key.SenderID,
        //CreateTime = k.Max(x => x.CreateTime)
    })
    .OrderByDescending(d => d.Id)                  
    .AsQueryable(),
    .DistinctBy(i => i.PartnerId);  // <============= just remove dublicate

var chatListQuery = from l in chatIdsQuery
               join x in _context.ChatMessages on l.Id equals x.Id
               orderby x.CreateTime descending
               select (new
               {
                   x.Id,
                   l.PartnerId,
                   x.Text,
                   x.CreateTime
               });
var chatList = chatListQuery.ToList();

and for async:

var chatList = await chatListQuery.ToListAsync();
chatList = chatList.DistinctBy(i => i.PartnerId).ToList();

or:

chatList = chatList.GroupBy(x => x.PartnerId).Select(x => x.First()).ToList();

Select latest two row of chat history linq

1 Answers1