EF Code First - globally configure varchar mapping over nvarchar

Question

I have a question that should be easy, but I myself could not find the answer.

I am using EF4 CTP-5 Code First Model with manually created POCOs. It treats string comparisons in generated SQL as

WHERE N'Value' = Object.Property 

I know that I can override this functionality using:

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

Which fixes the problem for this single occurrence and generates SQL correctly like:

 WHERE 'Value' = Object.Property 

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

The reason for correcting this is the efficiency of query execution. The comparison between varchar and nvarchar is very inefficient in SQL Server 2k5, where the varchar-varchar comparison is performed almost immediately.

Answer 1

Prior to EF 4.1, you can 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: for version 4.1, plugin agreements have been reset. Check out my blog for an alternative approach)

Answer 2

I continued Mark Kals's answer (and the Diego blog post) to globally set all lines for all objects as non-Unicode according to the question, instead of manually calling it for each 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); 

Answer 3

Here is the project by Sergey Barsky , which extends EF to allow user agreements, as a result of which you can create custom attributes instead of a free API.

Here is a snippet of code from here that demonstrates usefulness in action. What you do not see here is an attribute of decimal precision and others. So to your question, as soon as you set Unicode to false, it should be varchar , not 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 details.

Answer 4

Using the Diego blog to make public properties of PSCO varchar without using annotations:

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