{"id":1105,"date":"2020-10-29T18:25:51","date_gmt":"2020-10-29T21:25:51","guid":{"rendered":"https:\/\/benjaminray.com\/codebase\/?p=1105"},"modified":"2020-10-30T05:10:59","modified_gmt":"2020-10-30T08:10:59","slug":"force-strings-to-use-varchar-in-entity-framework-core","status":"publish","type":"post","link":"https:\/\/benjaminray.com\/codebase\/force-strings-to-use-varchar-in-entity-framework-core\/","title":{"rendered":"Force Strings to use VARCHAR in Entity Framework Core"},"content":{"rendered":"

The nvarchar<\/em> data type requires twice as much space to store as an equivalent-length varchar<\/em>. For that reason, it is usually recommended to use varchar<\/em> when you don't have a requirement to store Unicode characters.<\/p>\n

Entity Framework Core automatically uses the nvarchar<\/em> data type for strings, unless the column type is explicitly set, or .IsUnicode(false)<\/code> is set in OnModelCreating()<\/code>. If you want string columns to default to varchar<\/em>, and you don't care to explicitly set a column type (or set .IsUnicode(false)<\/code>) for every single string in your model, then you're either smart, lazy, or both. Speaking of lazy, here is how I automate this for myself in Entity Framework Core 3.1:<\/p>\n

protected override void OnModelCreating(ModelBuilder modelBuilder)\n{\n\n    \/\/ Other modelBuilder stuff here\n\n    \/\/ Force all string-based columns to non-unicode equivalent \n    \/\/ when no column type is explicitly set.\n    foreach (var property in modelBuilder.Model.GetEntityTypes()\n        .SelectMany(t => t.GetProperties())\n        .Where(\n               p => p.ClrType == typeof(string)    \/\/ Entity is a string\n            && p.GetColumnType() == null           \/\/ No column type is set\n        ))\n    {\n        property.SetIsUnicode(false);\n    }\n\n}\n<\/code><\/pre>\n
I haven't really analyzed the benefits or drawbacks of inserting this before or after other model changes in OnModelCreating()<\/code>, but I imagine it would interfere with any other code you have in there that checks or alters column types. So tweaking & testing may be required.<\/p>\n
Changes Required if Using ASP.Net Identity<\/h2>\n
If you are using ASP.Net Identity in your project, you need a couple of additions to this code. Calling base.OnModelCreating(modelBuilder);<\/code> is usually (always?) required when overriding OnModelCreating()<\/code> in a project that uses ASP.Net Identity. Also, an additional line in the .Where()<\/code> function filters out entities belonging to tables used by Identity. (ASP.Net Identity table names start with 'AspNet'.) In a test project which uses ASP.Net Identity, I found that my migrations could not be applied to the database because they attempted to alter some of the built-in Identity columns.<\/p>\n
protected override void OnModelCreating(ModelBuilder modelBuilder)\n{\n\n    \/\/ Other modelBuilder stuff here\n\n    \/\/ This line only required if using ASP.Net Identity:\n    base.OnModelCreating(modelBuilder);\n\n    \/\/ Force all string-based columns to non-unicode equivalent \n    \/\/ when no column type is explicitly set.\n    foreach (var property in modelBuilder.Model.GetEntityTypes()\n        .SelectMany(t => t.GetProperties())\n        .Where(\n               p => p.ClrType == typeof(string)    \/\/ Entity is a string\n            && p.GetColumnType() == null           \/\/ No column type is set\n            \/\/ Next line only required if using ASP.Net Identity:\n            && !p.DeclaringEntityType.GetTableName().StartsWith(\"AspNet\")\n        ))\n    {\n        property.SetIsUnicode(false);\n    }\n\n}\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
The nvarchar data type requires twice as much space to store as an equivalent-length varchar. For that reason, it is usually recommended to use varchar when you don’t have a  […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[19],"tags":[],"class_list":["post-1105","post","type-post","status-publish","format-standard","hentry","category-entity-framework"],"acf":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9GNjN-hP","jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/posts\/1105","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/comments?post=1105"}],"version-history":[{"count":9,"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/posts\/1105\/revisions"}],"predecessor-version":[{"id":1114,"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/posts\/1105\/revisions\/1114"}],"wp:attachment":[{"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/media?parent=1105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/categories?post=1105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/benjaminray.com\/codebase\/wp-json\/wp\/v2\/tags?post=1105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}