XAF - Unique Identifiers/Guid (uniqueidentifier, uuid) or sequential/integer Int32/Int64 (int, bigint, IDENTITY) type as a primary key in database tables

Hello, Jacob.

BaseObject is a default base persistent class (for both EF Core and XPO) with a Guid primary key (ID in EF Core and Oid in XPO). Our BaseObject is also used in all the system entities like PermissionPolicyUser, ReportDataV2, etc., for XAF built-in modules. Historically, XAF used integer keys by default around 2005-2006, but in 2007 we made a conscious decision to migrate from Int32 to Guid keys in XPO's BaseObject and in 2023 in EF Core's BaseObject - all due to usability issues and other complexities for customers. Since then, we have never looked back or regretted this decision - Guids are successfully used by the vast majority of XAF customers without issues.

Performance Considerations for Integer vs Guid Primary Keys

It seems that having a GUID as a Primary Key field is a bad choise, because performance in large tables is bad.

I personally saw many slow apps with integer keys and fast apps with Guids even for databases larger than 100GB. The greater storage size for Guid keys compared to integers looks like a very insignificant argument in today's world, considering the accessibility of storage/computing resources. From experience, Guid performance is all a matter of proper database design (table structure, relations, indices) and maintenance. For more information, please review my comment in T458563:

Using Guid vs Int32 PK is rather a religious or highly debated in the community question, which is not something I wanted to focus on much here, because both options are very good and are widely used in production.

For the record: in the two decades since XAF was introduced, we only had a couple cases/clients for whom Guid key fragmentation were "presumably" causing a performance issue for INSERT statements. It is still important to note that we had no access to these apps and databases to verify the relation of Guid keys to the reported behavior back then. Performance issues may occur both in XAF and non-XAF applications built from scratch, regardless of integer or Guid keys - it is impossible to accurately determine the cause of performance issues without collecting quantitative data (for example, the number of SQL queries, their duration, execution plans, the structure of database tables such as the availability of indices, etc.).

Ultimately, you should deal with this Guid migration only if you have a real problem in your production databases, and your DBA has confirmed that this issue is actually caused by GUID fragmentation. In most cases, even with default GUID generation, this is not an issue when proper database design and maintenance is performed. I also want to emphasize that changing the PK type from Guid to integer (or vice versa) is a serious design change that will be costly from all viewpoints (initial implementation and testing, further maintenance, dealing with external systems that use the same database, etc.). I just want to make sure that you have a confirmed performance issue and understand all implications (to save time and resources).

Implementation Considerations for Integer Primary Keys

our options of changing the key type to a Int with some kind of conversion of the data. Is something like this possible?

Recompile the Source Code or Create Your Own Base Class

To use a numeric primary key throughout your business classes, consider the following two options:

1. Modify the BaseObject class code and recompile XAF sources (the DevExpress.Persistent.BaseImpl.Xpo and DevExpress.Persistent.BaseImpl.EFCore libraries in particular) as per Recompile the Business Class Library and Rebuild Assemblies from the Source Code. This is a very complex and advanced task, which has multiple downsides, so we generally do not recommend it to customers.
2. Implement a custom base persistent class and use it instead of BaseObject in required business objects:

• EF Core: Follow Keys | Key types and values in the Microsoft documentation (this is a general-programming task, unrelated to DevExpress/XAF directly). If you require a string key property, then you may find the following examples helpful:
  T1179042
  How to use String property as primary key in Entity Framework.
• XPO: Follow the steps in Implement a Custom Base Persistent Class.

Support Additional XAF Features for Your Own Base Class

If you implement a custom base persistent class, take special note that with integer primary keys you will have to:

1. Guarantee key uniqueness in data records to avoid UI and other related issues in data-bound controls as per Web - How to avoid issues with data-bound controls due to missing or non-unique key values. These solutions are more complex to implement and maintain than Guids.
2. Re-implement many useful BaseObject features such as Soft Deletion, Concurrency Control, default display text, IObjectSpace, and others in your custom base class from scratch. For this, research our BaseObject source code and adapt it to your numeric keys.
3. Re-implement (optionally) the built-in XAF classes inherited from the BaseObject, if you want integer keys to be used everywhere or if you think they may also be affected by the problems you are trying to solve with this replacement in the first place (most likely, you will not need this due to the nature of scenarios with DashboardData, FileData, ReportDataV2, Event, Resource, etc.), which do not imply the creation of many records of this type.

Alter Database Schema and Migrate Existing Data Records Accordingly

To migrate your existing data with Guid PK to the Int32/Int64 PK type, create SQL scripts with the help of your DBA. For more information, please search public community resources like Stack Overflow (Migrating existing tables to use INT primary key instead of UUID mysql v8), because this task is unrelated to DevExpress/XAF directly. You can also get inspiration from our old Int32 to Guid converters for EF Core. (Note that these are not complete solutions since you will need to do the opposite conversion, but they will help you and your DBA better understand the complexity of the work involved.)

Also, you must be careful with potential collisions or overlapping if you transfer/synchronize your data with integer keys between databases in the future (see SSIS Data Migration Primary Key Identity Conflicts).