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?

Adriano Repetti · Accepted Answer · 2013-03-12 14:20:20Z

7

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

edited Mar 12, 2013 at 14:20

answered Mar 12, 2013 at 12:04

Adriano Repetti

67.5k20 gold badges144 silver badges215 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Naomi Over a year ago

In the EDMX I have correctly set type (char) and length (6) for the column. I also added extra metadata class and decorated the property with Column(TypeName = "char")] and StringLength attributes. Yet I see the strange code executing SELECT TOP(2) and varchar(8000) for the parameter. How to fix it?

Adriano Repetti Over a year ago

@Naomi not enough details to answer that. Post it as a new question and don't forget to include relevant part in your class declaration and code you execute to produce such SQL!

Naomi Over a year ago

May be I'll add a new question tomorrow. I actually already asked this question in MSDN social.msdn.microsoft.com/Forums/vstudio/en-US/…

Rossco Over a year ago

I used the fluent syntax in MyContext.OnModelCreating:var myTable = modelBuilder.Entity<MyEntityType>(); myTable.Property(x => x.TextField).HasColumnType("varchar(8)");

Andre Okazaki · Accepted Answer · 2016-12-19 22:50:46Z

5

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

answered Dec 19, 2016 at 22:50

Andre Okazaki

511 silver badge2 bronze badges

Collectives™ on Stack Overflow

How to handle varchar columns with Entity Framework?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related