How to standardize generic Entity Framework C# code using reflection or generics?

Question

I have three database tables which represent comparison scores for common name strings. They're separated into individual tables to speed scanning time. All of them share the same basic structure of four columns: an ID, two strings and score represtning how different the strings are.

The DB is accessed via Entity Framework. In the implementation code, this leads me to having three near-identitical functions, one for each object type:

    private bool CheckSurnameJw(string string1, string string2, double threshold)
    {
        JwDistanceSurname jw = _rep.GetJwDistanceSurname(string1, string2);

        if (jw == null)
        {
            double dist = JaroWinklerProximity(string1, string2);
            JwDistanceSurname newJw = new JwDistanceSurname { Surname1 = string1, Surname2 = string2, JwScore = dist };
            _rep.Update(newJw);
            _rep.SaveChanges();

            return dist >= surnameProximityThreshold;
        }
        else
        {
            return jw.JwScore >= threshold;
        }
    }

Looking at this, while I could nip and tuck slightly, I can't see any clear areas where the function could be sensibly improved by farming code out to another function. But it annoys the hell out of me to have to re-implement the same logic block three times, to deal with three different identity types.

I wrapped all three classes in an interface specifiying the four columns to see if that would help me tidy things up. But it doesn't: I can't use a generic "get" function because each is querying a different table and, likewise, when I create a new instance of the class I need to give it the appropriate type.

Is there a way I can improve this using reflection/generics?

Answer 1

If your four columns all have the same columnNames, I'd definitely go for inheritance using Table Per Type or some kind of Composition

However, if the four columns do not have a common meaning, I'd use an interface.

Table per Type approach

Use this if the four columns represent typically the same thing and you would think that your tables are special kinds of this common thing:

abstract class Common
{
    public string String1 {get; set;}
    public string string2 {get; set;}
    public double Threshold {get; set;} 
}

class Table1 : Common
{
     public int Id {get; set;}
     ...
}

class Table2 : Common
{
     public int Id {get; set;}
     ...
}

Your DbContext will be like:

class MyDbContext : DbContext
{
    public DbSet<Table1> Table1s {get; set;}
    public DbSet<Table2> Table2s {get; set;}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Table1>().ToTable("Table1s");
        modelBuilder.Entity<Table2>().ToTable("Table2s");
    }
}

This is enough for entity framework to design two tables, where each table has the four common columns.

A DbSet represents the table in the database. The database doesn't know how CheckSurName. Hence I choose not to let class Common know how to CheckSurName. I create an extension function of Common instead.

See Extension Methods Demystified

static class CommonExtensions
{
    public static bool CheckSurName(this Common common)
    {
        JwDistanceSurname jw = _rep.GetJwDistanceSurname(common.String1, common.String2);
        ...
    }
}

Usage:

IEnumerable<Table1> myTableCollection = myDbContext.Table1s
    .Where(table => table...)
foreach (Table1 table1 in myTableCollection)
{
     bool surNameOk = table1.CheckSurName();
     Process(surNameOk);
}

Composition Approach

As most developers, I favour composition over inheritance. The approach will be similar, but instead of Inheritance composition is used

class Common ...

class Table1
{
    public int Id {get; set;}
    public Common Common {get; set;}
}

etc. This will also lead to one Table per type, every table containing all Common properties. The extension function will be similar. The only difference is that you don't perform the check on surnames on your retrieve tables, but on the Common of your retrieved tables:

IEnumerable<Table1> retrievedTables = ...
foreach (Table1 table in retrievedTables)
{
    bool SurnameOk = table1.Common.CheckSurName();
    ...
}

If Common represents something like a person, and your tables represents Items that have a Person, like a SchoolClass with a Teacher, and a School with a HeadMaster, I'd definitely go for this approach. After all a School is not a Person.

Interface approach

You described your columns as if only their types where common, not the names, nor their meaning. You just have two strings and one double, (and an Id, which is lost in your CheckSurName), and the only common thing is that they are two strings and a double. In that case I'd go for an Interface.

Objects that have properties that are needed to CheckSurName will implement ISurNameCheckable:

interface ISurnameCheckable
{
    public string String1 {get;}
    public string String2 {get;}
    public double Threshold {get;}
}

class Table1 : ISurnameCheckable
{
    public int Id {get; set;}
    public string Street {get; set;}
    public string City {get; set}

    // implementation of ISurnameCheckable
    public string String1 {get{return this.Street;}}
    public string String2 {get{return this.City;}}
    ...
}

The extension function is almost the same:

public static bool CheckSurName(this ISurnameCheckable surnameCheckable)
{
    JwDistanceSurname jw = _rep.GetJwDistanceSurname(
       surnameCheckable.String1, surnameCheckable.String2);
    ...
}