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.

Mcfarlin

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)

Ore

Is there a way to achieve all this with attributes? – Introit

@DiegoMijelshon, oops sorry :( – Introit

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

Unitarianism

I'm a fan of self-explanatory code, so I would filter for string properties. Nevertheless, it's a valuable addition to this question. – Peters

Never hurts to be more explicit @GertArnold - have updated the answer with an option to do so. Thanks for the feedback. – Unitarianism

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

Stoughton

Note that we've pulled this from our codebase, because it would occasionally crash and corrupt the EF metamodel. I have no idea why. – Stoughton

I guess these lines can stay outside of foreach. 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() }); – Picul

Answer 4

Here is a project from Sergey Barskiy that extends EF to allow custom conventions, which as a result, you can make custom attributes instead of the fluent API.

Here is a code snippet from here that demonstrates the utility in action. What you don't see here is the decimal precision attribute and others. So per your question, once you set Unicode to false, it should be varchar as opposed to nvarchar.

public class Product
{
    public int ProductId { get; set; }
    [Indexed("Main", 0)]
    public string ProductNumber { get; set; }
    [Indexed("Main", 1)]
    [Indexed("Second", direction: IndexDirection.Ascending)]
    [Indexed("Third", direction: IndexDirection.Ascending)]
    public string ProductName { get; set; }
    [String(4, 12, false)] //minLength, maxLength, isUnicode
    public string Instructions { get; set; }
    [Indexed("Third", 1, direction: IndexDirection.Descending)]
    public bool IsActive { get; set; }
    [Default("0")]
    public decimal? Price { get; set; }
    [Default("GetDate()")]
    public DateTime? DateAdded { get; set; }
    [Default("20")]
    public int Count { get; set; }
}

Read this and this for detail.

Introit

Answer 5

With Diego's blog help, to make the public properties of a POCO varchar without using anotations is :

    private void SetStringPropertiesAsNonUnicode<e>(DbModelBuilder _modelBuilder) where e:class
    {
        //Indiquem a totes les propietats string que no són unicode per a que es crein com a varchar
        List<PropertyInfo> stringProperties = typeof(e).GetProperties().Where(c => c.PropertyType == typeof(string) && c.PropertyType.IsPublic).ToList();
        foreach (PropertyInfo propertyInfo in stringProperties)
        {
            dynamic propertyExpression = GetPropertyExpression(propertyInfo);
            _modelBuilder.Entity<e>().Property(propertyExpression).IsUnicode(false);
        }
    }

    // Edit: Also stole this from referenced blog post (Scott)
    static LambdaExpression GetPropertyExpression(PropertyInfo propertyInfo)
    {
       var parameter = Expression.Parameter(propertyInfo.ReflectedType);
       return Expression.Lambda(Expression.Property(parameter, propertyInfo), parameter);
    }

Multimillionaire