Using LINQ to group on ID, EmailListID, PhoneListID, etc

So it looks like you're actually trying to load an object-graph (containing nodes of distinct types) from a database using SQL - and you're trying to do that using a single query.

That won't work. (Naïve, single-query) SQL is not suitable for querying object-graphs. This is why ORMs exist. However with some RDBMS-specific SQL extensions (e.g. T-SQL, PL/SQL, etc) to execute a query batch you can return an object-graph from a database.

The good news is that Dapper supports this scenario with QueryMultiple - however as far as I know it won't map collection properties, so you need to do that manually (so read on!)

(I note that Entity Framework, specifically, will generate single-SELECT queries that return redundant data in columns that represent lower-multiplicity data - this has its trade-offs but generally speaking separate queries can work faster overall with the right tweaks (such as using a table-valued variable to hold KEY values instead of re-evaluating the same WHERE criteria for every query in the batch - as always, check your indexes, STATISTICS objects, and execution plans!).

When querying for an object-graph, you'll write a SELECT query batch where each query returns all objects of the same type that has a JOIN with any other entities with a 1:1 or 1:0..1 multiplicity (if it isn't more efficient to load them in a separate query in the same batch).

In your case, I see you have:

[Employees]---(1:m)---[Phones]
[Employees]---(1:m)---[Emails]

[JobTitles]---(1:m)---[Employees]
[Departments]---(1:m)---[Employees]
[EmployeeStatus]---(1:m)---[Employees]        // is this an enum table? if so, you can probably ditch it

So try this:

• For the sake of simplicity, JobTitles, Departments, and EmployeeStatus can be done in a single query.
• I assume the foreign-key columns are NOT NULL so an INNER JOIN should be used instead of LEFT OUTER JOIN.

const String EMPLOYEES_PHONES_EMAILS_SQL = @"

-- Query 1: Employees, Departments, EmployeeStatuses
SELECT
    e.id,
    e.FirstName,
    e.LastName,
    e.Nickname,
    t.Name AS JobTitleName, -- This is to disambiguate column names. Never rely on column ordinals!
    d.Name AS DepartmentName,
    s.Name AS StatusName
FROM
    dbo.Employees AS e
    INNER JOIN dbo.JobTitles      AS t ON e.JobTitleID   = t.id
    INNER JOIN dbo.Departments    AS d ON e.DepartmentId = d.id
    INNER JOIN dbo.EmployeeStatus AS s ON e.StatusID     = s.id;

-- Query 2: Phones
SELECT
    p.EmployeeId,
    p.Number,
    p.Type
FROM
    dbo.Phones AS p;

-- Query 3: Emails
SELECT
    m.id,
    m.EmployeeId,
    m.Address,
    m.Type
FROM
    dbo.Emails AS m;
";

using( SqlMapper.GridReader rdr = connection.QueryMultiple( EMPLOYEES_PHONES_EMAILS_SQL ) )
{
    List<EmployeeModel>          employees          = ( await rdr.ReadAsync<EmployeeModel>() ).ToList();
    var phonesByEmployeeId = ( await rdr.ReadAsync<PhoneModel>   () ).GroupBy( p => p.EmployeeId ).Dictionary( grp => grp.Key grp => grp.ToList() );
    var emailsByEmployeeId = ( await rdr.ReadAsync<EmailModel>   () ).GroupBy( m => m.EmployeeId ).Dictionary( grp => grp.Key, grp => grp.ToList() );

    foreach( EmployeeModel emp in employees )
    {
        if( phonesByEmployeeId.TryGetValue( emp.EmployeeId, out var phones ) )
        {
            emp.Phones.AddRange( phones );
        }
        
        if( emailsByEmployeeId.TryGetValue( emp.EmployeeId, out var emails ) )
        {
            emp.Emails.AddRange( emails );
        }
    }
}

I'll admit that I'm not intimately familiar with Dapper - and there is a problem with the code above: it doesn't instruct Dapper how to read the included Department, JobTitleModel, and EmployeeStatus data in the first query. I assume there's some overload of ReadAsync to specify other included data.

If you find yourself doing this kind of logic repetitively you can define your own extension-methods to handle the worst parts (such as GroupBy().ToDictionary(), and populating a collection property from a dictionary of loaded entities).

If you had a filter criteria, then you'd need to either store the resultant EmployeeId key values in a TVV, or repeat the criteria on Employees as the right-hand-side of an INNER JOIN in the queries for Phones and Emails.

For example, if you wanted to add an ability to find all Employees (and their phone-numbers and e-mail addresses) by name, you'd do this:

const String EMPLOYEES_PHONES_EMAILS_SQL = @"

-- Query 0: Get EmployeeIds:
DECLARE @empIds TABLE ( EmpId int NOT NULL PRIMARY KEY );
INSERT INTO @empIds ( EmpId )
SELECT
    EmployeeId
FROM
    dbo.Employees
WHERE
    FirstName LIKE @likeFirst
    OR
    LastName LIKE @likeLast;

-- Query 1: Employees, Departments, EmployeeStatuses
SELECT
    e.id,
    e.FirstName,
    e.LastName,
    e.Nickname,
    t.Name AS JobTitleName, -- This is to disambiguate column names. Never rely on column ordinals!
    d.Name AS DepartmentName,
    s.Name AS StatusName
FROM
    dbo.Employees AS e
    INNER JOIN dbo.JobTitles      AS t ON e.JobTitleID   = t.id
    INNER JOIN dbo.Departments    AS d ON e.DepartmentId = d.id
    INNER JOIN dbo.EmployeeStatus AS s ON e.StatusID     = s.id

    INNER JOIN @empIds AS i ON i.EmpId = e.EmployeeId;

-- Query 2: Phones
SELECT
    p.EmployeeId,
    p.Number,
    p.Type
FROM
    dbo.Phones AS p
    INNER JOIN @empIds AS i ON i.EmpId = p.EmployeeId;

-- Query 3: Emails
SELECT
    m.id,
    m.EmployeeId,
    m.Address,
    m.Type
FROM
    dbo.Emails AS m
    INNER JOIN @empIds AS i ON i.EmpId = m.EmployeeId;
";

using( SqlMapper.GridReader rdr = connection.QueryMultiple( EMPLOYEES_PHONES_EMAILS_SQL, new { likeFirst = "%john%", likeLast = "%smith%" } ) )
{
    // same as before
}