Map the result of stored procedure with the returning object using .net core-CodePudding

I am new to .net core. I have started with a Get API call that will return a single Employee record as a result from SP based on the Employee Id. I have 2 classes, one is Employee and the other is Address. I have a composition relation between these 2 tables. I want to return the Employee object that contains Employee details along with the list of Addresses. So, my Employee class looks like this:

public class Employee{
public string Name {get;set;}
....
public List<Address> Addr {get; set;}
}

I am able to return the Employee object containing the Employee details. But, the Addr is returning null.

Here is my code:

 public async Task<Employee> GetEmployeeDetails(int employeeid)
    {
            Employee emp;
            var dictionary = new Dictionary<string, object>
                    {
                        {"@employeeid", employeeid }
                    };

            var parameters = new DynamicParameters(dictionary);

            using (IDbConnection con = await GetConnectionAsync(ConfigHandler.GetConnectionString(Constants.DefaultConnectionString)))
            {
                emp = (Employee)await ExecuteQueryFirstOrDefaultAsync<Employee>(con, "GetEmployeeDetails", parameters);
                con.Close();
            }
            return emp;
    }

Should I use multiple result sets or a single result set from SP? i.e. the resultset for employees and address differently or Should I return only one result set. Since I am interested in the first address. And secondly, how is it possible to map the result from the Sp to the object. I will be grateful for any help/advice. Thanks!

CodePudding user response：

You should create a class that should look like this

    public class GetEmployeeDetailsResult 
    {
      public string Name {get;set;}
      public string Email {get;set;}
      public string Gender {get;set;}
      public string Addr {get;set;}
      public string City {get;set;}
      public string Zip {get;set;}
    }

In this class you can have a method to map to Employee class

public Employee MapEmployeeFromGetEmployeeDetailsResult(List<GetEmployeeDetailsResult> source)
{
  var firstElement = source.FirstOrDefault();
   return new Employee
            {
                Name = firstElement.Name,
                Email = firstElement.Email,
                Gender = firstElement.Gender,
                //this line will iterate and create the node for addresses
                Addr = source                  
                    .Select(emp => new Address
                    {
                        Addr = emp.Addr, 
                        City = emp.City,
                        Zip = emp.Zip
                    })
                    .ToList()
            };
}

Then use this class as the return type for your SQL query

         public async Task<Employee> GetEmployeeDetails(int employeeid)
            {
                    Employee emp;
                    var dictionary = new Dictionary<string, object>
                            {
                                {"@employeeid", employeeid }
                            };
        
                    var parameters = new DynamicParameters(dictionary);
        
                    using (IDbConnection con = await GetConnectionAsync(ConfigHandler.GetConnectionString(Constants.DefaultConnectionString)))
                    {
                        var result = await ExecuteQueryFirstOrDefaultAsync<GetEmployeeDetailsResult>(con, "GetEmployeeDetails", parameters);
                        con.Close();
                        emp = new GetEmployeeDetailsResult()
                              .MapEmployeeFromGetEmployeeDetailsResult(result);
                    }
                    return emp;    
    }

If you do not want the line new GetEmployeeDetailsResult() .MapEmployeeFromGetEmployeeDetailsResult(result); you can put the method MapEmployeeFromGetEmployeeDetailsResult to static and changed the

new GetEmployeeDetailsResult().MapEmployeeFromGetEmployeeDetailsResult(result);

GetEmployeeDetailsResult.MapEmployeeFromGetEmployeeDetailsResult(result);