One-to-Many composition and data retrieval
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开发者_JAVA百科,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.
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.
精彩评论