How to use String property as primary key in Entity Framework

Question

I'm new in EF and try to do my first steps by code first approach in ETF6.0 and now i have a problem.

I have a property

[Key]
public string FooId { get; set; }

which is my primary key for the model.

But if I run the

PM> Update-Database

command in package manager console to update pending migrations to the database i get the following error:

Identity column 'FooId' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

If I change the PK in my model to

[Key]
public int FooId { get; set; } 

everything works fine.

But I would need the PK to be of type string because it makes absolutely sens in my case. I know there are disadvantages but for me it is necessary.

I saw an older post here How to make string as primary key in entity framework!.

But it seems not to solve my problem or I just don't understand it.

Is it really that I can't use a string as PK on a SQL database?

Or is there a way to do that?

Solution 1

This is the proper way of creating a PK without Identity Autoincrement enabled:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string FooId { get; set; }

Solution 2

What is your reason for having a string as a primary key?

I would just set the primary key to an auto incrementing integer field, and put an index on the string field.

That way if you do searches on the table they should be relatively fast, and all of your joins and normal look ups will be unaffected in their speed.

You can also control the amount of the string field that gets indexed. In other words, you can say "only index the first 5 characters" if you think that will be enough. Or if your data can be relatively similar, you can index the whole field.

Solution 3

If you need your primary key to be a string, then don't make it an identity column. Identity columns will generate primary key values for you, which you should turn off if you intend to generate the values yourself.

Solution 4

Another possible answer for others not wanting to alter their entities is telling the DbContext:

  builder.Entity<Food>(b =>
  {
       b.Property(u => u.FooId).HasDefaultValueSql("newsequentialid()");
  });

This will tell the DbContext that the Food model has an Id named FoodId and it will requires an ID generation.

EF Core default values: MSDN

View more solutions

Comments

[Krzysztof Skowronek]

I assume that his reasoning was same as mine: you can use .Find(fooId) od context object and apply first level caching of entity framework I use it for username

[NightOwl888]

Be sure also NOT to inherit your table from DbSet<T>. It makes the scaffolding create extra foreign keys that you don't define on your table and also can make the migrations fail. It took me most of the day to work out why that was happening.