Async method reading mysql table produce list with duplicated elements-CodePudding

Async method returns list with duplicated elements. It is async method which is using mysql connector to connect with database. Then it execute query (SELECT *) and by using MySqlDataReader - I save and add to list rows until last ReadAsync() call.

Asynchronous programming is still black magic for me - I would appreciate any feedback or indicating unlogical code lines with explanation.

This method will be used in my web api controller and method purpose is to return all entries from 'Posts' table. Code is working fine when I 'reset' temp object each loop by using temp = new Post(); but I assume it is unacceptable ? What if my database would have not 15 but 15000 entries?

public async Task<List<Post>> GetPostsAsync()
        {
            List<Post> posts = new List<Post>();
            Post temp = new Post();

            try
            {
                await _context.conn.OpenAsync();

                MySqlCommand cmd = new MySqlCommand("USE idunnodb; SELECT * FROM Posts;", _context.conn);

                await using MySqlDataReader reader = await cmd.ExecuteReaderAsync();

                while(await reader.ReadAsync())
                {
                    temp.PostID = (int)reader[0];
                    temp.UserID = (int)reader[1];
                    temp.PostDate = reader[2].ToString();
                    temp.PostTitle = reader[3].ToString();
                    temp.PostDescription = reader[4].ToString();
                    temp.ImagePath = reader[5].ToString();

                    posts.Add(temp);

                }

                await _context.conn.CloseAsync();
            }
            catch (Exception ex)
            {
                return Enumerable.Empty<Post>().ToList();
            }

            return posts;
        }

CodePudding user response：

Looks like you can only read data by looping through MySqlDataReader, according to your code logic, you need to read each piece of data, and then add them to the List one by one for output.

Your code is behaving this way because temp has been declared & instantiated outside of your reader.ReadAsync() statement, you are updating the same object reference each time around the loop which is why you are seeing repeating objects in your list.

So you need to instantiate in reader.ReadAsync() loop:

while(await reader.ReadAsync())
{
    Post temp = new Post();
    ...
}