Disable conversion between VARCHAR and NVARCHAR when using Entity Framework over an existing SQL server database

Question

I have an existing database SQL server and I'm using EF6 over it. Let's assume I have the following code:

private sealed class Foo
{
    public int Id { get; set; }

    public string Bar { get; set; }
}

private sealed class FooConfiguration : EntityTypeConfiguration<Foo>
{
    public FooConfiguration()
    {
        ToTable("Foos");
        HasKey(e => e.Id);
        Property(e => e.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

private class FooContext : DbContext
{
    public FooContext(string connectionString)
        : base(connectionString)
    {
        Database.SetInitializer<FooContext>(null);
    }

    public virtual DbSet<Foo> Foos { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Configurations.Add(new FooConfiguration());
    }
}

Usage:

const string bar = "حالياً البحث عبر جوجل";
int fooId;
using (var db = new FooContext(connectionString))
{
    var foo = db.Foos.Add(new Foo { Bar = bar });
    db.SaveChanges();
    fooId = foo.Id;
}

using (var db = new FooContext(connectionString))
{
    var foo = db.Foos.First(f => f.Id == fooId);
    if (foo.Bar != bar)
    {
        Console.WriteLine("Oops!");
    }
}

What can possibly go wrong? Well, if the Bar column has type VARCHAR(MAX) instead of NVARCHAR(MAX), then we are in a bad position, because VARCHAR cannot properly store that string of Unicode characters, so we get a bunch of question marks instead.

So, the question is: can I some how disable this conversion between VARCHAR and NVARCHAR and enforce EF to throw some sort of type mismatch exception during SaveChanges? I've tried to use this in my configuration class:

Property(e => e.Bar).IsUnicode(true);
Property(e => e.Bar).HasColumnType("NVARCHAR(MAX)");

but it did nothing.

Thanks in advance.

This sounds more like an xy question. If you need to ensure that unicode characters are stored, then you need to ensure that the data types are declared correctly. If you're losing data because the wrong data type is being used, it's not the application or the data engine's fault; it's that the data types need to be configured correctly. — Thom A, Mar 16 '18 at 16:12
@Larnu You are absolutely right, it is a problem with incorrect types in my database, not the application code. I'm just looking for a solution that can help me find these problems before we have inserted a zillion rows in 'Foos' table — RX_DID_RX, Mar 16 '18 at 16:15
Is this Code First? There are some pointers (and a library) in [this question](https://stackoverflow.com/q/13089448/4137916), which basically asks what you're asking in a more general way, but for Code First you might be shafted since EF isn't going to laboriously retrieve metadata and compare notes first. Note that this is actually a pretty hard problem in general; most data access is ineffective or inefficient if it can't assume your database matches your model (and some schema differences are, of course, benign, but that's application dependent). — Jeroen Mostert, Mar 16 '18 at 16:22

Soheil Alizadeh · Answer 1 · 2018-03-17T08:46:30.420

The default data type for string columns is nvarchar(MAX), you don't have the need to set data type in your columns for changing to NVARCHAR(MAX). Ef has default conventions that Specify it. here is explained for knowing conventions.

if you want config generally convention for your models, you can do this:

public class DataTypeConvention : Convention
{
    public DataTypeConvention()
    {
        Properties<string>().Configure(config => { config.HasColumnType("nvarchar(MAX)"); });
    }
}

on your DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Conventions.Add(new DataTypeConvention());
}

Disable conversion between VARCHAR and NVARCHAR when using Entity Framework over an existing SQL server database

1 Answers1