Messaging Functionality Creating Sql query and Database View

Given the following:

I need to be able to get a list of all threads in which a given user has participated, sorted by most recent message first, with only the most recent message being displayed (1 message per thread)

This is the SQL query for the above:

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;

The way this works is this. a Loginid 1 send a LoginId 2 a message:

1. a new thread is created and a record is inserted into MessageThread

2. a new record is added into Message table with the treadId from above

3. TWO records are inserted into MessageThreadParticipant (Sender LoginId and Recipient LogiId)

   When a user opens his/hers list of messages MessageReadState will update ReadDate of the message.

QUESTION: I would like to create a view where I can simply filter on LoginId (I would be using LINQ). However I can’t do this with the Query above (since i would need to pass loginId somewhere within the sql statement). Is there anyway to modify the above SQL query in such way that would allow me to have a view?? what is it?

EDIT: i think i have it but i’m not sure if this is the best/most efficient solution:

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;

Answer

Does this do something like what you want?

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;