Dynamic Translate To Avoid C# Syntax Errors

Consider the following database table (SQL Server 2005). I'd like to use this in EF (v6, .net 4.5.1) with the Translate function but after searching seems this is not supported. CR

Solution 1:

A bit tricky, but doable.

The idea is to utilize the Translate method by implementing and using a custom DbDataReader that performs the required mapping.

Before doing that, let implement a generic DbDataReader class that does just delegating to the underlying DbDataReader:

abstractclassDelegatingDbDataReader : DbDataReader
{
    readonly DbDataReader source;
    public DelegatingDbDataReader(DbDataReader source)
    {
        this.source = source;
    }
    publicoverrideobjectthis[string name] { get { return source[name]; } }
    publicoverrideobjectthis[int ordinal] { get { return source[ordinal]; } }
    publicoverride int Depth { get { return source.Depth; } }
    publicoverride int FieldCount { get { return source.FieldCount; } }
    publicoverride bool HasRows { get { return source.HasRows; } }
    publicoverride bool IsClosed { get { return source.IsClosed; } }
    publicoverride int RecordsAffected { get { return source.RecordsAffected; } }
    publicoverride bool GetBoolean(int ordinal) { return source.GetBoolean(ordinal); }
    publicoverride byte GetByte(int ordinal) { return source.GetByte(ordinal); }
    publicoverride long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length) { return source.GetBytes(ordinal, dataOffset, buffer, bufferOffset, length); }
    publicoverride char GetChar(int ordinal) { return source.GetChar(ordinal); }
    publicoverride long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length) { return source.GetChars(ordinal, dataOffset, buffer, bufferOffset, length); }
    publicoverride string GetDataTypeName(int ordinal) { return source.GetDataTypeName(ordinal); }
    publicoverride DateTime GetDateTime(int ordinal) { return source.GetDateTime(ordinal); }
    publicoverride decimal GetDecimal(int ordinal) { return source.GetDecimal(ordinal); }
    publicoverride double GetDouble(int ordinal) { return source.GetDouble(ordinal); }
    publicoverride IEnumerator GetEnumerator() { return source.GetEnumerator(); }
    publicoverride Type GetFieldType(int ordinal) { return source.GetFieldType(ordinal); }
    publicoverride float GetFloat(int ordinal) { return source.GetFloat(ordinal); }
    publicoverride Guid GetGuid(int ordinal) { return source.GetGuid(ordinal); }
    publicoverride short GetInt16(int ordinal) { return source.GetInt16(ordinal); }
    publicoverride int GetInt32(int ordinal) { return source.GetInt32(ordinal); }
    publicoverride long GetInt64(int ordinal) { return source.GetInt64(ordinal); }
    publicoverride string GetName(int ordinal) { return source.GetName(ordinal); }
    publicoverride int GetOrdinal(string name) { return source.GetOrdinal(name); }
    publicoverride string GetString(int ordinal) { return source.GetString(ordinal); }
    publicoverrideobject GetValue(int ordinal) { return source.GetValue(ordinal); }
    publicoverride int GetValues(object[] values) { return source.GetValues(values); }
    publicoverride bool IsDBNull(int ordinal) { return source.IsDBNull(ordinal); }
    publicoverride bool NextResult() { return source.NextResult(); }
    publicoverride bool Read() { return source.Read(); }
    publicoverride void Close() { source.Close(); }
    publicoverride T GetFieldValue<T>(int ordinal) { return source.GetFieldValue<T>(ordinal); }
    publicoverride Task<T> GetFieldValueAsync<T>(int ordinal, CancellationToken cancellationToken) { return source.GetFieldValueAsync<T>(ordinal, cancellationToken); }
    publicoverride Type GetProviderSpecificFieldType(int ordinal) { return source.GetProviderSpecificFieldType(ordinal); }
    publicoverrideobject GetProviderSpecificValue(int ordinal) { return source.GetProviderSpecificValue(ordinal); }
    publicoverride int GetProviderSpecificValues(object[] values) { return source.GetProviderSpecificValues(values); }
    publicoverride DataTable GetSchemaTable() { return source.GetSchemaTable(); }
    publicoverride Stream GetStream(int ordinal) { return source.GetStream(ordinal); }
    publicoverride TextReader GetTextReader(int ordinal) { return source.GetTextReader(ordinal); }
    publicoverride Task<bool> IsDBNullAsync(int ordinal, CancellationToken cancellationToken) { return source.IsDBNullAsync(ordinal, cancellationToken); }
    publicoverride Task<bool> ReadAsync(CancellationToken cancellationToken) { return source.ReadAsync(cancellationToken); }
    publicoverride int VisibleFieldCount { get { return source.VisibleFieldCount; } }
}

Nothing fancy - annoyingly overriding all abstract/meaningful virtual members and delegate to the underlying object.

Now the reader that performs name mapping:

classMappingDbDataReader : DelegatingDbDataReader
{
    Dictionary<string, string> nameToSourceNameMap;
    publicMappingDbDataReader(DbDataReader source, Dictionary<string, string> nameToSourceNameMap) : base(source)
    {
        this.nameToSourceNameMap = nameToSourceNameMap;
    }
    privatestringGetSourceName(string name)
    {
        string sourceName;
        return nameToSourceNameMap.TryGetValue(name, out sourceName) ? sourceName : name;
    }
    publicoverrideobjectthis[string name]
    {
        get { returnbase[GetSourceName(name)]; }
    }
    publicoverridestringGetName(int ordinal)
    {
        string sourceName = base.GetName(ordinal);
        return nameToSourceNameMap
            .Where(item => item.Value.Equals(sourceName, StringComparison.OrdinalIgnoreCase))
            .Select(item => item.Key)
            .FirstOrDefault() ?? sourceName;
    }
    publicoverrideintGetOrdinal(string name)
    {
        returnbase.GetOrdinal(GetSourceName(name));
    }
}

Again, nothing fancy. Override a few methods and perform a name to column name and vice versa mapping.

Finally, a helper method that does what you are asking:

publicstaticclassEntityUtils
{
    publicstaticObjectResult<T> ReadSingleResult<T>(this DbContext dbContext, DbDataReader dbReader)
        where T : class
    {
        var objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;
        var columnMappings = objectContext.GetPropertyMappings(typeof(T))
            .ToDictionary(m => m.Property.Name, m => m.Column.Name);
        var mappingReader = new MappingDbDataReader(dbReader, columnMappings);
        return objectContext.Translate<T>(mappingReader);
    }

    static IEnumerable<ScalarPropertyMapping> GetPropertyMappings(this ObjectContext objectContext, Type clrEntityType)
    {
        var metadata = objectContext.MetadataWorkspace;

        // Get the part of the model that contains info about the actual CLR typesvar objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace));

        // Get the entity type from the model that maps to the CLR typevar entityType = metadata
                .GetItems<EntityType>(DataSpace.OSpace)
                      .Single(e => objectItemCollection.GetClrType(e) == clrEntityType);

        // Get the entity set that uses this entity typevar entitySet = metadata
            .GetItems<EntityContainer>(DataSpace.CSpace)
                  .Single()
                  .EntitySets
                  .Single(s => s.ElementType.Name == entityType.Name);

        // Find the mapping between conceptual and storage model for this entity setvar mapping = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace)
                      .Single()
                      .EntitySetMappings
                      .Single(s => s.EntitySet == entitySet);

        // Find the storage property (column) mappingsvar propertyMappings = mapping
            .EntityTypeMappings.Single()
            .Fragments.Single()
            .PropertyMappings
            .OfType<ScalarPropertyMapping>();


        return propertyMappings;
    }

ReadSingleResult is the helper method in question. The GetPropertyMappings method is using part of the code from EF6.1 Get Mapping Between Properties and Columns.

Sample usage similar to the provided example:

var readMethodBase = typeof(EntityUtils).GetMethod("ReadSingleResult", new[] { typeof(DbContext), typeof(DbDataReader) });

foreach (var className in classNames)
{
    // ...var readMethod = readMethodBase.MakeGenericMethod(classType);
    var result = ((IEnumerable)readMethod.Invoke(null, newobject[] { dbContext, dbReader }))
        .Cast<dynamic>()
        .ToList();
    // ...
    dbReader.NextResult();
}

Hope that helps.