如何使用 Pomelo.EntityFramework 的 JsonObject

How to use JsonObject of Pomelo.EntityFramework(如何使用 Pomelo.EntityFramework 的 JsonObject)

问题描述

我想将字符串列表作为 json 存储到 mysql 表中.我看到 pomelo entityframework 对此提供了支持.我跟着这个 https://libraries.io/github/tuanbs/Pomelo.EntityFrameworkCore.MySql

I want to store list of string into mysql table as json. I saw there is support for this in pomelo entityframework. I followed this https://libraries.io/github/tuanbs/Pomelo.EntityFrameworkCore.MySql

这是我的实体

public class Project
{
   public int Id {get;set;}

   public string Title {get;set;}

   public JsonObject<List<string>> Tags {get;set;}
}

但是当 _context.Database.EnsureDeleted(); 被调用时，它给出以下错误

But when _context.Database.EnsureDeleted(); is called it gives below error

实体类型项目"上的导航属性标签"不是虚拟的.UseLazyLoadingProxies 要求所有实体类型都是公开的，未密封，具有虚拟导航属性，并具有公共或受保护的构造函数.

Navigation property 'Tags' on entity type 'Project' is not virtual. UseLazyLoadingProxies requires all entity types to be public, unsealed, have virtual navigation properties, and have a public or protected constructor.

但它不是导航属性，我必须添加虚拟关键字，而是一列.不知道我在这里错过了什么.

But it is not navigation property that I have to add virtual keyword with it but is a column. Don't know what am I missing here.

推荐答案

看下面的示例代码，它取自 我在我们的 GitHub 存储库上的帖子，并且没有问题:

Take a look at the following sample code, that is taken from my post on our GitHub repository, and works without issues:

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Storage;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
        public JsonObject<Energy> Energy { get; set; }
        public JsonObject<List<string>> Comments { get; set; }
    }

    public class Energy
    {
        public double Kilojoules { get; set; }
        public double Kilocalories { get; set; }
    }

    public class Context : DbContext
    {
        public virtual DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseMySql("server=127.0.0.1;port=3306;user=root;password=;database=So62301095",
                    b => b.ServerVersion(new ServerVersion("8.0.20-mysql")))
                .UseLoggerFactory(LoggerFactory.Create(b => b
                    .AddConsole()
                    .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }
    }

    internal class Program
    {
        private static void Main()
        {
            using (var context = new Context())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                context.IceCreams.AddRange(
                    new IceCream
                    {
                        Name = "Vanilla",
                        Energy = new Energy
                        {
                            Kilojoules = 866.0,
                            Kilocalories = 207.0
                        },
                        Comments = new List<string>
                        {
                            "First!",
                            "Delicious!"
                        }
                    },
                    new IceCream
                    {
                        Name = "Chocolate",
                        Energy = new Energy
                        {
                            Kilojoules = 904.0,
                            Kilocalories = 216.0
                        },
                        Comments = new List<string>
                        {
                            "My husband likes this one a lot."
                        }
                    });

                context.SaveChanges();
            }

            using (var context = new Context())
            {
                var result = context.IceCreams
                    .OrderBy(e => e.IceCreamId)
                    .ToList();

                Debug.Assert(result.Count == 2);

                Debug.Assert(result[0].Name == "Vanilla");
                Debug.Assert(result[0].Energy.Object.Kilojoules == 866.0);
                Debug.Assert(result[0].Comments.Object.Count == 2);
                Debug.Assert(result[0].Comments.Object[0] == "First!");
            }
        }
    }
}

它生成以下 SQL:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 8.0.20 MySql SensitiveDataLoggingEnabled DetailedErrorsEnabled

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (81ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      DROP DATABASE `So62301095`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE DATABASE `So62301095`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (66ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE TABLE `IceCreams` (
          `IceCreamId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `Energy` json NULL,
          `Comments` json NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (15ms) [Parameters=[@p0='["First!","Delicious!"]', @p1='{"Kilojoules":866.0,"Kilocalories":207.0}', @p2='Vanilla' (Size = 4000)], CommandType='Text', CommandTimeout='30']

      INSERT INTO `IceCreams` (`Comments`, `Energy`, `Name`)
      VALUES (@p0, @p1, @p2);
      SELECT `IceCreamId`
      FROM `IceCreams`
      WHERE ROW_COUNT() = 1 AND `IceCreamId` = LAST_INSERT_ID();

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@p0='["My husband likes this one a lot."]', @p1='{"Kilojoules":904.0,"Kilocalories":216.0}', @p2='Chocolate' (Size = 4000)], CommandType='Text', CommandTimeout='30']

      INSERT INTO `IceCreams` (`Comments`, `Energy`, `Name`)
      VALUES (@p0, @p1, @p2);
      SELECT `IceCreamId`
      FROM `IceCreams`
      WHERE ROW_COUNT() = 1 AND `IceCreamId` = LAST_INSERT_ID();

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      SELECT `i`.`IceCreamId`, `i`.`Comments`, `i`.`Energy`, `i`.`Name`
      FROM `IceCreams` AS `i`
      ORDER BY `i`.`IceCreamId`

仔细查看 IceCream.Comments 属性，它完全符合您的要求.

Take a close look at the IceCream.Comments property, that does exactly what you want.

在下面的同一 GitHub 问题上，您会发现 另一个由我发布，有一个更复杂的例子.

On the same GitHub issue further below, you find another post by me, with a much more sophisticated example.

此外，我们接下来将为 Pomelo 实现完整的 JSON 支持(可能在一周内).

Also, we are going to implement full JSON support next for Pomelo (probably within a week).

这篇关于如何使用 Pomelo.EntityFramework 的 JsonObject的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！