I have a database with deep table to table relationships
for example
Clients (one to many) -> ClientData (one to many) -> ClientJob (one to many) -> ClientProcess (one to many) -> ClientStep...
so on.
And these aren't one branch trees either. So Clients also has a one to many relationship with another tree of tables. The database has a very large number of tables and a bunch of relationships between them.
The C# models I have are similar in design to this
public class Client {
//base client data
public string ClientId { get; set; }
public string ClientName { get; set; }
//etc...
//Objects with the one to many relationship
public List<ClientData> {get; set;}
public List<ClientLocation> {get; set;}
//etc..
}
And the ClientData object has it's base data and lists of objects representative of it's relational database relationships and so on.
I have been directed to not use Entity Framework, and instead I must create hand written views or stored procedures for data access.
My question is, is there a design pattern I can employ here that makes this SQL/C# structure efficient and maintainable?
I would like the ability to also query substructures, so instead of always getting a complete Client and all of it's related objects, I can get a specific ClientData and all of it's nested objects but not the Client which owns it.