Improving the Class-to-Table Mapping
Learn about configuring entity data models for different database providers, including changing entity models for SQLite databases.
The dotnet-ef the command-line tool generates different codes for SQL Server and SQLite because they support different levels of functionality.
SQL Server and SQLite generated code
For example, SQL Server text columns can limit the number of characters. SQLite does not support this. So, dotnet-ef will generate validation attributes to ensure string properties are limited to a specified number of characters for SQL Server but not for SQLite, as shown in the following code:
// SQLite database provider-generated code[Column(TypeName = "nvarchar (15)")]public string CategoryName { get; set; } = null!;// SQL Server database provider-generated code[StringLength(15)]public string CategoryName { get; set; } = null!;
Neither database provider will mark non-nullable string properties as required:
// no runtime validation of non-nullable propertypublic string CategoryName { get; set; } = null!;// nullable propertypublic string? Description { get; set; }// decorate with attribute to perform runtime validation[Required]public string CategoryName { get; set; } = null!;
Improving entity model mapping and validation for SQLite
We will make some small changes to improve the entity model mapping and validation rules for SQLite. First, we will add a regular expression to validate that a CustomerId value is exactly five uppercase letters. Second, we will add string length requirements to validate that multiple properties throughout the entity models know the maximum length allowed for their text values:
Step 1: In Customer.cs, add a regular expression to validate its primary key value to only allow uppercase Western characters, as shown highlighted in the following code:
[Key][Column(TypeName = "nchar (5)")][RegularExpression("[A-Z]{5}")]public string CustomerId { get; set; } = null!;
Step 2: Activate the code editor’s find and replace feature (in Visual Studio 2022, navigate to “Edit |Find” and “Replace | Quick Replace”), ...