Entity Framework v4 and underscores in column names

Question

Has anyone found a solution or workaround to the issue of how Entity Framework v4 supports column names prefixed with an underscore? I've noticed that properties on entities created by EFv4 have the underscore prefix replaced with a "C_" prefix. So, for example, the database column:

Order._activity

becomes the entity property:

Order.C_activity

Then when I attempt a query I understandably get the error:

The data reader is incompatible with the specified 'CorporateModel.Order'. A member of the type, 'C_activity', does not have a corresponding column in the data reader with the same name.

This is a legacy database with dependent application so changing the column names in the database is not an option.

Thanks for any input.

Answer 1

OK, I don't have exact solution for you, but have a similar situation. EF Generated fields ends with ID if when referencing another table. Example. Table Person has a column JobID instead of Job. We don't like that. This was the issue: http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/f8ddb2be-78c9-4296-b293-37b7bc8e8fd7

So what my team mate did is to "override" the default convention. Please forgive if this is not exactly your case. I think it should give you some hints. This is with EF V 4.1

 public class ASRCDb : DbContext, IUnitOfWork
{
    public void Complete()
    {
        SaveChanges();
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
        AddAllEntities(modelBuilder);
    }

    static void AddAllEntities(DbModelBuilder modelBuilder)
    {
        var entity = typeof(DbModelBuilder).GetMethod("Entity");
        foreach (var entityType in GetEntityTypes())
        {
            var entityTypeConfiguration = entity.MakeGenericMethod(entityType).Invoke(modelBuilder, null);
            foreach (var propertyInfo in GetReferenceProperties(entityType))
                ConfigureRelationship(propertyInfo, entityTypeConfiguration);
        }
    }

    static IEnumerable<PropertyInfo> GetReferenceProperties(Type entityType)
    {
        return entityType.GetProperties().Where(p => typeof(IEntity).IsAssignableFrom(p.PropertyType));
    }

    static IEnumerable<Type> GetEntityTypes()
    {
        return typeof(Entity).Assembly.GetTypes().Where(type => type.IsClass && !type.IsAbstract);
    }

    static void ConfigureRelationship(PropertyInfo propertyInfo, dynamic entityTypeConfiguration)
    {
        var required = propertyInfo.GetCustomAttributes(typeof(RequiredAttribute), true).Any();
        var navigation = required
                             ? entityTypeConfiguration.HasRequired(GetPropertyExpression(propertyInfo))
                             : entityTypeConfiguration.HasOptional(GetPropertyExpression(propertyInfo));
        UsePropertyNameAsColumnName(propertyInfo, navigation);
    }

    static void UsePropertyNameAsColumnName(PropertyInfo propertyInfo, dynamic navigation)
    {
        Action<ForeignKeyAssociationMappingConfiguration> mapKey = x => x.MapKey(propertyInfo.Name);
        navigation.WithMany().Map(mapKey);
    }

    static dynamic GetPropertyExpression(PropertyInfo propertyInfo)
    {
        var parameter = Expression.Parameter(propertyInfo.ReflectedType);
        return Expression.Lambda(
            typeof(Func<,>).MakeGenericType(propertyInfo.ReflectedType, propertyInfo.PropertyType),
            Expression.Property(parameter, propertyInfo),
            parameter);
    }
}

All the Credit is to my buddy Diego Mijelshon.

Answer 2

It seems that EF does not support property names that start with an underscore and that this is a pattern constraint enforced by the EDM schemas. One example is the SSDL schema.

I went with another solution: using LINQ to SQL instead of EF to build the model, then using the reflection provider to implement IQueryable/IUpdatable on the model (see here) so I could expose it via a WCF Data Service. This keeps underscores intact in the model and in the resulting OData returned from the service.

However, there is a caveat: since Microsoft client proxy code generators use the EdmItemCollection to parse the metadata during generation, attempting to generate a proxy (e.g. in Visual Studio) fails for a service with entity properties starting with underscores. You will need to consuming the OData from such a service without the aid of one of these proxy generators.