Entity Framework Column Mapping

Question

I have an issue with mapping an object property to a column from a database function.

The database function returns a column called [On Hand]. Therefore my model property is called OnHand.

This obviously does not map correctly and fails to retrieve the data correctly for that column.

I have attempted the following in order to resolve this:

Editing the model to use an annotation

[Column("On Hand")]
public int OnHand { get; set; }

Using Fluent API

modelBuilder.Entity<BinDetail>()
    .Property(e => e.OnHand)
    .HasColumnName("On Hand");

Neither of these approaches have worked either together or independently.

The only way i can get this to work on the test database is to alter the return column of the function to [OnHand], however, due to other systems using this function, this is not an option to use on the live database.

Any suggestions anybody has would be greatly appreciated

Answer 1

If you're using Entity Framework Core 1.0 RC 1, there is a bug (it's fixed at RC2 and onwards) causes this.

A workaround is ordering fields by A to Z, a quick sample:

"SELECT " + GetColumnNames<Unit>("R") + " FROM Unit AS R"

Helper methods:

private static Dictionary<Type, PropertyInfo[]> getPropertiesCache = new Dictionary<Type, PropertyInfo[]>();

public static string GetColumnNames<T>(string prefix)
{
    var columns = GetProperties(typeof(T)).OrderBy(i => i.Name).Select(i => $"[{prefix}].[{i.Name}]");

    return string.Join(", ", columns);
}

public static IEnumerable<PropertyInfo> GetProperties(Type type)
{
    if (getPropertiesCache.ContainsKey(type))
        return getPropertiesCache[type].AsEnumerable();

    var properties = type
        .GetTypeInfo()
        .DeclaredProperties;

    getPropertiesCache.Add(type, properties.ToArray());

    return getPropertiesCache[type].AsEnumerable();
}