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

Question

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 1

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();
// ...  

EDIT: For completeness, here is another approach. It's similar to the way EF materializes similar queries and does not need temporary dictionary, but requires the input set to be ordered by the PK of the master table, so you need to add

ORDER BY D.Id

at the end of your SQL. Databases can easily and efficiently provide such ordering, and the benefit of this solution is that it allows deferred execution and does not require processing the whole set in order to start returning results. It's not essential if you want to just get a list, but can be useful in other scenarios.

static IEnumerable<EmployeeDepartmentVM> GetEmployeeDepartmentVMs(DbCommand command)
{
    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");
        for (bool more = reader.Read(); more;)
        {
            var result = new EmployeeDepartmentVM
            {
                department = new Department(),
                employees = new List<Employee>()
            };
            result.department.Id = reader.GetInt32(departmentIdCol);
            result.department.Name = reader.GetString(departmentNameCol);
            do
            {
                if (reader.IsDBNull(employeeIdCol)) continue;
                var employee = new Employee();
                employee.Id = reader.GetInt32(employeeIdCol);
                employee.Name = reader.GetString(employeeNameCol);
                employee.DepartmentId = result.department.Id;
                result.employees.Add(employee);
            }
            while ((more = reader.Read()) && reader.GetInt32(departmentIdCol) == result.department.Id);
            Debug.Assert(!more || reader.GetInt32(departmentIdCol) > result.department.Id); // Sanity check
            yield return result;
        }
    }
}

To get a list as in the first approach, just add ToList() after the call, e.g.

var result = GetEmployeeDepartmentVMs(command).ToList();