TAGS :Viewed: 5 - Published at: a few seconds ago

[ Best practice for Ids Entity framework code first ]

So I stumbled upon this article earlier today https://blogs.msdn.microsoft.com/azuremobile/2014/05/22/tables-with-integer-keys-and-the-net-backend/

In the article, the author makes a comment that got my attention. He said

Note: Typically, when starting from an Entity Framework Code-First model in your .NET Backend, you would use string ids

From what I've read, using string Ids can be a performance issue in as your table grows. So I would just like to know if this was just the authors opinion or it is a standard. If it is the later, I would like to know the reasons behind this.

Answer 1

IMHO identity field should be numeric for performance reasons matching int is way much faster than matching string and numeric field saves a lot of space than string.

Answer 2

Technically yes, you can use the string as primary key, but if a string makes sense to be the primary key then you should probably use it. You have to take in your account some consideration.

  • Digtis comparison is faster then string comparison

  • Longer string mean harder to compare

When you must use a string as primary key then set the length e.g. MaxLength = 20 = nvarchar(20)

  public class User
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None), MaxLength(20)]
    public string UserId { get; set; }

This will help you to avoid some performance issues. You can also change the generated key from nvarchar to varchar by using dbcontext.executesqlcommand this will give you more space (One charachter will use only one byte and not 2).

Alternatively, you can with code first change the column data type as following:

[Key, DatabaseGenerated(DatabaseGeneratedOption.None), Column(TypeName = "varchar"), MaxLength(20)]
public string UserId { get; set; }