Deep SQL relationships to a C# object model

Question

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.

Answer 1

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.

-- data table 0
SELECT ... FROM Client WHERE ClientId = @clientId;
-- data table 1
SELECT ... FROM ClientData WHERE ClientId = @clientId;
-- data table 2
SELECT b.*
FROM ClientData a
JOIN ClientJob b ON b.ClientDataId = a.ClientDataId
WHERE a.ClientId = @clientId;
-- etc.

Then building on that and with some up-front configuration, you can convert an entire DataSet into an object tree.

var sqlToObjectConfig = new []
{
    typeof(Client),
    typeof(ClientData),
    typeof(ClientJob)
};

// call your converter
ConvertToObject<Client>(sqlToObjectConfig, dataSet);
// it will use the config to know the type of each DataTable (by index)

So your normal steps would be:

1. Develop a query to get the data you want
2. Define a configuration array for the query's conversion to objects
3. Define a method to execute the query and convert

You could relatively easily query a specific branch like ClientJobs on down.

Answer 2

This is where an Object Relational/Mapping library, like NHibernate or Entity Framework comes in handy. It juggles the SQL, lazy loading and mapping for you.

Having a deep structure of interconnected tables is not a bad idea. Proper data design and normalization can dictate the need for these relationships. OR/M's try to bridge that gap between a relational data model and an object oriented one to make it easier to do precisely what you are finding difficult.

I have been directed to not use Entity Framework, and instead I must create hand written views or stored procedures for data access.

You can still call stored procedures for data retrieval, inserts, updates, and deletes using NHibernate. Despite what you have been told, an OR/M will save you headaches. Your table structure and relationships are complex enough to benefit from this.