EF Code First - Globally set varchar mapping over nvarchar

Question

I have what should be an easy question but I have been unable to find the answer myself.

I am using EF4 CTP-5 Code First Model with hand generated POCOs. It is processing string comparisons in generated SQL as

WHERE N'Value' = Object.Property

I am aware that I can override this functionality using:

[Column(TypeName = "varchar")]
public string Property {get;set;}

Which fixes the issue for that single occurrence and correctly generates the SQL as:

WHERE 'Value' = Object.Property

However, I am dealing with a VERY large domain model and going through each string field and setting TypeName = "varchar" is going to be very very tedious. I would like to specify that EF should see string as varchar across the board as that is the standard in this database and nvarchar is the exception case.

Reasoning for wanting to correct this is query execution efficiency. Comparison between varchar and nvarchar is very inefficient in SQL Server 2k5, where varchar to varchar comparisons execute almost immediately.

Answer 1

Before EF 4.1, you could use conventions and add the following convention to your ModelBuilder:

using System;
using System.Data.Entity.ModelConfiguration.Configuration.Properties.Primitive;
using System.Data.Entity.ModelConfiguration.Conventions.Configuration;
using System.Reflection;

public class MakeAllStringsNonUnicode :
    IConfigurationConvention<PropertyInfo, StringPropertyConfiguration>
{
    public void Apply(PropertyInfo propertyInfo, 
                      Func<StringPropertyConfiguration> configuration)
    {
        configuration().IsUnicode = false;
    }
}

(Taken from http://blogs.msdn.com/b/adonet/archive/2011/01/10/ef-feature-ctp5-pluggable-conventions.aspx)

---

UPDATE: Pluggable conventions were dropped for the 4.1 release. Check my blog for an alternative approach)

Answer 2

For anyone looking to do this in EF Core (v3 and above), a quick way to achieve this is through the ModelBuilder.Model property; it provides easy access to all entities and properties within the model.

A "bare-bones" implementation follows:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Apply configurations via regular modelBuilder code-first calls
    // ... 
    // ...


    // Override the configurations to force Unicode to false
    var entities = modelBuilder.Model.GetEntityTypes();
    foreach (var entity in entities)
    {
        foreach (var property in entity.GetProperties())
        {
            property.SetIsUnicode(false);
        }
    }
}

EF Core happily ignores the SetIsUnicode call on non-string properties, so you don't even have to check the property type (but you easily could if it makes you feel better :)

For those who prefer to be a bit more explicit, tacking on a where clause to the GetProperties() call will do the trick:

...
    var stringProperties = entity.GetProperties()
                                 .Where(e=> e.ClrType == typeof(string));
    foreach (var property in stringProperties)
    {
       property.SetIsUnicode(false);
    }
...

UPDATE - Entity Framework Core 6

You are now able to do this type of global mapping out-of-the-box using EF Core 6's pre-convention-model-configuration

A sample of how this would be achieved with this new feature is shown below:

configurationBuilder
    .DefaultTypeMapping<string>()
    .IsUnicode(false);

Answer 3

I extended Marc Cals' answer (and Diego's blog post) to globally set all strings on all entities as non-unicode as per the question, rather than having to call it manually per-class. See below.

/// <summary>
/// Change the "default" of all string properties for a given entity to varchar instead of nvarchar.
/// </summary>
/// <param name="modelBuilder"></param>
/// <param name="entityType"></param>
protected void SetAllStringPropertiesAsNonUnicode(
    DbModelBuilder modelBuilder,
    Type entityType)
{
    var stringProperties = entityType.GetProperties().Where(
        c => c.PropertyType == typeof(string)
           && c.PropertyType.IsPublic 
           && c.CanWrite
           && !Attribute.IsDefined(c, typeof(NotMappedAttribute)));

    foreach (PropertyInfo propertyInfo in stringProperties)
    {
        dynamic propertyExpression = GetPropertyExpression(propertyInfo);

        MethodInfo entityMethod = typeof(DbModelBuilder).GetMethod("Entity");
        MethodInfo genericEntityMethod = entityMethod.MakeGenericMethod(entityType);
        object entityTypeConfiguration = genericEntityMethod.Invoke(modelBuilder, null);

        MethodInfo propertyMethod = entityTypeConfiguration.GetType().GetMethod(
            "Property", new Type[] { propertyExpression.GetType() });

        StringPropertyConfiguration property = (StringPropertyConfiguration)propertyMethod.Invoke(
            entityTypeConfiguration, new object[] { propertyExpression });
        property.IsUnicode(false);
    }
}

private static LambdaExpression GetPropertyExpression(PropertyInfo propertyInfo)
{
    var parameter = Expression.Parameter(propertyInfo.ReflectedType);
    return Expression.Lambda(Expression.Property(parameter, propertyInfo), parameter);
}

/// <summary>
/// Return an enumerable of all DbSet entity types in "this" context.
/// </summary>
/// <param name="a"></param>
/// <returns></returns>
private IEnumerable<Type> GetEntityTypes()
{
    return this
        .GetType().GetProperties()
        .Where(a => a.CanWrite && a.PropertyType.IsGenericType && a.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>))
        .Select(a => a.PropertyType.GetGenericArguments().Single());
}

Finally, call it from your OnModelCreating(DbModelBuilder modelBuilder):

foreach (var entityType in GetEntityTypes())
    SetAllStringPropertiesAsNonUnicode(modelBuilder, entityType);