將SQL查詢轉換為LINQ運算式

所以我正在開發一個簡單的收件箱聊天。我需要收集所有最新用戶的直接訊息。為此，我撰寫了一個按預期作業的 SQL 查詢，但 LINQ 版本沒有。有人知道如何將此 SQL 正確轉換為 LINQ 嗎？

這

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

應該導致這個

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

這是在 MSSQL 上運行的 SQL 查詢：

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), IIF(created_by_id > receiver_id, receiver_id, created_by_id)
order by created_at

林克：

(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();

uj5u.com熱心網友回復：

Id 應該是整數而不是字串。你有 '1' 這是一個字串。receiver_id 和 created_by id 怎么都為 1 并且 create_by 大于 receiver_id？嘗試以下：

    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; }
    }

uj5u.com熱心網友回復：

嘗試以下查詢。它應該回傳所需的結果。

小記：我看不出這種情況的原因IIF(created_by_id = receiver_id, created_by_id, receiver_id)，它總是receiver_id。無論如何添加到查詢中。

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;

標籤：

上一篇：Linq（或C#代碼）自連接表以填充空/空值

下一篇：有沒有辦法將Microsoft.Identity.Web用于外部登錄按鈕，而不依賴于Microsoft身份驗證？