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

score 1 · Answer 1 · answered May 09 '16 at 09:35

After digging into my code, i have realised that the way i have executed the function is by using:

Database.SqlQuery<BinDetail>("Query for function").ToList();

Therefore, I realised that one solution for this would be to alter the query from:

SELECT * FROM.....

to:

SELECT ......, [On Hand] AS OnHand......

This does work and seems to retrieve data correctly, however, i dont think it is very pretty or good practice.

Therefore, if anybody has a more elegant solution to this or for calling a function, then please let me know as i am always looking to improve my code and our standards.

Medeni Baykal · Accepted Answer · 2016-09-19T13:50:41.587

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();
}

edited Sep 19 '16 at 13:50

answered May 09 '16 at 09:49

Medeni Baykal

4,223
1
28
36

Could you please explain why this works and how it works please as i am struggling to follow it a little bit. My EntityFramework reference is v4.0.30319 – DaRoGa May 09 '16 at 10:01
It's a bug (https://github.com/aspnet/EntityFramework/issues/4940) with EF Core 1.0 RC1, if you're not using it, this workaround will not fix your problem. I basicly orders column names A-Z so they maps correctly. – Medeni Baykal May 09 '16 at 10:17
I have upvoted as it seems like it could be a very helpful solution to somebody and it is a good answer but unfortunately it didnt work for me – DaRoGa May 09 '16 at 10:20

Entity Framework Column Mapping

2 Answers2