Caution
I recently needed to share a reference to a row in a table with an external system. Simplest solution is to share the primary key which most of the time is auto incremental unique value. From the outset this does not look too bad. However, sharing primary keys can be a massive security flow. If a someone gets hold of the primary key, they can "guess" what the next key might be.
Setup
Entity Framework kindly provide us a standard pattern to setup a primary key for an entity. See the following model class.
The "Id" is set to be the primary key, with the seed and increment set to 1. See the following illustration.
It is never a good idea to share technical keys outside the application boundary. The way around is to have random key that is uniquely identifies the row. We can achieve this by introducing a new "Reference" column with the type "GUID". The modified model class looks like the following.
Problem
There two ways to populate the GUID. The simplest approach is to create a new GUID (GUID.NewGuid()) just before saving the entity. This is proven to work and reliable.
Alternatively we can get the database to do the work and generate a new GUID. In order to force the database to create the GUID automatically, we need to "inform" Entity Framework our intention. The first step is the update the model class to the following. (BTW you need to reference System.ComponentModel.DataAnnotations.Schema namespace). I am using data annotation, but the same behavior can be achieved by the Entity Framework fluent API.
I find the syntax pretty strange. We are basically saying that "Reference" is the "identity" of the entity. Does this mean primary key is no longer the "identity"? (we'll find out soon).
If you regenerate the database (using Entity Framework Code First) what you see may surprise you. By the outset the table looks pretty good. (see the illustration).
As expected the "Id" is the primary key and the "Reference" is an uniqueidentifier field.The biggest surprise is the seed and the increment for the primary key. (see below.)
The seed and the increment is not set and worse "Id" column is no longer the identity. This is strange.
If we dig a just a bit deeper, we see how SQL Server generate the uniqueidentifier automatically (see below).
The "DefaultValue" is set to "NewId()" which is how SQL Server generate a GUID. When a new row is added, the "NewId()" function is invoked and it populates the "Reference" column.
Solution
We yet fix the problem. Our desire is to have an incremental primary key and a random "Reference". In order to achieve this, we need to update the model to the following.
The key point here is that we have added "DatabaseGeneratedOption.Identity" to the "Id". This adds the seed and increment to "Id". So when we add a new entity database, automatically generate a random "GUID". The GUID is safe and can be shared externally.
thanks .
ReplyDelete