Best way to generically convert SQL-Based classes to DTO using Class Name in C#?

Question

I have a collection of classes in a SQL database that all share the same structure, and cannot be changed. They are similar to the example below.

I am using Entity Framework Core and would like to have one set of code which can handle the crud for each of these tables. I've tried to generically the dto object back to the entity-framework generated class based on the table name saved in the DTO in the past, but had little luck.

I haven't found a way to convert the following class to a generic type using reflection below would be an example of the class I would like to convert to the type below it without having a switch statement or pre-defined mappings:

DTOClass
{
   Id;
   ShortName;
   LongName;
   TableName;
}

EmployeeType
{
   Id;
   ShortName;
   LongName;
   TableName;
}

Any help would be appreciated.

Answer 1

Step 1. Create an interface for the common fields (which in this case seems to be all of them).

interface INamedType
{
    int    Id { get; set; }
    string ShortName { get; set; }
    string LongName { get; set; }
}

Step 2. Let the EF generate separate models for your entities.

Step 3. Add the interface to the entities you wish to have under common code. Since EF classes are partial, you can do this in your own class file within your project.

partial class EmployeeType : INamedType
{
}
partial class JobType : INamedType
{
}
//etc.

Step 4. Now you can write common code for any entity that implements INamedType.

void PrintShortName(INamedType type)
{
    Console.WriteLine($"The type's short name is {type.ShortName}");
}

void Foo()
{
    PrintShortName(myEmployeeType);
    PrintShortName(myJobType);
}

Answer 2

I have had to do something like this myself, and of course I used reflection to do it. Here is the code I used, with an explanation below:

    private static readonly ConcurrentDictionary<(Type, Type), (PropertyInfo, PropertyInfo)[]> PICache =
        new ConcurrentDictionary<(Type, Type), (PropertyInfo, PropertyInfo)[]>();

    public static E2 EntityCopy<E1, E2>(E1 from, E2 to)
    {
        var types = (fromType: typeof(E1), toType: typeof(E2));
        if (PICache.TryGetValue(types, out var props))
        {
            foreach ((var p1, var p2) in props)
            {
                p2.SetValue(to, p1.GetValue(from));
            }
        }
        else
        {
            PropertyInfo[] GetOrderedProps(Type t) => t.GetProperties().OrderBy(p => p.Name).ToArray();
            var e1_props = GetOrderedProps(types.fromType);
            var e2_props = GetOrderedProps(types.toType);
            var props_new = new List<(PropertyInfo, PropertyInfo)>();

            var i1 = 0; var i2 = 0;
            while (i1 < e1_props.Count() && i2 < e2_props.Count())
            {
                var p1 = e1_props[i1]; var p2 = e2_props[i2];

                if (p1.Name == p2.Name)
                {
                    if (!p2.CustomAttributes.Any(a => a.AttributeType == typeof(KeyAttribute))
                        && !(p2.PropertyType.Name.Contains("ICollection"))
                        && p1.GetMethod != null
                        && p2.SetMethod != null)
                    {
                        try
                        {
                            p2.SetValue(to, p1.GetValue(from));
                            props_new.Add((p1, p2));
                        }
                        catch (Exception ex)
                        {
                            Log(ex.Message);
                        }
                    }
                    i1++; i2++;
                }
                else if (string.Compare(p1.Name, p2.Name) < 0) { i1++; }
                else { i2++; }
            }
            PICache.TryAdd(types, props_new.ToArray());
        }
        return to;
    }

Here, PICache is caching an ordered array of pairs of PropertyInfos. The idea is that the first time we see a pair of Types, we will go through the properties in a specific order (here I've ordered by name) and match the source property to the destination property. Once we've done this once the properties and their ordering are cached for perfomance.

You should be able to see some caveats here. I am not copying any ICollection property, since I am using EF6 with lazy loading. If I were to copy these properties I could end up loading data from the database I don't want, which massively impacts performance. I am also not copying the Key, which you may want to do.

Answer 3

You may need to extend the Dbcontext with a generic method that returns the DTO. context.GetDTO<Table>().Select(x=> new DTO() {Id = x.Id, ShortName = x.ShortName, LongName = x.LongName }).ToList()