How do I avoid code duplication when modelling a table, its layout, and its records, all of which share the same basic structure?

Question

This will be a somewhat abstract question.

I am working on a Data Access Layer framework which needs to distinguish between a table, its abstract schema/layout, and concrete table records. I'm afraid that because of this distinction, there will be much code duplication. I could need some input on ways to avoid this.

+-----------+
|    Foo    |
+-----------+
| +Id: Guid |
+-----------+

Note that this diagram could describe any of these: a table schema, a concrete table, or a concrete table record, having a field Id with type Guid.

• All that's known in the schema is the field's name and type.
• In the concrete (opened) table, the field's "column index" is additionally known.
• With records, all of these things are known, plus the field has a concrete value.

Translating this to code, I would get lots of similar types (in pairs of three). I'll use interfaces to keep the example brief; what I want to show is the similarity of the types:

// these interfaces only need to be implemented once:

interface ISchemaField<T>  {  string Name  { get; }       }


interface ITableField<T>   {  string Name  { get; }        
                              int    Index { get; }       }

interface IRecordField<T>  {  string Name  { get; }        
                              int    Index { get; }       
                              T      Value { get; set; }  }

---

// these three interfaces are an example for one entity; there would be
// three additional types for each additional entity.

interface IFooSchema
{
    ISchemaField<Guid> Id { get; }
    IFooTable Open(IDbConnection dbConnection, string tableName);
}

interface IFooTable
{
    ITableField<Guid> Id { get; }
    ICollection<IFooRecord> ExecuteSomeQuery();
}

interface IFooRecord
{
    IRecordField<Guid> Id { get; }
}

Now I would like to avoid having to write three very similar implementations for every entity in a concrete data model. What are some possible ways to reduce code duplication?

• I've thought of code generation (e.g. T4), which would be an OK solution, but I would prefer a "manually" coded solution (if there is one) with fewer lines of code & more readable code.

• I've thought about creating one class per entity which implements the schema, table, and record interface all at the same time... but that feels messy and like a violation of Separation of Concerns.

Answer 1

IMHO, I think you are going a bit too far with the abstraction of the table structure. Overusing interfaces can make the code hard to read. I find it very annoying, for example, that you can't press F12 to see the implementation of an object because it's an interface type.

A proven model that I have worked with for years and it's super easy to maintain is to keep all class names identical to the table names, then field names that match column names. You can then easily generate methods just using search and replace in the editor, and same goes for code changes. This way you don't need to keep column names and column indexes in memory. You just hardcode them in your data access tier (HARDCODING IS NOT ALWAYS BAD!). For example:

this.Price = reader["Price"] as decimal?;

Performance is very good with this approach and code is super maintainable!

Whatever approach you follow, the key thing is how you keep the mappings from table columns to class properties. I recommend hardcoding them and use a straightforward naming convention (column name = property name). This approach requires you to recompile when you add or change columns, but I think these changes do not happen often enough to justify having the column names in variables or reading them from a separate file. Doing so avoids recompilation, but makes the code less easy to follow. I don't think it's worth it.