.net core ef 读取mysql json字段过程记录

1、建表

CREATE TABLE `file_parsed_info` (
  `ID` char(36) COLLATE utf8mb4_general_ci NOT NULL,
  `FILE_NAME` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件名称',
  `FILE_CONTENT` json DEFAULT NULL COMMENT '解析后的内容',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='已转换的文件';

2、添加nuget引用

Pomelo.EntityFrameworkCore.MySql

3、添加连接字符串的配置

在appsetting里添加如下的节点

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "Database": "Server=127.1.1.1;User Id=sa;Password=111111;Database=dki_file_trace;"
  },
  "AllowedHosts": "*"
}

 

4、添加实体类

    public class file_parsed_info
    {
        public Guid ID { get; set; }
        public string FILE_NAME { get; set; }public string FILE_CONTENT;
        [NotMapped]
        public JObject FILE_CONTENT_JSON
        {
            get
            {
                return JsonConvert.DeserializeObject<JObject>(string.IsNullOrEmpty(FILE_CONTENT) ? "{}" : FILE_CONTENT);
            }
            set
            {
                FILE_CONTENT = value.ToString();
            }
        }
    }

5、添加Content上下文

注意下面标红加粗的那一行，不加那一条，就读不出来json对象的值

    public class MySqlDbContext : DbContext
    {
        public MySqlDbContext()
        {

        }
        public MySqlDbContext(DbContextOptions<MySqlDbContext> options) : base(options)
        {

        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {

        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<file_parsed_info>().Property<string>("FILE_CONTENT").HasField("FILE_CONTENT");
        }
        public DbSet<file_parsed_info> file_parsed_info { get; set; }
    }

6、添加startup文件

        public void ConfigureServices(IServiceCollection services)
        {
            string connectionString = Configuration.GetConnectionString("Database");
            services.AddDbContext<MySqlDbContext>(options => options.UseMySql(connectionString, ServerVersion.Parse("8.0.18"), null));
        }

7、在controller里使用

    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {
        private readonly MySqlDbContext _context;
        private readonly ILogger<WeatherForecastController> _logger;
        public WeatherForecastController(ILogger<WeatherForecastController> logger, MySqlDbContext context)
        {
            _logger = logger;
            _context = context;
        }

        [HttpGet]
        public string Get()
        {
            var model = _context.file_parsed_info.FirstOrDefault();
            var result = Newtonsoft.Json.JsonConvert.SerializeObject(model);
            return result;
        }