The first thing that comes to mind for auto-generating field values is primary key columns (primary keys with IDENTITY).EF Core's default primary key configuration also enables Identity self-growth and automatically identifies the primary key. The default primary key configuration in EF Core also enables Identity self-growth and automatically identifies the primary key:
1. The name is ID, id, or Id, which is case insensitive;
2. The name consists of the entity class name + Id. For example, the Car entity class contains a property called CarID or CarId;
3. The attribute type is an integer type (int, long, ushort, etc., but not byte) or GUID.
These rules for identifying primary keys are implemented by something called "Convension" (Convension), specifically, a class called KeyDiscoveryConvention. I'll show you a bit of the source code.
public class KeyDiscoveryConvention : IEntityTypeAddedConvention, IPropertyAddedConvention, IKeyRemovedConvention, IEntityTypeBaseTypeChangedConvention, IEntityTypeMemberIgnoredConvention, IForeignKeyAddedConvention, IForeignKeyRemovedConvention, IForeignKeyPropertiesChangedConvention, IForeignKeyUniquenessChangedConvention, IForeignKeyOwnershipChangedConvention, ISkipNavigationForeignKeyChangedConvention { private const string KeySuffix = "Id"; …… public static IEnumerable<IConventionProperty> DiscoverKeyProperties( IConventionEntityType entityType, IEnumerable<IConventionProperty> candidateProperties) { (entityType, nameof(entityType)); // ReSharper disable PossibleMultipleEnumeration var keyProperties = (p => string.Equals(, KeySuffix, )); if (!()) { var entityTypeName = (); keyProperties = ( p => == + && (entityTypeName, ) && (KeySuffix, )); } return keyProperties; // ReSharper restore PossibleMultipleEnumeration } …… }
These logical And actually look for attribute names in <ClassName>Id format, such as StudentID, CarId, OrderID ...... The principle of foreign key discovery is also the same as that of primary keys.
A simple example with Sqlite data. Here is the entity class (assuming it is used to represent input method information):
public class InputMethod { public ushort RecoId { get; set; } public string? MethodDisplay { get; set; } public string? Description { get; set; } public string? Culture { get; set; } }
As you can see, the attribute of this class that is used as the primary key is RecoId. However, it is named in such a way that it can't be recognized automatically, and we have to explicitly tell the EF that it is the primary key. There are two ways to do this:
1. Annotation method. Apply the relevant characteristic class directly on the attribute. For example
public class InputMethod { [Key] public ushort RecoId { get; set; } …… }
2. Override the OnModelCreating method of the DbContext class. For example
protected override void OnModelCreating(ModelBuilder modelBuilder) { <InputMethod>().HasKey(e => ); }
If you use the above rewrite of the OnModelCreating method, then your DbContext-derived class already recognizes the InputMethod entity class. However, if you are using the [Key] feature on properties, then the DbContext derived class will not recognize the entity class, and you will need to declare its collection as a property of DbContext.
internal class TestDBContext : DbContext { // constructor public TestDBContext(DbContextOptions<TestDBContext> opt) : base(opt) { } // Declaring a collection of entities as an attribute public DbSet<InputMethod> InputMethods { get; set; } }
Note that the collection of data records to use DbSet<>, other types of collections are not available yo. For example, if you change it to this, it will report an error.
public List<InputMethod> InputMethods { get; set; }
Explain that people only recognize DbSet collections, other collections are invalid.
Here Lao Zhou chose the service container to configure.
static void Main(string[] args) { IServiceCollection services = new ServiceCollection(); // Constructing a Connection String SqliteConnectionStringBuilder constrbd = new(); = ""; // Adding Sqlite Functionality <TestDBContext>( connectionString: (), optionsAction: dcopt => { (msg => (msg), ); } ); // Generate a list of services var svcProd = (); if(svcProd == null) { return; } // Accessing the data context using TestDBContext dbc = <TestDBContext>(); …… }
You can write the connection string directly as a string without using ConnectionStringBuilder.The default SQLite library doesn't support passwords, so I won't set a password. In the call AddSqlite method, there is a parameter named optionsAction, we can use it to configure the log output. LogTo method configuration is simple, as long as you provide a delegate, it binds the method as long as there is a string type of input parameter on the line, this string parameter is the log text.
After you configure the logging feature, when you run the program, the console can see the executed SQL statements.
Let's create the database and insert two InputMethod records.
// Accessing the data context using TestDBContext dbc = <TestDBContext>(); // Delete Database (); // Creating a database (); // Try to insert two records InputMethod[] ents = [ new(){MethodDisplay = "double spelling input", Description="Press two keys to complete a syllable",Culture="zh-CN"}, new() {MethodDisplay = "Six-finger input", Description="Designed for people with six fingers.",Culture="zh-CN"} ]; <InputMethod>().AddRange(ents); int result = (); ($"Number of records updated: {result}"); // Print the inserted record foreach(InputMethod im in <InputMethod>()) { ($"ID={}, Display={}, Culture={}"); }
The EnsureDeleted method is called for testing purposes and is generally not called in real-world applications. Because the function of this method is to delete the existing database, if you call this method, the application will delete the database every time it starts, and the users will complain. If you call this method, the application will delete the database every time you start, then the user will definitely complain about you.EnsureCreated method can be used, its function is to create a new database if the database does not exist; if the database exists, it does not do anything. Therefore, calling the EnsureCreated method will not cause data loss, feel free to use.
Insert data and call the SaveChanges method to save the code to the database, I believe that everyone is very familiar with the old week will not be introduced.
After the program is run, you will get a log like this:
info: 2024/8/4 12:48:11.517 [20101] () Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] PRAGMA journal_mode = 'wal'; info: 2024/8/4 12:48:11.582 [20101] () Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE "tb_ims" ( "RecoId" INTEGER NOT NULL CONSTRAINT "PK_tb_ims""MethodDisplay""Description""Culture" TEXT NULL ); info: 2024/8/4 12:48:11.700 [20101] () Executed DbCommand (3ms) [Parameters=[@p0='?' (Size = 5), @p1='?' (Size = 10), @p2='?' (Size = 4)], CommandType='Text', CommandTimeout='30'] INSERT INTO "tb_ims" ("Culture", "Description", "MethodDisplay") VALUES (@p0, @p1, @p2) RETURNING "RecoId"; info: 2024/8/4 12:48:11.712 [20101] () Executed DbCommand (0ms) [Parameters=[@p0='?' (Size = 5), @p1='?' (Size = 10), @p2='?' (Size = 4)], CommandType='Text', CommandTimeout='30'] INSERT INTO "tb_ims" ("Culture", "Description", "MethodDisplay") VALUES (@p0, @p1, @p2) RETURNING "RecoId"; Update the number of records:2 info: 2024/8/4 12:48:11.849 [20101] () Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT "t"."RecoId", "t"."Culture", "t"."Description", "t"."MethodDisplay" FROM "tb_ims" AS "t" ID=1, Display=double spelling input, Culture=zh-CN ID=2, Display=Six-finger input, Culture=zh-CN
This way you will see that incremental IDs are automatically generated by default for primary keys of integer type. Note that this is generated by the database, not by EF Core's generator. The SQL statement will vary from database to database.
For comparison, let's change to SQL Server and see the output logs.
// Constructing a Connection String SqlConnectionStringBuilder constrbd = new(); = ".\\SQLTEST"; = "CrazyDB"; = true; // Sometimes you can't connect if you don't trust the server certificate = true; // readable and writable = ; // Adding SQL Server Features <TestDBContext>( connectionString: (), optionsAction: opt => { (logmsg => (logmsg), ); });
The rest of the code remains unchanged and is run again. The output log is as follows:
info: 2024/8/4 13:01:06.087 [20101] () Executed DbCommand (115ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] CREATE DATABASE [CrazyDB]; info: 2024/8/4 13:01:06.122 [20101] () Executed DbCommand (31ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN ALTER DATABASE [CrazyDB] SET READ_COMMITTED_SNAPSHOT ON; END; info: 2024/8/4 13:01:06.137 [20101] () Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT 1 info: 2024/8/4 13:01:06.181 [20101] () Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [tb_ims] ( [RecoId] int NOT NULL IDENTITY, [MethodDisplay] nvarchar(12) NOT NULL, [Description] nvarchar(max) NULL, [Culture] nvarchar(max) NULL, CONSTRAINT [PK_tb_ims] PRIMARY KEY ([RecoId]) ); info: 2024/8/4 13:01:06.317 [20101] () Executed DbCommand (30ms) [Parameters=[@p0='?' (Size = 4000), @p1='?' (Size = 4000), @p2='?' (Size = 12), @p3='?' (Size = 4000), @p4='?' (Size = 4000), @p5='?' (Size = 12)], CommandType='Text', CommandTimeout='30'] SET IMPLICIT_TRANSACTIONS OFF; SET NOCOUNT ON; MERGE [tb_ims] USING ( VALUES (@p0, @p1, @p2, 0), (@p3, @p4, @p5, 1)) AS i ([Culture], [Description], [MethodDisplay], _Position) ON 1=0 WHEN NOT MATCHED THEN INSERT ([Culture], [Description], [MethodDisplay]) VALUES (i.[Culture], i.[Description], i.[MethodDisplay]) OUTPUT INSERTED.[RecoId], i._Position; Number of records updated：2 info: 2024/8/4 13:01:06.438 [20101] () Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [t].[RecoId], [t].[Culture], [t].[Description], [t].[MethodDisplay] FROM [tb_ims] AS [t] ID=1, Display=double spelling input, Culture=zh-CN ID=2, Display=Six-finger input, Culture=zh-CN
A. When using a Sqlite database, the CREATE TABLE statement is generated with a PRIMARY KEY AUTOINCREMENT column;
B. When using SQL Server, the primary key column uses IDENTITY, which by default is seeded with 1 and incremented with 1. So the key values for the inserted records are 1 and 2.
Sometimes we don't want the primary key column to generate values automatically, again there are two ways to configure this:
1, through the characteristic class to annotate. Such as
public class InputMethod { [Key, DatabaseGenerated()] public ushort RecoId { get; set; } public string? MethodDisplay { get; set; } public string? Description { get; set; } public string? Culture { get; set; } }
Set DatabaseGeneratedOption to None to cancel the automatic generation of columns.
2, through the model configuration, that is, rewrite the OnModelCreating method to achieve.
protected override void OnModelCreating(ModelBuilder modelBuilder) { <InputMethod>().HasKey(e => ); <InputMethod>() .Property(k => ) .ValueGeneratedNever(); }
In this case, we need to manually assign values to the primary key columns when inserting data.
======================================================================================
The above is a warm-up exercise, which is a relatively simple application program. The following old week to big partners to solve a problem. I've seen people ask questions on GitHub and other platforms, but they don't get solved. If you see this piece of water and you have this confusion, then you are in luck. Okay, let's look at the problem without further ado.
Demand:The primary key remains the same, but I don't want it to have IDENTITY, and the value of the primary key is generated in my customized way when inserting the recordThe essence of this need is: I don't want the database to generate the incremental ID for me. The essence of this need is: I don't want the database to generate incremental IDs for me, I want to generate them in the program.
As I mentioned earlier, the default behavior is to generate self-growing columns if the primary key column is an integer type or GUID. So, we have a very critical step - that is how to disable EF to generate IDENTITY columns. If you have seen the source code of EF Core SQL Server, you may know that there is a convention class called SqlServerValueGenerationStrategyConvention, which by default sets the autogeneration strategy for primary key columns to IdentityColumn.
public virtual void ProcessModelInitialized( IConventionModelBuilder modelBuilder, IConventionContext<IConventionModelBuilder> context) => ();
So, some big partners may think, then I derive a class from SqlServerValueGenerationStrategyConvention, override the ProcessModelInitialized method, change the auto-generation strategy to None, and then replace it in the convention collection with the SqlServerValueGenerationStrategyConvention in the convention collection.
It's not that this idea doesn't work, it's just a bit more work. Not only do you have to define a new class, but you also have to register it with the service container to replace the SqlServerValueGenerationStrategyConvention. After all, the EF Core framework uses service containers and dependency injection to organize components internally. This is done by passing a DbContextOptions<TContext> object when initializing the DbContext class (including your derived classes), which has a ReplaceService method that replaces the service in the container. This is configured when you call the AddSqlServer method.
public static IServiceCollection AddSqlServer<TContext>( this IServiceCollection serviceCollection, string? connectionString, Action<SqlServerDbContextOptionsBuilder>? sqlServerOptionsAction = null, Action<DbContextOptionsBuilder>? optionsAction = null) where TContext : DbContext
The above solution is too cumbersome, so Lao Zhou did not use it. In fact, even if the generation policy is Identity when the service is initialized, we can modify it when we build the model. The way to do this is to override the OnModelCreating method of the DbContext class, and then we can modify the generation policy through the method. Of course, there is a bit of a twist here, we can't call it on the ModelBuilder instance, because it doesn't directly implement the IConventionModelBuilder interface:
public class ModelBuilder : IInfrastructure<IConventionModelBuilder>
IInfrastructure<T> interface is to hide T, do not want the program code to access the type T. The DbContext class also implements this interface, but it hides the IServiceProvider object, do not want us to access the services registered in it. In other words, the implementer of IConventionModelBuilder is hidden. However, EF Core doesn't take things too far, but at least gives an extension method GetInfrastructure, with which we can get a reference to the IConventionModelBuilder type.
Figuring out this principle makes the code easier to write.
protected override void OnModelCreating(ModelBuilder modelBuilder) { IConventionModelBuilder cvbd = modelBuilder.GetInfrastructure(); if (()) { cvbd.HasValueGenerationStrategy(); } …… }
If you change the generation policy to None, the primary key columns are generated without IDENTITY.
If you're happy, you can manually assign values to the primary key columns when inserting records as well. However, to be able to generate the values automatically, we should write our own generation class.
public class MyValueGenerator : ValueGenerator<int> { // Returning false means that the generated value is not temporary, and that it will eventually be stored in the database's public override bool GeneratesTemporaryValues => false; private static readonly Random rand = new((int)); public override int Next(EntityEntry entry) { // Get all entities DbSet<InputMethod> ents = <InputMethod>(); int newID = default; do { // Generate Random ID newID = (); } // Guaranteed non-repetition while ((x => == newID)); // Returns the new value return newID; } }
My logic here is like this, the value is randomly generated, but a loop is used to check if the value already exists in the database, and if it does, it continues to be generated until the value is not duplicated.
To implement a custom generator, there are two abstract classes available:
1, if you generate a value, the type is not certain (may be int, may be long, may be ......), then implement the ValueGenerator class;
2、If the value to be generated is of explicit type, such as int here, then implement the ValueGenerator<TValue> class with generic parameters.
These two classes have an inheritance relationship, ValueGenerator<TValue> derives from the ValueGenerator class. Abstract members that need to be implemented:
A. GeneratesTemporaryValues property: read-only, return bool value. If you generate the value is temporary, return true, not temporary, return false. what does it mean. Temporary values that are temporarily assigned to the attribute/field, but INSERT, UPDATE, the value will not be stored in the database; if the value is not temporary, it will eventually be stored in the database. In the above example, Lao Zhou told it to return false, which means that the value generated will be written to the database.
B. If you inherit ValueGenerator class, please implement NextValue abstract method, the return type is object, which is the generated value; if you inherit ValueGenerator<TValue>, please implement Next method, the return type of this method is determined by the generic parameter. In the above example, it is int.
After writing the generated class, to apply it to the entity model, again override the OnModelCreating method of the DbContext class.
protected override void OnModelCreating(ModelBuilder modelBuilder) { IConventionModelBuilder cvbd = (); if (()) { (); } <InputMethod>().HasKey(e => ); <InputMethod>() .Property(k => ) .HasValueGenerator<MyValueGenerator>() .ValueGeneratedOnAdd(); <InputMethod>().ToTable("tb_ims") .Property(x => ) .IsRequired() .HasMaxLength(12); }
The ValueGeneratedOnAdd method indicates that the value is automatically generated when the record is inserted into the database, and the HasValueGenerator method sets your customized generator.
Now, with a custom generator rule, the primary key cannot be assigned a value when inserting data. Once the value is assigned, the generator is invalidated.
// Try to insert two records InputMethod[] ents = [ new(){ MethodDisplay = "double spelling input", Description="Press two keys to complete a syllable",Culture="zh-CN"}, new() { MethodDisplay = "Six-finger input", Description="Designed for people with six fingers.",Culture="zh-CN"} ]; <InputMethod>().AddRange(ents); int result = ();
Run the application, and you'll notice that the RecoId column no longer has the IDENTITY keyword in the CREATE TABLE statement generated this time.
info: 2024/8/4 18:41:24.956 [20101] () Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT 1 info: 2024/8/4 18:41:24.982 [20101] () Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN ALTER DATABASE [CrazyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; END; info: 2024/8/4 18:41:25.003 [20101] () Executed DbCommand (21ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] DROP DATABASE [CrazyDB]; info: 2024/8/4 18:41:25.104 [20101] () Executed DbCommand (82ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] CREATE DATABASE [CrazyDB]; info: 2024/8/4 18:41:25.137 [20101] () Executed DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='60'] IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN ALTER DATABASE [CrazyDB] SET READ_COMMITTED_SNAPSHOT ON; END; info: 2024/8/4 18:41:25.142 [20101] () Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT 1 info: 2024/8/4 18:41:25.194 [20101] () Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [tb_ims] ( [RecoId] int NOT NULL, [MethodDisplay] nvarchar(12) NOT NULL, [Description] nvarchar(max) NULL, [Culture] nvarchar(max) NULL, CONSTRAINT [PK_tb_ims] PRIMARY KEY ([RecoId]) ); info: 2024/8/4 18:41:25.408 [20101] () Executed DbCommand (24ms) [Parameters=[@__newID_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT CASE WHEN EXISTS ( SELECT 1 FROM [tb_ims] AS [t] WHERE [t].[RecoId] = @__newID_0) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END info: 2024/8/4 18:41:25.448 [20101] () Executed DbCommand (1ms) [Parameters=[@__newID_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT CASE WHEN EXISTS ( SELECT 1 FROM [tb_ims] AS [t] WHERE [t].[RecoId] = @__newID_0) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END info: 2024/8/4 18:41:25.488 [20101] () Executed DbCommand (2ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 4000), @p2='?' (Size = 4000), @p3='?' (Size = 12), @p4='?' (DbType = Int32), @p5='?' (Size = 4000), @p6='?' (Size = 4000), @p7='?' (Size = 12)], CommandType='Text', CommandTimeout='30'] SET IMPLICIT_TRANSACTIONS OFF. SET NOCOUNT ON. INSERT INTO [tb_ims] ([RecoId], [Culture], [Description], [MethodDisplay]) VALUES (@p0, @p1, @p2, @p3), (@p4, @p5) (@p4, @p5, @p6, @p7). Update the number of records:2 info: 2024/8/4 18:41:25.524 [20101] () Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT [t].[RecoId], [t].[Culture], [t].[Description], [t].[MethodDisplay] FROM [tb_ims] AS [t] ID=427211935, Display=Six-finger input, Culture=zh-CN ID=1993200136, Display=double spelling input, Culture=zh-CN
How about this, isn't this a very high-end way to play? Of course, if the primary key is a string type, you can also generate a string value, everything depends on your needs, anyway, the principle is the same.
Finally, let's talk about how to automatically change the date and time. This is also very common in the actual development, such as a schedule, its entity is as follows:
public class Plan { /// <summary> /// Program ID /// </summary> public int ID { get; set; } /// <summary> /// Brief description of the program /// </summary> public string? PlanDesc { get; set; } /// <summary> /// Program level /// </summary> public int Level { get; set; } /// <summary> /// Program Creation Time /// </summary> public DateTime? CreateTime { get; set; } /// <summary> /// total planned volume /// </summary> public float TotalTask { get; set; } /// <summary> /// completion rate /// </summary> public float Completed { get; set; } /// <summary> /// update time /// </summary> public DateTime? UpdateTime { get; set; } }
The last field UpdateTime indicates the time of update after insertion, so this field can be left NULL during insertion, for example, if I change the number of scheduled completions Completed, the current time is automatically assigned to the UpdateTime field when it is written to the database. This can not be done with the value generator, because the generator can only be inserted into the data before or after the insertion of the value generated once, after the update of the data will not generate a new value, it can not be done automatically set the update time. So, here we can think differently: rewrite the SaveChanges method of the DbContext class to find out which records have been modified before the command is sent to the database, and then set the UpdateTime property before sending the SQL statement. This can also achieve the function of automatically recording the update time.
public class MyDBContext : DbContext { …… public override int SaveChanges(bool acceptAllChangesOnSuccess) { var modifieds = from c in () where == && is Plan select c; foreach(var obj in modifieds) { (nameof()).CurrentValue = ; } return base.SaveChanges(acceptAllChangesOnSuccess); } }
Modified represents the state of the entity that has been changed. When you modify the value of a property, you should assign it to CurrentValue, which represents the current value of the entity, and do not change the value of OriginalValue, which refers to the value read from the database, and most of the time you don't need to change it, unless you want to copy the data from the current DbContext instance to another DbContext instance.
This way when the Plan object is modified, the update time will be set automatically before committing. Here is the test code:
// Creating a Context using var ctx = new MyDBContext(); // For testing, make sure to delete the database (); // Determine the creation of the database (); // Create three records Plan p01 = new() { PlanDesc = "Assembly of batteries", CreateTime = , TotalTask = 100f, Completed = 0f, }; Plan p02 = new Plan() { PlanDesc = "Replacement of base plate", CreateTime = , Level = 4, TotalTask = 12.0f, Completed = 0f }; Plan p03 = new() { PlanDesc = "Cleaning cover", TotalTask = 20.5f, Completed = 0f, CreateTime = }; (p01); (p02); (p03); // Update to database int n = (); ($"{n} records have been inserted"); // Print data Print(); MODIFY: // It's a label. ("Enter the record ID to be updated:"); string? line = (); if(line == null) { ("Did you enter it?"); goto MODIFY; // Go back to the label. } if(!int.TryParse(line, out int id)) { ("Did you damn well enter an integer?"); goto MODIFY; // Go back to the label. } UPDATE: // tab (of a window) (computing) ("Please enter the number of planned completions:"); line = (); if (line == null) { ("Are you sure you're playing the right keyboard?"); goto UPDATE; } if(!float.TryParse(line, out float comp)) { ("Floating point number. Floating point number. Floating point number."); goto UPDATE; } // find Plan? curPlan = (x => == id); if (curPlan == null) { ("Can't find the record."); goto MODIFY; } if(comp > ) { ("Do you work in an alien dimension?"); goto UPDATE; } // update = comp; (); // Print again Print();
Insert three pieces of data, then enter the record ID to change the Completed value. You will see the update time after the change.
Well, that's all the water we have today.