One-to-Many composition and data retrieval

Question

This is a classic problem that I could never seem to come up with a solution for that I was happy with. What would be considered an OO-elegant and DB-scalable approach to this problem?

Employee
- Name, Phone, Meta, etc

Company
- Meta, etc
- Employee[]

CompanyRepository (RDMBS)
- Company GetById(int)
- Company[] GetAll()

Approach #1: 'GetById' Selects all from 'tCompany' and left joins 'tEmployee'. Sql Select yields 12 rows. Returns a single company populated with 12 employees.

'GetAll' Same Select as above but returns 12,000,000 rows. Through creative loops and logic returns 1,000,000 companies each with 12 employees.

Approach #2: 'GetById' ... same as above

'GetAll'. Selects all from 'tCompany' but nothing from 'tEmployee'. Sql select yields 1,000,000 rows. Returns 1,000,000 companies but each with a null 'Employees' property.

Approach #3 ... split the domain into 'SimpleCompany' containing only meta and 'ComplexCompany' that inherits from 'SimpleCompany' but has a 'Employees' property. GetById returns 'ComplexCompany' and GetAll returns 'SimpleCompany' array.

... each smells bad for different reasons.

Answer 1

What is business reason to get all companies (12,000,000 rows)? I would not advise you to have all 12,000,000 rows in memory at a time.

May be you should use pagination. Select limited set of companies at a time, then iterate from one page to another until no rows are returned.

public Company[] GetAllByPageNumber(int pageNumber, int pageSize)

The down side here is that Company should not be inserted or deleted while you are iterating.