How to handle varchar columns with Entity Framework?

Question

I have a table with a varchar column and I am using Entity Framework to use this column in the WHERE clause.

Entity Framework generates the query with N'' hence the index on the column cannot be used. Is there a way to force Entity Framework to generate varchar query instead of nvarchar one?

Answer 1

It actually depends on how you built your EF model, if you're using its Designer you can specify the required data type for each column (in your case simply set varchar and you're done).

If you're using a code-first approach you have to decorate the property that represents that column with the proper attribute (string objects in .NET are always Unicode so it'll map nvarchar by default), just do this (with data annotations, if you're using StringAttribute then se its IsUnicode property to false):

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

Answer 2

You can use the EntityFunctions.AsNonUnicode(string) method, so then the EF will not pass the string value as nvarchar. I had the same issue with EF 5 and EDMX, where the Oracle database was ignoring a varchar2 column index, and that's worked for me.

var q = (from TableClass t in TableName
         where t.varchar2Column == EntityFunctions.AsNonUnicode(someText));

MSDN reference: https://msdn.microsoft.com/pt-br/library/system.data.objects.entityfunctions(v=vs.110).aspx