Business scenario
- The data between the two databases needs to be migrated, and then the table structure is different, and the migration process also involves the processing of lost data. Therefore, the function of import and column mapping can not be simply used to complete the task
- The nuget package of. net core happens to have a driver corresponding to the database. In addition, at the beginning, I didn't want to write entity classes and expected to use DBFirst mode to generate code (in EF Core, it doesn't seem to be called DB First anymore)
- Later, the code generation from the database table failed... Therefore, the handwritten entity class is selected to map the database table
Technical realization
Void code
- Import the required nuget package
- Create entity class
- Create the context of EF and manually fill in the dataset attribute DBSet corresponding to the entity class
- The console project starts, creates different database contexts, and starts processing data
Actual development
-
The packages that need to be introduced are required not only for the project where the EF Core is located, but also for the startup project of the solution
-
When creating an entity class, note that the corresponding features need to be added (Table, Key, Columns, MaxLength)
-
using System; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; [Table("T_CARGO")] public class T_CARGO { // ID character varying(36) NOT NULL, [Key] [Column("ID")] public string ID { get; set; } // CARGO_ID character varying(255) , [Column("CARGO_ID")] public string CARGO_ID { get; set; } // CARGO_NAME character varying(255) , [Column("CARGO_NAME")] [MaxLength(255)] public string CARGO_NAME { get; set; } }
-
-
Define the context of EF and associate the table with the database
-
public class KJZMMSContext : DbContext { public KJZMMSContext(DbContextOptions<KJZMMSContext> options) : base(options) { Console.WriteLine("Yes"); } public DbSet<T_CARGO> T_CARGOSet { get; set; } }
-
-
Starting from the console project, create a database context
-
The database connection string can be read from the configuration file
-
You may not use dependency injection as most blogs on the Internet. That's just right. I created it manually here
-
DbContextOptionsBuilder<KJZMMSContext> builder = new DbContextOptionsBuilder<MMSContext>().UseOscar("Server=*.*.*.*;Port=2003;Database=OSRDB;Encoding=UTF-8;User Id=**;Password=**", action => { action.UseDefaultSchema("****"); action.UseCaseSensitive(); }); // Create your own EF context mmsContext = new MMSContext(builder.Options);
-
Summary items
- MaxLength is a feature that I ignored at first, so when inserting data, errors are always reported, and the error information is very unclear and maddening. The error information is as follows
- String overflow, value too long for type character varying(255)
- I really vomited. This error caused me to constantly modify the length of the database field. I thought the length of the inserted string was too long.
- There are two ways to define the relationship between entity classes and tables. One is the fluent api and the other is the feature. I'm still not used to the usual api. I don't want to write it like that unless it is generated by the framework.
- The console is only a startup mode. When creating EF context, it is still implemented by using static code blocks in the tool class
Appendix code
- EFHelper class used
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
namespace KZDBMS.EFCoreModule.DataHelper
{
public class EFHelper
{
public DbContext DatabaseContext { get; set; }
public EFHelper(DbContext dbContext)
{
this.DatabaseContext = dbContext;
}
//protected DbContext DatabaseContext
//{
// get
// {/ / in order to ensure the uniqueness of threads, thread slots are used for storage
// // DbContext dbContext = CallContext.GetData("dbContext") as DbContext;
// if (dbContext == null)
// {
// //If it is empty, a new context is created and put into the thread slot
// //dbContext = new MPMS20190522Entities();
// dbContext = new AHDCEntities();
// CallContext.SetData("dbContext", dbContext);
// }
// return dbContext;
// }
//}
/// <summary>
///Query filtering
/// </summary>
/// <typeparam name="C"></typeparam>
/// <param name="whereLambda"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
public IQueryable<T> GetAllEntities<T>(System.Linq.Expressions.Expression<Func<T, bool>> whereLambda) where T : class, new()
{
return DatabaseContext.Set<T>().Where(whereLambda);
}
/// <summary>
///Paging query
/// </summary>
/// <returns></returns>
public IQueryable<T> SelectByPage<T, C>(System.Linq.Expressions.Expression<Func<T, bool>> whereLambda, System.Linq.Expressions.Expression<Func<T, C>> orderBy, int pageIndex, int pageSize, bool isAsc, out int totalCount) where T : class, new()
{
var temp = DatabaseContext.Set<T>().Where<T>(whereLambda);
totalCount = temp.Count();
if (isAsc)
{
//In ascending order, the generic type is followed when it is obtained
temp = temp.OrderBy<T, C>(orderBy).Skip<T>((pageIndex - 1) * pageSize).Take<T>(pageSize);
}
else
{
//In descending order, it is obtained without following the generics
temp = temp.OrderByDescending<T, C>(orderBy).Skip((pageIndex - 1) * pageSize).Take(pageSize);
}
return temp;
}
/// <summary>
///Add data
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public T Add<T>(T entity) where T : class, new()
{
//DbContext.Entry<T>(entity).State = EntityState.Added;
//return DbContext.SaveChanges() > 0;
DatabaseContext.Set<T>().Add(entity);
//DbContext.SaveChanges(); // Note out the save operation here and change to unified save
return entity;
}
/// <summary>
///Delete data
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Delete<T>(T entity) where T : class, new()
{
//DbContext.Entry<T>(entity).State = EntityState.Deleted;
DatabaseContext.Set<T>().Remove(entity);
//return DbContext. SaveChanges() > 0; // Note out the save operation here and change to unified save
return true;
}
/// <summary>
///Update data
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public bool Update<T>(T entity) where T : class, new()
{
DatabaseContext.Entry<T>(entity).State = EntityState.Modified;
//return DbContext. SaveChanges() > 0; // Note out the save operation here and change to unified save
return true;
}
/// <summary>
///Save your changes
/// </summary>
/// <returns></returns>
public bool SaveChanges()
{
return DatabaseContext.SaveChanges() > 0;
}
}
}
-
A piece of code to remove string sensitive words by reflection
public static void ReplaceSensitiveKey(Object obj) { // TODO desensitization operation var properties = obj.GetType().GetProperties(); foreach (var property in properties) { if (property.PropertyType == typeof(string)) { // As long as it is of string type, desensitization operation shall be carried out string propValue = (string) property.GetValue(obj); propValue = propValue == null ? null : propValue.Replace("Sensitive words", "Harmonious words").; property.SetValue(obj, propValue); } Console.WriteLine(property.Name); } }