Create a VARCHAR BINARY column in MySQL with Entity Framework Codefirst

Question

I have an application that is using EntityFramework Code First to create the database if it doesnt exist. And Im using MySQL for the database engine.

I have a table where one of the Key columns needs to be Case-Sensitive. I dont want to mark the entire database or table as case-sensitive using collation, so the best way to do it (that I know of) is to mark the column as BINARY.

Does anyone know how to do this using Code First? I tried doing:

[Column(TypeName = "varchar(100) BINARY")]

but it doesn't like that, the first time I access the DB Model I get

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code

Additional information: Sequence contains no matching element

Maybe I should just do an ALTER TABLE after the database is created?

Michael - sqlbot · Accepted Answer · 2016-11-30 23:24:54Z

1

I dont want to mark the entire database or table as case-sensitive using collation

Well, no, that doesn't make sense, but you should be able to do it on the column, using an appropriate collation, e.g.:

VARCHAR(100) COLLATE utf8_bin

Or just...

VARBINARY(100)

...is perfectly valid in MySQL (though I have no idea about whether EF will handle it correctly).

http://dev.mysql.com/doc/refman/5.7/en/binary-varbinary.html

answered Nov 30, 2016 at 23:24

Michael - sqlbot

180k28 gold badges388 silver badges458 bronze badges

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

4 Comments

Brian Tacker Over a year ago

I had actually tried this after I posted the question, unfortunately it does not work in my case because I need the EF property to be a string but this column type is only compatible with byte[]

Michael - sqlbot Over a year ago

Well, if it's binary, then it isn't a string -- it's bytes -- so that part makes sense in a strongly-typed environment. What about VARCHAR(100) COLLATE utf8_bin? That should be a case-sensitive string.

Brian Tacker Over a year ago

That works fine if Im creating the table manually, but Im looking for a way to do it using EntityFramework

Michael - sqlbot Over a year ago

Possibly -- "you can't" -- random google find: github.com/aspnet/EntityFramework/issues/2779 may or may not be applicable. From my perspective as a DBA, ORMs are generally really good at making simple things difficult or impossible. The notion that this "isn't a very common requirement" strikes me as rather ludicrous, sort of like saying "VARCHAR with a length other than 255 isn't a very common requirement."

Collectives™ on Stack Overflow

Create a VARCHAR BINARY column in MySQL with Entity Framework Codefirst

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related