Bimal Das Bimal Das - 6 months ago 16
SQL Question

How to Copy Data From Sql Object to C# Model Property

I have two tables:


  • Employee:
    Id
    ,
    Name
    ,
    DepartmentId

  • Department:
    Id
    ,
    Name



Employee.cs:

public int Id {get;set;}
public string Name {get;set;}
public int DepartmentId {get;set;}


Department.cs:

public int Id {get;set;}
public string Name {get;set;}


ViewModel: EmployeeDepartmentVM:

public Department department {get;set;}
public List<Employee> employees {get;set;}


To Join these two tables I have written this code:

SELECT E.* , D.Id as DId , D.Name as DName
from [Employee] as E
LEFT OUTER JOIN [Department] as D
ON E.DepartmentId = D.Id
where D.Id = 1


How do I get EmployeeDepartmentVM type from the above query?

I know if I write a model like my problem will be solved:

public int Id {get;set;}
public string Name {get;set;}
public int DepartmentId {get;set;}
public int DId {get;set;}
public string Name {get;set;}


But I don't want to write extra model. Simply want bind query data into EmployeeDepartmentVM type.

Answer

I really don't see what's the challenge. The EmployeeDepartmentVM definition implies that you need to group the result set by the Department. Assuming the result set is unordered, it can be achieved by simply maintaining a dictionary for locating the view models of the already added departments during the read.

Which leads to something like this:

static List<EmployeeDepartmentVM> GetEmployeeDepartmentVMList(DbCommand command)
{
    var resultById = new Dictionary<int, EmployeeDepartmentVM>();
    using (var reader = command.ExecuteReader())
    {
        var employeeIdCol = reader.GetOrdinal("Id");
        var employeeNameCol = reader.GetOrdinal("Name");
        var departmentIdCol = reader.GetOrdinal("DId");
        var departmentNameCol = reader.GetOrdinal("DName");
        while (reader.Read())
        {
            var departmentId = reader.GetInt32(departmentIdCol);
            EmployeeDepartmentVM result;
            if (!resultById.TryGetValue(departmentId, out result))
            {
                result = new EmployeeDepartmentVM
                {
                    department = new Department(),
                    employees = new List<Employee>()
                };
                result.department.Id = departmentId;
                result.department.Name = reader.GetString(departmentNameCol);
                resultById.Add(departmentId, result);
            }
            var employee = new Employee();
            employee.Id = reader.GetInt32(employeeIdCol);
            employee.Name = reader.GetString(employeeNameCol);
            employee.DepartmentId = departmentId;
            result.employees.Add(employee);
        }
    }
    return resultById.Values.ToList();
}

Some things to note. The way written, your SQL query implies that Department related fields can be null (LEFT OUTER JOIN). However, the WHERE clause and also the Employee model (DepartmentId field non nullable) implies that it cannot happen. If the intent is to include the departments with no employees, then better change the join to RIGHT OUTER and use something like this:

// ...
if (reader.IsDBNull(employeeIdCol)) continue;
var employee = new Employee();
// ...   
Comments