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

Medeni Baykal · Accepted Answer · 2016-09-19 13:50:41Z

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, 2016 at 13:50

answered May 9, 2016 at 9:49

Medeni Baykal

4,3331 gold badge30 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DaRoGa Over a year ago

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

Medeni Baykal Over a year ago

It's a bug (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.

DaRoGa Over a year ago

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 · Accepted Answer · 2016-05-09 09:35:11Z

1

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.

answered May 9, 2016 at 9:35

DaRoGa

2,4149 gold badges37 silver badges65 bronze badges

Collectives™ on Stack Overflow

Entity Framework Column Mapping

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related