消息传递功能创建 Sql 查询和数据库视图

鉴于以下情况：

我需要能够获取给定用户参与的所有线程的列表，首先按最新消息排序，仅显示最新消息（每个线程 1 条消息）

这是上面的 SQL 查询：

SELECT
    Message.MessageId,
    Message.CreateDate,
    Message.Body,
    Login.Username,
    (SELECT MessageReadState.ReadDate
        FROM MessageReadState
        WHERE MessageReadState.MessageId = Message.MessageId
            AND MessageReadState.LoginId = 2) AS ReadState
FROM Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId
WHERE Message.MessageId IN (
    SELECT Max(Message.MessageId)
    FROM MessageThreadParticipant
    INNER JOIN Message
        ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId
    WHERE MessageThreadParticipant.LoginId=2
    GROUP BY MessageThreadParticipant.MessageThreadId
)
ORDER BY Message.CreateDate DESC;

其工作原理是这样的。 Loginid 1 向 LoginId 2 发送消息：

1. 创建一个新线程并向 MessageThread 中插入一条记录

2. 消息表中添加了一条新记录，其中包含上面的treadId

3. 两条记录被插入到 MessageThreadParticipant 中（发送者 LoginId 和接收者 LogiId）

   当用户打开他/她的消息列表时，MessageReadState 将更新消息的 ReadDate。

问题： 我想创建一个可以简单地过滤 LoginId 的视图（我将使用 LINQ）。但是，我无法使用上面的查询执行此操作（因为我需要在 sql 语句中的某个位置传递 loginId）。有没有办法修改上面的 SQL 查询，让我可以查看？它是什么？

编辑： 我想我有它，但我不确定这是否是最好/最有效的解决方案：

SELECT
    Message.MessageId,
    Message.MessageThreadId,
    Message.SenderLoginId,
    Mtp.LoginId,
    Login.Username,
    Message.CreateDate,
    Message.Body,
    (SELECT MessageReadState.ReadDate
        FROM MessageReadState
        WHERE MessageReadState.MessageId = Message.MessageId
            AND MessageReadState.LoginId = Mtp.LoginID) AS ReadState
FROM Message 
    INNER JOIN Login ON Message.SenderLoginId = Login.LoginId
    INNER JOIN MessageThreadParticipant Mtp ON Mtp.MessageThreadId = Message.MessageThreadId
WHERE Message.MessageId IN (
    SELECT Max(Message.MessageId)
    FROM MessageThreadParticipant
    INNER JOIN Message
        ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId
    WHERE MessageThreadParticipant.LoginId=Login.LoginID
    GROUP BY MessageThreadParticipant.MessageThreadId
)
ORDER BY Message.CreateDate DESC;

这会做你想要的事情吗？

SELECT
  Login.LoginID,
  Login.Username,
  Message.MessageThreadID,
  Message.MessageId,
  Message.CreateDate,
  Message.Body,
  Sender.LoginID                     AS SenderLoginID,
  Sender.Username                    AS SenderUsername,
  MessageReadState.ReadDate          AS RecipientReadDate
FROM
  Login
INNER JOIN
  MessageThreadParticipant
    ON MessageThreadParticipant.LoginId = Login.LoginID
-- This gives all threads every LoginID has ever participated in

CROSS APPLY
  (SELECT TOP 1 * FROM Message WHERE ThreadId = MessageThreadParticipant.MessageThreadId ORDER BY CreateDate DESC) AS Message
-- This gives the newest message for each of those threads.
-- The Login.LoginID could be either the Sender or Recipient

INNER JOIN
  Login AS [Sender]
    ON Sender.LoginID = Message.SenderLoginID
LEFT JOIN
  MessageReadState
    ON  MessageReadState.MessageID = Message.MessageID
    AND MessageReadState.LoginId <> Sender.LoginID
-- This gets the Sender's details, and tries to get whether the recipient read the message
-- It assumes the only MessageReadState entries are for the Sender and Recipient.

ORDER BY
  Message.CreateDate DESC;