Grouping by ID and displaying the top 1 with Linq

Question

Looking for a bit of advice on grouping with entity framework and linq.

So i have a table "tbl_ChatLog" that contains userID and sendToUserID etc...and with this data i'm trying to display the "Top 1" from each "SendToUserID"

So in my UI it would look like so:

1001 (Contains multiple but show the top 1)
1003 (Contains multiple but show the top 1)
1008 (Contains multiple but show the top 1)
1009 (Contains multiple but show the top 1)

The start of my code below:

public static List<Chat> getChatMessage() 
        {
            var entities = new FreeEntities();
            //My ID
            Business.User user = Business.User.getUserBySecurityToken();

             List<Chat> chatMessages = 
             (
                from cm in entities.tbl_ChatLog 
                where cm.UserID == user.uid 
                select new Chat 
                { 
                   uid = (int)cm.UserID, 
                   sendToUserID = (int)cm.SendToUserID, 
                   message = cm.Message, dateAdded = (DateTime)cm.DateAdded 
                }
             ).OrderByDescending(x => x.dateAdded).ToList();
            return chatMessages;
        }

Hoping you can help me out on this one. Grouping always seems to throw me.

Much appreciated,

Terry

possible duplicate of [How to get first record in each group using Linq](http://stackoverflow.com/questions/19012986/how-to-get-first-record-in-each-group-using-linq) — DavidG, Sep 25 '14 at 21:07
@DavidG although the link was very insightful, i believe it truly didn't give me any ideas on how to select into a new object(DTO) and to be honest i don't think its possible? iv'e separated my solution into projects. MSDN didn't really help to much either. I'm trying to keep my code as clean as possible. — Tez Wingfield, Sep 29 '14 at 19:32
*"Top 1" from each "SendToUserID"*, but grouped within what? UserId? — Gert Arnold, Oct 05 '14 at 07:16
@GertArnold Yes group by UserId. But at that at point, i then want to select into an object (Chat). — Tez Wingfield, Oct 05 '14 at 16:48
After looking at all answers, i found it particularly difficult to select into an object. Because my project consists of multiple layers i wanted to abstract away from EF(EDMX). I managed to get this working by using my own work around. — Tez Wingfield, Oct 05 '14 at 16:54

Raein Hashemi · Accepted Answer · 2014-10-05T17:18:05.853

0

You could use groupby:

List<Chat> chatMessages =

(from cm in entities.tbl_ChatLog 

where cm.UserID == user.uid 

orderby cm.DateAdded descending

group cm by cm.sendToUserID into grp

select new Chat
{ 

uid = grp.FirstOrDefault().UserID,

sendToUserID = grp.Key, 

message = grp.FirstOrDefault().Message,

dateAdded = grp.FirstOrDefault().DateAdded

}).ToList()

This will get you a list of your table data grouped by sendToUserID and the first entry of each group containing every property including sendToUserID.

edited Oct 05 '14 at 17:18

answered Oct 04 '14 at 10:24

Raein Hashemi

3,346
4
22
33

this logic seems well defined but i need to select into an object (Chat) I'm not sure this is possible. I'm going to add a comment to my question with what i have so far please review. – Tez Wingfield Oct 05 '14 at 16:50
you can build your List on select new. I am editing my answer, please check. – Raein Hashemi Oct 05 '14 at 17:13
@Rainmain I will try this out and let you know. – Tez Wingfield Oct 06 '14 at 17:14

score 0 · Answer 2 · answered Oct 05 '14 at 17:04

The original problem was trying to select the first message from each unique "SendToUserId" and grouping by UserId and then selecting into a DTO was a nightmare but i managed to get a simple work around. Code below:

getFirstChatMessage() 
    {
        var entities = new FreeEntities();
        //User ID
        Business.User user = Business.User.getUserBySecurityToken();

        // Create new list of Chat
        List<Chat> chatList = new List<Chat>();

          var res = from c in entities.tbl_ChatLog
          where c.UserID == user.uid
          group c by c.UserID
          into groups
          select groups.OrderByDescending(p => p.DateAdded).FirstOrDefault();

        foreach (var r in res) 
        {
          chatList.Add(new Chat() 
          {
           uid = (int)r.UserID, 
           sendToUserID = (int)r.SendToUserID,
           message = (from m in entities.tbl_ChatLog where m.UserID == (int)r.SendToUserID 
           orderby r.DateAdded descending select m.Message).FirstOrDefault(),
           dateAdded = (DateTime)r.DateAdded, 
           fullName = (from b in entities.tbl_Bio where b.UserID == (int)r.SendToUserID 
           select b.FirstName + " " +      b.SurName).FirstOrDefault() 
          });
        }
        return chatList;
    }

Grouping by ID and displaying the top 1 with Linq

2 Answers2