C# link how to populate parent list with sublist from two related lists

Question

C# link how to populate parent list with sublist from two related lists

c# dapper linq nested-lists

Question

I'm new to Linq but need to get some code finished quickly. I have two classes:

public class SAPMember
{
    public string EmployeeNumber { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<CostCentre> CostCentres { get; set; }
}

 public class CostCentre
{
    public string EmployeeNumber { get; set; }
    public string CostCentreCode { get; set; }
    public string Division { get; set; }
}

Each SAPMember can have one or more CostCentres.

This is my code to populate a two independent lists initially (using Dapper), which I then hope to combine into one list with a sublist:

    _SAPMembers = new List<SAPMember>();

   string sql = @"SELECT EmployeeNo as EmployeeNumber,
              LastName, FirstName
              FROM Employees";

   _SAPMembers = DbConn.Query<SAPMember>(sql).ToList();

   List<CostCentre> _CostCentres = new List<CostCentre>();

   string sql2 = @"SELECT EmployeeNo as EmployeeNumber, CostCentreCode,
                DivisionDescription as Division
                FROM Employees";

  _CostCentres = DbConn.Query<CostCentre>(sql2).ToList();

I've tried Linq grouping and joins etc but can't get the syntax right, and my _SAPMembers list populated with employee details plus related list of costcentres.

Code sample would be greatly appreciated. I've seen that this might be possible from one more complex Dapper query, but I think for my skill level linq might be a better solution.

Antony D

View more on Stack Overflow

Antony D · Accepted Answer · 3/8/2018 11:59:26 PM

As Amit suggested, I could use Dapper, as so: (note:I changed CostCentre key to EmpNo)

 if (_SAPMembers == null)

{ List _SAPMembers = new List();

var lookup = new Dictionary<string, SAPMember>();

var result = DbConn.Query<SAPMember, CostCentre, SAPMember>(@"
select DISTINCT e.EmployeeNo as EmployeeNumber, e.LastName, e.FirstName, c.EmployeeNo as EmpNo
, c.CostCentreCode as Name, c.DivisionDescription as Division
FROM EmployeeListing e
join EmployeeListing c on e.EmployeeNo = c.EmployeeNo
where e.TerminationDate is null and c.TerminationDate is null", (e, c) =>
{
    if (!lookup.TryGetValue(e.EmployeeNumber, out SAPMember sapMember))
        lookup.Add(e.EmployeeNumber, sapMember = e);

    if (sapMember.CostCentres == null)
        sapMember.CostCentres = new List<CostCentre>();

    sapMember.CostCentres.Add(c);

    return sapMember;
}, splitOn: "EmpNo");

_SAPMembers = result.Distinct().ToList();

} return _SAPMembers;

C# link how to populate parent list with sublist from two related lists

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator