Using GUID With Microsoft C# .NET and Entity Framework ORM

The Microsoft C# programming language some years ago introduced a new database access paradigm called Entity Framework (EF), which can enhance the previous Active Data Objects (ADO) method. EF is an ORM (Object Relational Mapping) similar to Hibernate for Java. It lets you create object oriented classes that map directly to columns in a database table.

Entity Framework (EF) works very well with Microsoft ASP.NET Model View Controller (MVC) web applications for database access, primarily with Microsoft SQL Server databases.

Also, EF works well with LINQ (Language Integrated Query) which lets you do SQL similar queries in C# code with an C# EF class (model).

Entity Framework classes are called Models. One requirement of EF is that there will be at least one primary key in the database table, normally called ID. Most of the EF examples on the internet show an ID column, a property in the class, as an INT column in the database table that has been configured to auto-increment by 1 (identity) with each new database table record. This works well with small databases, but if you want to combine two seperate database tables into one, this could cause problems if using an INT auto increment identity primary key. There would be what is called a COLLISION because the two tables being merged would both have the same primary key number but with different data in each table.

The solution to this problem is to use a different data type for the primary key ID, one good solution is to use a GUID for the primary key. GUID stands for Globally Unique Identifier. A GUID is based on the Universally-Unique Identifier paradigm, detailed by the Internet Engineering Task Force (IETF) with RFC 4122. A GUID is a unique string of characters that is almost impossibe to be duplicated in even huge computer data sets. Merging two database tables that use GUID primary keys is relatively simple and does not have COLLISIONS that can occur when using auto-increment INT primary keys.

With Microsoft ASP.NET web applications using Entity Framework and a SQL Server database. There is two methods you can use to generate a GUID.

1) One method is to generate the GUID primary key using C# software, and then after its creation insert it into the database with a new row of data.

2) The other method is to configure SQL Server database to auto-generate a new GUID with each new table record.

In my Curricum Vitae ASP.NET MVC Web Application with C#, viewable at http://www.michaelgworkman.com, it is using Entity Framework for the data access. And I configured the SQL Server database to auto generate the GUID with each new record. This can be done manually with clicks of the mouse in SQL Server Management Studio (SSMS), or a better method is to do the entire configuration using a TSQL Script.

The following TSQL script is what I used to generate a SQL Server database table to store contact category information from the web application, and it uses an auto generated GUID for the primary key, in this table the primary key is the ID column. This data is used for a drop down on the contact page of my CV MVC website.

USE [CVDB]
GO

CREATE TABLE [CVDB].[dbo].[ContactCategories] (
 [ID] uniqueidentifier NOT NULL,
 [category] NVARCHAR(100) NOT NULL,
 [sortorder] INT NOT NULL,
 CONSTRAINT [PK_contactcategories] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [CVDB].[dbo].[ContactCategories] ADD CONSTRAINT [DF_contactcategories_ID] DEFAULT (newid()) FOR [ID]
GO

Note that there are three blocks of TSQL code in this script. The first one tells the database server what database is being used, in this case CVDB. The second one creates the table with an ID as primary key, in SQL Server database the column type for a GUID primary key is UNIQUEIDENTIFIER. Also note the third line of TSQL code starting with ALTER TABLE, this is the statement that configures the ID column to be auto-generated with a new GUID for each new table record in the database.

Next we work on the Entity Framework (EF) C# class that is the model for ContactCategories database table. This is the C# code for our EF model:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CVitae.Models
{
	[Table("ContactCategories", Schema = "dbo")]
	public class ContactCategory
	{
    		[Key]
		[Required]
		[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
		public Guid ID { get; set; }

		[Required(AllowEmptyStrings = false), MaxLength(100)]
		[Display(Name = "Category")]
		public string category { get; set; }

		[Required]
		[Display(Name = "Sort Order")]
		public int sortorder { get; set; }
	}
}

Note the various data annotations in this EF model. The TABLE annotation is used to explicitly link the C# EF model class to a specific database table, you can do this with this tag, or without the tag EF defaults to the DB table being the same name as the C# class. Also note the line for the primary key:

public Guid ID { get; set; }

GUID is a native type in the C# programming language, and if a GUID is being used in the database table, then its corresponding property in the C# EF model class should be type GUID.

Also note the other data annotations for the ID property. [Key] designates that C# model class property is a primary key or foreign key in the database.

Also very important, when a GUID is being auto-generated by the database, the corresponding ID property in the C# EF model must have the following data annotation for it to work correctly.

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

Other data annotations being used here include the DISPLAY tag, which lets you designate the name that will be displayed in a Razor view by default when the class object properties are referenced in the Razor view code in an ASP.NET MVC web application.

Once the database table is created and configured to use an auto uniqueidentifier GUID, and after inserting data into the table, this is what the records in the database table will look like. This data appears in a drop down list in my CV MVC web application on the CONTACT page. Note the ID column which is a uniqueidentifier GUID.

In summary, primary key DB columns that use an auto-increment INT for the column type, those are good for small databases with no plans to merge database tables with other tables. However, merging two or more DB tables together with an auto-increment INT primary key can result in COLLISION errors during the merge. One good solution to this issue is to use a uniqueidentifier GUID for the primary key in the database table, and also a GUID type in the Entity Framework class model.

To see my Curriculum Vitae, go to Michael G Workman CV

To see my projects on Azure DevOps, go to https://dev.azure.com/AbionTechnology/

To see my Posts and Answers on Stack Overflow, go to Michael G. Workman on Stack Overflow

 If you have any questions about C, C++, Microsoft C# .NET,  Microsoft Azure Cloud, Unix, Linux, and/or Mobile Apps, please feel free to contact me by email at:

michael.g.workman@outlook.com