How to get mysql char column type from C# Entity Framework Code First Model

Question

I have just done the equivalent of Hello World by creating an Entity Framework Code First model (I have written other C# programs but not with Entity Framework). I created a model like this:

class Preferences
{
    [Key]
    public string StationName { get; set; }
    [MaxLength(30)]
    public string MainDatabase { get; set; }
    [MaxLength(30)]
    public string DefaultSequence { get; set; }
}

and then had a little routine to add a record

var newPrefs = new Preferences
{
    StationName = "MATT",
    DefaultSequence = "NONE",
    MainDatabase = "NONE"
};
Preferences prefs = foo.Preferences.Add(newPrefs);

which then tries to create the database and fails when adding the primary key with the error

"BLOB/TEXT column 'StationName' used in key specification without a key length"

because it uses the data type "mediumtext" instead of CHAR or VARCHAR and MySQL can't use that type for a key.

Is there a method that is still more-or-less database agnostic that will tell MySQL to use the preferred type for the key column? Or do I just have to give up and make an integer key?

I also tried variations of the design like this but nothing worked.

class Preferences
{
    [Key,DataType("CHAR"),StringLength(30)]
    public string StationName { get; set; }
    [MaxLength(30)]
    public string MainDatabase { get; set; }
    [MaxLength(30)]
    public string DefaultSequence { get; set; }
}

Thanks for your help.

AaronLS · Accepted Answer · 2013-03-22 21:46:55Z

6

Try fluent mapping column type maybe:

modelBuilder.Entity<Preferences>()
    .Property(p => p.Id)
    .HasColumnType("CHAR(30)");

I think this is equivilent of [Column(TypeName = "CHAR(30)")] but not certain it's the same.

Edit: As per Matt's testing, length is seperate and, "char" may be case sensitive(there are a lot of settings in MySQL and other engiens related to case sensitivity in identifiers, and OS can play a part sometimes, so may vary): [Column(TypeName="char")][MaxLength(30)]

edited Mar 22, 2013 at 21:46

answered Mar 21, 2013 at 21:06

AaronLS

38.6k24 gold badges151 silver badges217 bronze badges

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

3 Comments

Matt Over a year ago

I will try it, but would doing something like that be database agnostic? For example, if I switched to MSSQL in the future would it accept CHAR for a column type?

Matt Over a year ago

Success! Although it is case sensitive and the length has to be in a separate attribute [MaxLength(30)] [Column(TypeName="char")]

AaronLS Over a year ago

@Matt char is ANSI standard SQL, it "should" be agnostic if all engines implement the standards consistently(I'm no expert on this though): troels.arvin.dk/db/rdbms/#data_types-char

Gert Arnold · Accepted Answer · 2013-03-21 20:47:08Z

2

I would suggest you apply a commonly accepted practice in relational database design, which is to have meaningless primary keys. Meaningless to the business domain that is.

class Preferences
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]  
    public int Id { get; set; }

    [MaxLength(30)]
    public string StationName { get; set; }
    [MaxLength(30)]
    public string MainDatabase { get; set; }
    [MaxLength(30)]
    public string DefaultSequence { get; set; }
}

Additional benefit: now you're free to change the StationName whenever necessary.

answered Mar 21, 2013 at 20:47

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

4 Comments

Matt Over a year ago

Thanks, but if I really wanted a char column...how would I do that?

AaronLS Over a year ago

As someone who for awhile tried to use natural keys, I will never do that again. There are so many little gotchas, and various levels of support in different frameworks, that you find yourself painted into a corner occasionally.

Matt Over a year ago

Right, I understand, but I have some tables that I need to be backwards compatible with.

Gert Arnold Over a year ago

I can't answer your question on a technical level because I'm not familiar with MySql. But you could try to generate a model database-first with DbContext and see what type is used.

Collectives™ on Stack Overflow

How to get mysql char column type from C# Entity Framework Code First Model

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related