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.
Best Answer
Sounds like somebody wants you to write a lot of SQL.
I'm assuming here only reading data since that is all the question specifically mentions.
In the past, I have created an AutoMapper-like component which would map a DataRow to a class by examining the class's property names with reflection, then assign matching DataTable columns of same name and similar type to the class's properties.
Then building on that only a little, you can convert an entire DataTable to a list of objects. I believe AutoMapper will do this already.
The query to populate an entire object tree usually returned multiple DataTables in the DataSet.
Then building on that and with some up-front configuration, you can convert an entire DataSet into an object tree.
So your normal steps would be:
You could relatively easily query a specific branch like ClientJobs on down.