public IReadOnlyList <Message> SearchMessagesByPhraseForUser(Guid userId, String phrase) { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var usersRepository = new UsersRepository(_connectionString); var chatsRepository = new ChatsRepository(_connectionString, usersRepository); var user = _usersRepository.GetUser(userId); using (var command = connection.CreateCommand()) { command.CommandText = "SearchMessagesByPhrase"; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@Text", phrase); using (var reader = command.ExecuteReader()) { Message prevMessage = null; var prevId = Guid.NewGuid(); var result = new List <Message>(); while (reader.Read()) { var message = new Message { Id = reader.GetGuid(reader.GetOrdinal("Id")), Date = reader.GetDateTime(reader.GetOrdinal("Date")), IsSelfDestructing = reader.GetBoolean(reader.GetOrdinal("IsSelfDestructing")), Text = reader.GetString(reader.GetOrdinal("Text")), Chat = chatsRepository.GetChatInfo(reader.GetGuid(reader.GetOrdinal("ChatId"))), User = user }; if (prevId != message.Id) { message.Attachments = (reader["File"] == DBNull.Value ? null : new List <byte[]> { reader.GetSqlBinary(reader.GetOrdinal("File")).Value }); if (prevMessage != null) { result.Add(prevMessage); } prevMessage = message; prevId = message.Id; } else { prevMessage?.Attachments.Add(reader.GetSqlBinary(reader.GetOrdinal("File")).Value); } } if (prevMessage == null) { return(null); } result.Add(prevMessage); return(result.AsReadOnly()); } } } }