Translate SQL query into a LINQ expression-CodePudding

So I'm developing an easy inbox chat. I need to gather all latest user's direct messages. For this I wrote an SQL query which works as expected, but the LINQ version doesn't. Anybody have ideas of how do this SQL should be properly translated into LINQ?

This

CreatedAt Content ReceiverId CreatedById
1/1/2021  Hello!  2          1
1/1/2021  Hello!  3          1
1/2/2021  Hi!     1          2

Should result into this

 CreatedAt Content ReceiverId CreatedById
 1/1/2021  Hello!  3          1
 1/2/2021  Hi!     1          2

This is an SQL query ran on MSSQL:

select created_at = MAX(created_at) from messages
where
  receiver_id = '2921295d-f0c0-4162-ec8f-08d9d5c5b3f5' or 
  created_by_id = '2921295d-f0c0-4162-ec8f-08d9d5c5b3f5'
group by IIF(created_by_id = receiver_id, created_by_id, receiver_id)
order by created_at

LINQ:

(from message in Query.Where(x => x.ReceiverId == 1 || x.CreatedById == 1)
    group message by new { message.CreatedById, message.ReceiverId }
    into groupped
    select new InboxItemDto
    {
        CreatedAt = groupped.Max(z => z.CreatedAt),
        Content = groupped.FirstOrDefault().Content,
        Receiver = groupped.Key.CreatedById == 1
           ? groupped.FirstOrDefault().Receiver.FirstName
           : groupped.FirstOrDefault().CreatedBy.FirstName,
        Type = groupped.FirstOrDefault().Type

    }).OrderByDescending(x => x.CreatedAt)
    .ToList();

CodePudding user response：

Id's should be integers not strings. You have '1' which is a string. How can receiver_id and created_by id be both 1 and create_by be greater than receiver_id? Try following :

    class Program
    {

        static void Main(string[] args)
        {
            List<Message> messages = new List<Message>();

            var created_at = messages.Where(x => ((x.receiver_id == 1) || (x.created_by_id == 1)) && (x.created_by_id > x.receiver_id))
                .OrderBy(x => x.created_at)
                .ThenBy(x => x.desc)
                .GroupBy(x => new object[] { x.created_by_id, x.receiver_id })
                .ToList();            
        }
    }
    public class Message
    {
        public int receiver_id { get; set; }
        public int created_by_id { get; set; }
        public DateTime created_at {get;set;}
        public string desc { get; set; }
    }

CodePudding user response：

Try the following query. It should return desired result.

Small note: I do not see reason of this condition IIF(created_by_id = receiver_id, created_by_id, receiver_id), it is always receiver_id. Anyway added to the query.

var filteredWithKey =  
    from message in Query
    where message.ReceiverId == 1 || message.CreatedById == 1
    select new 
    {
        UserKey = message.ReceiverId == message.CreatedById ? message.CreatedById : message.ReceiverId,
        Message = message
    };

var query =
    from k in filteredWithKey.Select(k => new { k.UserKey }).Distinct()
    from lastMessage in filteredWithKey
        .Where(x => x.UserKey == k.UserKey)
        .Select(x => x.Message)
        .OrderByDescending(m => m.CreatedAt)
        .Take(1)
    select lastMessage;