What is the proper usage of HasColumnType and Database generated

Question

I'm migrating a substantial EF model of ~80 entites from EF4 to EF6, and I'm also changing it from a Designer EDMX-generated database, to a Code First database.

Right now I'm configuring the entity relationships using EF fluent-api, and I'm not certain I'm doing it correctly.

It's type in the SQL Server database is varchar(50), so should I be configuring it like this?

        mb.Entity<SomeObject>()
            .Property(so => so.Type)
            .IsUnicode(false)
            .HasColumnName("Type")
            .HasColumnType("varchar")
            .HasMaxLength(50)
            .IsRequired()
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

or like this, without the HasMaxLength(50)?

        mb.Entity<SomeObject>()
            .Property(crt => crt.Type)
            .IsUnicode(false)
            .HasColumnName("Type")
            .HasColumnType("varchar(50)")
            .IsRequired()
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

Additionally, say I have another object with a GUID ID:

    mb.Entity<AnotherObject>()
        .Property(ao => ao.ID)
        .HasColumnName("ID")
        .HasColumnType("uniqueidentifier")
        .IsRequired()
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

In the database it has a default of newsequentialid(), should I be configuring it with DatabaseGeneratedOption.None, DatabaseGeneratedOption.Identity, or DatabaseGeneratedOption.Computed?

What is the difference between those options? Additionally, in the code GUIDs are mostly being assigned at object instantiation, like so:

Guid ID = new Guid.NewGuid()

Is that appropriate?

score 14 · Accepted Answer · answered Jun 15 '14 at 10:50

14

varchar(50) is not a column type itself, 'varchar' is a data type while (50) is the maximum length of characters in a string. you have to do it like this

mb.Entity<SomeObject>()
            .Property(so => so.Type)
            .IsUnicode(false)
            .HasColumnName("Type")
            .HasColumnType("varchar")
            .HasMaxLength(50)
            .IsRequired()
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

for your second question, if you dont give GUID, it will be set to default value of GUID in the settings of the database, if you want to set it, use the GUID generator class.

answered Jun 15 '14 at 10:50

Aunn Raza

468
2
12

Do you know which way is considered best practice for assigning GUIDs? From what I've heard, having the dbms assign it is "safer" because it will internally prevent repeat GUIDs, while an externally assigned GUID could conflict? Although the difference is a bit academic, because the probabilities of generating of GUID hash conflict is tiny. – kpk47 Jun 15 '14 at 17:17
Primary key value is internal thing of the db and normally you shouln't even touch it on your application side - even for simplicity reason. Besides the more you do on database side the better it is. – mr100 Jun 15 '14 at 20:19
Does the IDE provide any pre-defined constants such as `varchar`, `varbinary` etc? – Shimmy Weitzhandler Feb 25 '18 at 06:59

score 2 · Answer 2 · answered Jun 15 '14 at 10:42

I am not an expert on how these things are internally implemented but I cant tell you what I know from using them.

Regarding your first question using HasColumnType method like this:

HasColumnType("varchar(50)")

Entity Framework does not recognizes type "varchar(50)" but it recognizes type "varchar". The only proper usage that works with ef (checked in version 6) is:

.HasColumnType("varchar")
.HasMaxLength(50)

Regarding second question - DatabaseGeneratedOption. From my experience for EF there is no difference between DatabaseGeneratedOption.Computed and DatabaseGeneratedOption.None - it treats them just the same. Difference is made only for DatabaseGeneratedOption.Identity - in such case column is defined as identity column and the value passed to it during inserting data to db from application will be ignored, instead it will be assigned by database. If you define your column as DatabaseGeneratedOption.None or DatabaseGeneratedOption.Computed then you will need to provide value for the ID column as you shown:

Guid ID = new Guid.NewGuid()

Otherwise it will try always to assign there default value for GUID (all digits set to zero). If you specify your column as DatabaseGeneratedOption.Identity then you do not need to care for assigning value to your ID before saving it to db. Instead it will set by dbms to default value (which in your case is "newsequentialid()"). So right option for you is DatabaseGeneratedOption.Identity.

Thank you, that has helped clarify things. The fluent API can be a bit confusing at times. Not to mention the wording of the methods are clear, but slightly different from traditional relational database terminology! — kpk47, Jun 15 '14 at 17:12

What is the proper usage of HasColumnType and Database generated

2 Answers2

Linked