new List of Class object gives System.NullReferenceException

Question

I am upgrading my project from ASP.NET 4.8 to ASP.NET Core 6.0. The code below works fine for ASP.NET, but gives an error for Core. I am not sure why. I have tried many different ways to solve it, but so far no luck.

The error appears when I try to navigate to its resource: http://localhost/odata/Users.

UserService.cs

namespace pal_data.Business
{
    public class UserService
    {
        public List<User> GetData()
        {
            try
            {
                var lsret = new List<User>();   // ERROR: System.NullReferenceException: Object reference not set to an instance of an object.

                using (SqlConnection con = new(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnPort"].ConnectionString))
                {
                    var strSQL = "select a.*, b.RoleName from AppUser a inner join AccessRole b on a.RoleID = b.AccessRoleID";

                    using (SqlDataAdapter da = new(strSQL, con))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);

                        foreach (DataRow dr in dt.Rows)
                        {
                            var item = new User
                            {
                                UserID = int.Parse(dr["AppUserID"].ToString() ?? ""),
                                RoleID = int.Parse(dr["RoleID"].ToString() ?? ""),
                                Role = dr["RoleName"].ToString() ?? "",
                                UserName = dr["UserName"].ToString() ?? "",
                                Name = dr["Name"].ToString() ?? "",
                                Password = dr["Password"].ToString()
                            };

                            lsret.Add(item);
                        }
                    }
                }

                return lsret;
            }
            catch (SqlException ex)
            {
                // ...
            }
            catch (Exception e)
            {
                // ...
            }
        }
    }
}

UsersController.cs

namespace pal_data.Controllers
{
    [EnableCors]
    public class UsersController : ODataController
    {
        private readonly UserService m_service = new();

        [EnableQuery]
        public ActionResult<IEnumerable<User>> Get()
        {
            return Ok(m_service.GetData());
        }
    }
}

User.cs

namespace pal_data.Models
{
    public class User
    {
        public int UserID { get; set; }
        public string? FunctionResult { get; set; }
        public int ReturnCode { get; set; }
        public int RoleID { get; set; }
        public string? Role { get; set; }
        public string? UserName { get; set; }
        public string? Name { get; set; }
        public string? Password { get; set; }
    }
}

Working UserService.cs

• this returns data without a null error

namespace pal_data.Business
{
    public class UserService
    {
        public List<User> GetData()
        {
            var lsret = new List<User>
            {
                new User
                {
                    UserID = 1,
                    RoleID = 1,
                    Role = "asdfsdf",
                    UserName = "sadfsdf",
                    Name = "sdfasdf",
                    Password = "asdfsdf",
                }
            };

            return lsret;
        }
    }
}

Answer 1

Because the code failed on the SQL query, the error showed on this line var lsret = new List<User>(); for some reason. Lesson learned I guess. My SQL query is written properly now (without using ConfigurationManager) and the entity now returns properly.