public void LoadByUserID(int userID, int organizationID) { Chats chats = new Chats(LoginUser); chats.LoadByUserID(userID, organizationID); if (chats.IsEmpty) { return; } StringBuilder builder = new StringBuilder(); foreach (Chat chat in chats) { if (builder.Length > 0) { builder.Append(","); } builder.Append(chat.ChatID.ToString()); } using (SqlCommand command = new SqlCommand()) { string text = @" ( SELECT cp.*, cc.FirstName, cc.LastName, cc.Email, cc.CompanyName, CASE WHEN DATEDIFF(second, cc.LastPing, GETUTCDATE()) < 15 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsOnline, ISNULL((SELECT TOP 1 cm.ChatMessageID FROM ChatMessages cm WHERE cm.ChatID= cp.ChatID ORDER BY cm.ChatMessageID DESC), 0) AS 'LastPostedMessageID', ISNULL((SELECT TOP 1 cp2.LastMessageID FROM ChatParticipants cp2 WHERE cp2.ChatID= cp.ChatID AND cp2.ParticipantType=0 AND cp2.ParticipantID=@UserID), 0) AS 'MyLastMessageID' FROM ChatParticipants cp LEFT JOIN ChatClients cc ON cc.ChatClientID = cp.ParticipantID WHERE cp.ChatID IN ({0}) --AND cp.DateLeft IS NULL AND cp.ParticipantType = 1 UNION SELECT cp.*, u.FirstName, u.LastName, u.Email, o.Name, CASE WHEN DATEDIFF(second, u.LastPing, GETUTCDATE()) < 15 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsOnline, ISNULL((SELECT TOP 1 cm.ChatMessageID FROM ChatMessages cm WHERE cm.ChatID= cp.ChatID ORDER BY cm.ChatMessageID DESC), 0) AS 'LastPostedMessageID', ISNULL((SELECT TOP 1 cp2.LastMessageID FROM ChatParticipants cp2 WHERE cp2.ChatID= cp.ChatID AND cp2.ParticipantType=0 AND cp2.ParticipantID=@UserID), 0) AS 'MyLastMessageID' FROM ChatParticipants cp LEFT JOIN Users u ON u.UserID = cp.ParticipantID LEFT JOIN Organizations o ON o.OrganizationID = u.OrganizationID WHERE NOT (cp.ParticipantID = @UserID AND cp.ParticipantType = 0) AND cp.ChatID IN ({0}) --AND cp.DateLeft IS NULL AND cp.ParticipantType = 0 ) ORDER BY ChatID "; command.CommandText = string.Format(text, builder.ToString()); command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@UserID", userID); Fill(command); } }