Dynamic translation to avoid C # syntax errors

Question

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

CREATE TABLE Foo ( pk INT NOT NULL PRIMARY KEY, Foo VARCHAR(100) ) 

Using convention matching that would create a Foo class with a Foo property that is not supported by C # syntax. I tried using ColumnAttribute :

 public partial class Foo { [Key] public virtual int pk {get;set;} [Column("Foo")] public virtual string Name {get;set;} } 

This seems to work, but I want the start page to request data about the loaded data through the stored procedure and MARS (and use a common structure so that I can reuse it on other pages), so I called the stored procedure and it loops through the result sets by calling ObjectContext.Translate via reflection (similar to the following, but this is abbreviated):

 var methTranslate = typeof(ObjectContext).GetMethod("Translate", new[] { typeof(DbDataReader), typeof(string), typeof(MergeOption) }); foreach (var className in classNames) { // ... var translateGenericMethod = methTranslate.MakeGenericMethod(classType); // ... reader.NextResult(); var enumerable = (IEnumerable)translateGenericMethod.Invoke(ObjectContext, new object[] { reader, entitySet.Name, MergeOption.AppendOnly }); } 

Of the few things I read, ColumnAttribute matching is not supported. From MSDN :

EF does not consider any matching when creating objects using the Translate method. It simply maps the column names in the result set to the property names in your classes.

And of course, I get errors:

The data reader is incompatible with the specified "Namespace.Foo". An element of type "Name" does not have a corresponding column in the data reader with the same name.

The problem is that I do not see an alternative or a way of pointing / hinting at a match. I could change the class name, but this is less desirable than property names.

Any workarounds or any other way to dynamically load data without using Translate ?

Answer 1

A bit complicated, but doable.

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

Before doing this, give the opportunity to implement a common DbDataReader class that simply delegates the underlying DbDataReader :

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

Nothing unusual - annoyingly redefining all abstract / meaningful virtual members and delegating the main object.

Now the reader who does the name matching:

 class MappingDbDataReader : DelegatingDbDataReader { Dictionary<string, string> nameToSourceNameMap; public MappingDbDataReader(DbDataReader source, Dictionary<string, string> nameToSourceNameMap) : base(source) { this.nameToSourceNameMap = nameToSourceNameMap; } private string GetSourceName(string name) { string sourceName; return nameToSourceNameMap.TryGetValue(name, out sourceName) ? sourceName : name; } public override object this[string name] { get { return base[GetSourceName(name)]; } } public override string GetName(int ordinal) { string sourceName = base.GetName(ordinal); return nameToSourceNameMap .Where(item => item.Value.Equals(sourceName, StringComparison.OrdinalIgnoreCase)) .Select(item => item.Key) .FirstOrDefault() ?? sourceName; } public override int GetOrdinal(string name) { return base.GetOrdinal(GetSourceName(name)); } } 

Again, nothing unusual. Override several methods and execute the name for the column name and vice versa.

Finally, a helper method that does what you ask:

 public static class EntityUtils { public static ObjectResult<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 types var objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace)); // Get the entity type from the model that maps to the CLR type var entityType = metadata .GetItems<EntityType>(DataSpace.OSpace) .Single(e => objectItemCollection.GetClrType(e) == clrEntityType); // Get the entity set that uses this entity type var 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 set var mapping = metadata.GetItems<EntityContainerMapping>(DataSpace.CSSpace) .Single() .EntitySetMappings .Single(s => s.EntitySet == entitySet); // Find the storage property (column) mappings var propertyMappings = mapping .EntityTypeMappings.Single() .Fragments.Single() .PropertyMappings .OfType<ScalarPropertyMapping>(); return propertyMappings; } 

ReadSingleResult is the helper method in question. The GetPropertyMappings method uses part of the code from EF6.1 Get a mapping between properties and columns .

A usage example similar to the following 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, new object[] { dbContext, dbReader })) .Cast<dynamic>() .ToList(); // ... dbReader.NextResult(); } 

Hope this helps.