c# - Grouping by when id can be in column 1 or 2 -

on website, members can send messages each other. here message class :

public partial class message {     public system.guid id { get; set; }     public system.datetime datemsg { get; set; }     public string senderid { get; set; }     public string receiverid { get; set; }     public string text { get; set; } } 

i'd query last message each opened discussion. since have no "discussion id", can't "group discussionid" , select first 1 in each group. how ?

public ienumerable<message> get_discussionsfor(string userid)         {             list<message> list;             using (var context = new modelcontainer())             {                 iqueryable<message> dbquery = context.set<message>();                                 list = dbquery                     .asnotracking()                     .where(m => m.senderid.equals(userid) || m.receiverid.equals(userid))                     .orderbydescending(m => m.datemsg)                                             .tolist<message>();             }             return list;         } 

well, might work, although won't fast.

here's first part of query:

            var list1 = dbquery                 .asnotracking()                 .where(m => m.senderid.equals(userid) || m.receiverid.equals(userid)); 

i going create query, creates anonymous type encapsulating message , id of other user.

            var senderfirst = list1                 .where(m => m.senderid.equals(userid))                 .select(                  m => {                    message = m,                    otheruser = m.receiverid                  }); 

i'm creating one, time ones user want receiver, , other user sender:

          var receiverfirst = list1               .where(m => m.receiverid.equals(userid))               .select(                 m => {                    message = m,                    otheruser = m.senderid,                 }); 

i concatenating them. can because have same properties same types , same names: behind scenes, they're same anonymous type.

            var list2 = senderfirst.concat(receiverfirst); 

as result, each message involving user want appear other user identified otheruser. note haven't changed message object @ all: it's still there, right values senderid , receiverid. wrapped in object, additional metadata.

you said message involving given 2 users in same discussion. so, can simulate grouping discussion grouping otheruser:

             var list3 = list2.groupby(m => m.otheruser); 

so have grouped messages. can sort each group , select final message.

             var list4 = list3                  .select(g => g.orderbydescending(m => m.message.datemsg).first()); 

and have list<message>, because message object still in there, ready sprung encapsulation:

             var list = list4                  .select(m => m.message)                  .tolist();