Results multimapping a.k.a master-detail in F#

Let's consider how we can query for the relational data given by these sample types:

Company
- Id
- Name
- Country

Employee
- CompanyId
- Firstname
- Lastname

They are in relation where employees belong to a certain company identified by the CompanyId.

How can we map the complete set of data retrieved in a single query?

We can introduce monoidical types for Company and Employee and use grouping.

Let's consider results retrieved with a query like this:

select Id, Name, Country, Firstname, Lastname from Company inner join Employee on Id = CompanyId;

And let's consider we have them as a list of DTO values:

type CompanyReportView =
    { Id: int
      Name: string
      Country: string
      Firstname: string
      Lastname: string }

As a result, we want to provide us with a report:

type Report = { AllCompanies: Company list }

and Company =
    { Name: string
      Country: string
      Employees: Employee list }

and Employee = { Firstname: string; Lastname: string }

Given sample data:

let list: CompanyReportView list =
    [ { Id = 123
        Name = "Berechtigtes Interesse"
        Country = "DE"
        Firstname = "Alex"
        Lastname = "Lange " }
      { Id = 123
        Name = "Berechtigtes Interesse"
        Country = "DE"
        Firstname = "Oscar"
        Lastname = "Vogel" }
      { Id = 123
        Name = "Berechtigtes Interesse"
        Country = "DE"
        Firstname = "Max"
        Lastname = "Werner" } ]

We can prepare a report:

module Report =

    let private toCompanyDto (item: CompanyReportView) =
        { Name = item.Name
          Country = item.Country
          Employees = [] }

    let private toEmployeeDto (item: CompanyReportView) =
        { Firstname = item.Firstname
          Lastname = item.Lastname }

    let prepare (list: CompanyReportView list): Report =
        let pairsByCompany =
            list
            |> List.map (fun item -> toCompanyDto item, toEmployeeDto item)
            |> List.groupBy fst

        let companies =
            pairsByCompany
            |> List.map
                (fun (company, pairsByCompany) ->
                    let employees = pairsByCompany |> List.map snd
                    { company with Employees = employees })

        { AllCompanies = companies }

Finally, let report = Report.prepare list will print:

val report : Report =
  { AllCompanies =
                  [{ Name = "Berechtigtes Interesse"
                     Country = "DE"
                     Employees =
                                [{ Firstname = "Alex"
                                   Lastname = "Lange " };
                                 { Firstname = "Oscar"
                                   Lastname = "Vogel" };
                                 { Firstname = "Max"
                                   Lastname = "Werner" }] }] }