21

I know I can add a Data Annotation to each property to set it's column type to VARCHAR but I have hundreds of columns I want to be VARCHAR instead of NVARCHAR is there some way to setup EF6 to make this the default?

Matthew Verstraete
  • 6,335
  • 22
  • 67
  • 123

1 Answers1

33

Looking up EF conventions, I think you can do something like this:

modelBuilder.Properties<string>().Configure(c => c.HasColumnType("varchar"));

I largely ripped this off from http://msdn.microsoft.com/en-us/data/jj819164#order and haven't tested it.

[Edit:] As @Shimmy points out in the comments, there's also c => c.IsUnicode(false) which appears to do the same thing without hardcoding a column type.

Jimmy
  • 27,142
  • 5
  • 87
  • 100
  • Do you know if this will override the Data Annotations? I have some annotations to set a few columns to `CHAR` – Matthew Verstraete Jan 07 '14 at 00:41
  • 2
    It looks like the data annotations will take precedence as they are a more specific setting (most specific wins). – Jimmy Jan 07 '14 at 04:22
  • 6
    @Jimmy, what's the difference between the setting in your answer and `c.IsUnicode(false)`? – Shimmy Weitzhandler May 08 '15 at 10:01
  • @Shimmy, my answer sets the column type explicitly, whereas IsUnicode looks like a more fluent API to describe the intent. Technically, the implementation of IsUnicode could probably determine the most suitable column type, and also evolve over time if new types were introduced, or account for different supported types in different versions of SQL (although in this case I think everything supports varchar/nvarchar). – Jimmy May 08 '15 at 23:14
  • @Jimmy, so currently, when working against SQL-Server, do you know if `IsUnicode` will make it generate a `varchar` instead of `nvarchar`? – Shimmy Weitzhandler May 09 '15 at 18:02
  • 2
    @Shimmy it looks that way, see line 920 in this file: https://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework.SqlServer/SqlProviderServices.cs – Jimmy May 10 '15 at 17:15
  • @Jimmy thanks for nailing it out! I see it in the method `GetStringDbType`, no line numbers in CodePlex... – Shimmy Weitzhandler May 12 '15 at 12:28
  • Disabling Unicode just to change from NVarchar to Varchar is a bad idea if you need to store unicode data like non-english text. Also it seems rather obscure. In that case I much prefer the HasColumnType("varchar") property - it does what it's supposed to without obscure side-effects. – Gertsen Apr 26 '16 at 08:10
  • 1
    @Gertsen It's all about which one you want to be the default. if you want the default to be `varchar` and then be forced to use `HasColumnType("nvarchar")` or something, well then that's a main effect which is the exact inverse of the current state of affairs; no side-effects going on here! – ErikE Mar 14 '17 at 02:20
  • Using `IsUnicode` will allow the column to be `char` (if `IsFixedLength` is set) or `varchar`, whereas `HasColumnType("varchar")` will potentially override `char` to `varchar`. – ErikE Mar 14 '17 at 05:55
  • This is great but doesn't work for EF Core, is there an equivalent? – SBFrancies Jan 04 '18 at 13:10
  • 1
    @SBFrancies - actually, from what I can tell, .IsUnicode() does work in EF Core. I'm using EF Core 2.0. Running a migration after adding it changed my nvarchar columns to varchar. Or do you mean that there is no IsFIxedLength() in order to generate a "char" column? – BioData41 Jul 02 '18 at 15:45
  • @BioData41 I think my comment was from before the edit which added the .IsUnicode solution. I was referring to the original answer. – SBFrancies Jul 02 '18 at 20:31
  • @ErikE I've tried `property.SetIsUnicode(false)` and it's still NVARCHAR in the generated migration .cs file... EF7. – Alexander Feb 22 '23 at 00:22
  • @Alexander Hmmm I don't know what to tell you. I haven't worked with EF since about the time I last commented. – ErikE Feb 22 '23 at 17:23
  • @ErikE it turned out there was another type of oproperty to check. I was not aware, since it was a 3rd party lib :) In fact, the solution does work. – Alexander Feb 23 '23 at 16:40