Entity framework Code First - configure mapping for SqlQuery

Question

I'm using Entity Framework 5 (with Code First approach) to populate a class of mine from a legacy stored procedure with parameters, and this is working fine (details follow). My problem is that I want to map the columns' names to property with different names (I don't like the names coming from the Erp). I tried to use a Configuration class (like what I do when I map to views or tables) to specify the column name for properties with a different name, and here are my results:

• if I don't use the configuration class (I don't add it in the OnModelCreating method of the DbContext) then EF works but loads only the properties that match exactly with the name of the columns (and this is what I expected in this case); others property are null;
• if I use the configuration class (adding it to the modelBuilder in the OnModelCreating method of the DbContext) then EF raises an exception stating that "The data reader is incompatible with the specified '...Item'. A member of the type, 'Description', does not have a corresponding column in the data reader with the same name", and this sounds very strange to me, because in the configuration I specify that the property Description map to the column ItemDescription.

Why the configuration is affecting my result but its specification are not used to map the columns? Is there another way to specify this mapping using SqlQuery?

Here are the details:

My POCO class:

public class Item
    {
        public String Id { get; set; }
        public String Description { get; set; }
    }

The configuration class:

public class ItemConfiguration : EntityTypeConfiguration<Item>
    {
        public ItemConfiguration()
        {
            HasKey(x => new { x.Id });
            Property(x => x.Id).HasColumnName("Code");
            Property(x => x.Description).HasColumnName("ItemDescription");
        }
    }

The stored procedure return the data with the columns "Code" and "ItemDescription"; I call it in this way:

var par = new SqlParameter();
par.ParameterName = "@my_par";
par.Direction = ParameterDirection.Input;
par.SqlDbType = SqlDbType.VarChar;
par.Size = 20;
par.Value = ...;

var data = _context.Database.SqlQuery<Item>("exec spItem @my_par", par);

and with this I add the configuration to the context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
      modelBuilder.Configurations.Add(new ItemConfiguration());
}

Thank you!

Answer 1

I found here:

http://entityframework.codeplex.com/workitem/233?PendingVoteId=233

that "The SqlQuery method is designed not to take any mapping into account ...".

They say also "We agree that it would be useful to have the option to make SqlQuery honor Column attributes so we're keeping this issue open and putting it on our backlog for future consideration.", so, if you have my same problem, please vote :-)

Answer 2

Meanwhile, you can use this method. Few tests (because it worked for my classes) but not to difficult to fix if needed... It need a context (to retrieve mapped custom types) and it need a different connection to run a datareader on it at the same time.

Usage:
List students = Mapper.Map(context, (new SchoolContext()).Database.Connection, "Select * from Students");

public static class Mapper
{
    /// <summary>
    /// Maps the result of a query into entities.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="context">The context.</param>
    /// <param name="queryConnection">The connection to run the query. Must be different from the one on the context.</param>
    /// <param name="sqlQuery">The SQL query.</param>
    /// <returns>An entity list</returns>
    /// <exception cref="System.ArgumentNullException">
    /// context
    /// or
    /// queryConnection
    /// or
    /// sqlQuery
    /// </exception>
    public static List<T> Map<T>(DbContext context, DbConnection queryConnection, string sqlQuery) where T:new()
    {
        if (context == null) 
            throw new ArgumentNullException("context");
        if (queryConnection == null)
            throw new ArgumentNullException("queryConnection");
        if (sqlQuery == null) 
            throw new ArgumentNullException("sqlQuery");

        var connectionState = queryConnection.State;

        if (connectionState != ConnectionState.Open)
            queryConnection.Open();

        DbCommand command = queryConnection.CreateCommand();
        command.CommandText = sqlQuery;
        DbDataReader reader = command.ExecuteReader();

        List<T> entities = new List<T>();

        while (reader.Read())
        {
            entities.Add(InternalMap<T>(context, reader));
        }

        if (connectionState != ConnectionState.Open)
            queryConnection.Close();

        return entities;

    }

    private static T InternalMap<T>(DbContext context, DbDataReader reader) where T: new()
    {

        T entityObject = new T();

        InternalMapEntity(context, reader, entityObject);

        return entityObject;
    }

    private static void InternalMapEntity(DbContext context, DbDataReader reader, object entityObject)
    {

        ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
        var metadataWorkspace = ((EntityConnection)objectContext.Connection).GetMetadataWorkspace();

        IEnumerable<EntitySetMapping> entitySetMappingCollection = metadataWorkspace.GetItems<EntityContainerMapping>(DataSpace.CSSpace).Single().EntitySetMappings;
        IEnumerable<AssociationSetMapping> associationSetMappingCollection = metadataWorkspace.GetItems<EntityContainerMapping>(DataSpace.CSSpace).Single().AssociationSetMappings;

        var entitySetMappings = entitySetMappingCollection.First(o => o.EntityTypeMappings.Select(e => e.EntityType.Name).Contains(entityObject.GetType().Name));

        var entityTypeMapping = entitySetMappings.EntityTypeMappings[0];
        string tableName = entityTypeMapping.EntitySetMapping.EntitySet.Name;
        Console.WriteLine(tableName);

        MappingFragment mappingFragment = entityTypeMapping.Fragments[0];

        foreach (PropertyMapping propertyMapping in mappingFragment.PropertyMappings)
        {
            object value = Convert.ChangeType(reader[((ScalarPropertyMapping) propertyMapping).Column.Name], propertyMapping.Property.PrimitiveType.ClrEquivalentType);
            entityObject.GetType().GetProperty(propertyMapping.Property.Name).SetValue(entityObject, value, null);
            Console.WriteLine("{0} {1} {2}", propertyMapping.Property.Name, ((ScalarPropertyMapping)propertyMapping).Column, value);
        }

        foreach (var navigationProperty in entityTypeMapping.EntityType.NavigationProperties)
        {
            PropertyInfo propertyInfo = entityObject.GetType().GetProperty(navigationProperty.Name);

            AssociationSetMapping associationSetMapping = associationSetMappingCollection.First(a => a.AssociationSet.ElementType.FullName == navigationProperty.RelationshipType.FullName);

            // associationSetMapping.AssociationTypeMapping.MappingFragment.PropertyMappings contains two elements one for direct and one for inverse relationship
            EndPropertyMapping propertyMappings = associationSetMapping.AssociationTypeMapping.MappingFragment.PropertyMappings.Cast<EndPropertyMapping>().First(p => p.AssociationEnd.Name.EndsWith("_Target"));

            object[] key = propertyMappings.PropertyMappings.Select(c => reader[c.Column.Name]).ToArray();
            object value = context.Set(propertyInfo.PropertyType).Find(key);
            propertyInfo.SetValue(entityObject, value, null);
        }

    }
}