json - EF Core 和 Pomelo 5.0 - 如何查询 json 列
问题描述
我在 Pomelo 5.0 中使用了最新版本的 json 实现,并将我的 maria 服务器配置为使用 microsoft json 序列化。
var serverVersion = new MariaDbServerVersion(new Version(10, 3, 0));
services.AddDbContext<BusinessManagementDbContext>(options =>
{
options.UseMySql(databaseConfiguration.ConnectionString, serverVersion, m =>
{
m.UseMicrosoftJson(MySqlCommonJsonChangeTrackingOptions.FullHierarchyOptimizedSemantically);
m.EnableRetryOnFailure();
});
options.EnableSensitiveDataLogging(true);
});
我可以将我的 POCO 保存在我的数据库中,但是当我尝试查询我的数据时,我得到一个空对象。这是我的数据: HeidySQL 数据
我的查询非常简单,但我认为我没有使用正确的方式进行 json 查询。
await Context.ValidatedSaleInvoices.AsNoTracking().FirstOrDefaultAsync(x => x.Id == id);
我的数据和我的属性“内容”之间似乎没有反序列化。
我怎样才能做到这一点 ?
谢谢,
编辑
我的模型是:
public class ValidateSaleInvoiceEntity
{
public int Id { get; set; }
public ValidateSaleInvoiceContent Content { get; set; }
}
public class ValidateSaleInvoiceContent
{
public string BusinessName { get; set; }
public DateTime Date { get; internal set; }
public string Number { get; internal set; }
public Address Address { get; internal set; }
public List<ValidateSaleInvoiceLineEntity> Lines { get; internal set; } = new List<ValidateSaleInvoiceLineEntity>();
}
public class ValidateSaleInvoiceLineEntity
{
public string Description { get; internal set; }
public decimal Quantity { get; internal set; }
public decimal UnitPriceVatExcluded { get; internal set; }
public decimal VatRate { get; internal set; }
}
我的 json 结果是这样的(空的,就像没有反序列化一样:{ "BusinessName":"", "Date":"", "Number":"" etc.}
我的老板停止了我关于 MariaDB Json 实现的 poc,所以我不得不回到这个好老朋友纯 sql 专栏:/。这就是为什么我没有完整的 json 结果。对不起
解决方案
对于将 JSON 自动序列化/反序列化为 POCO 的属性,您需要告诉 Pomelo,该属性的表列是 MySQL/MariaDB 类型json
:
public class ValidateSaleInvoiceEntity
{
public int Id { get; set; }
[Column(TypeName = "json")] // <-- this is one way to do it
public ValidateSaleInvoiceContent Content { get; set; }
}
public class MyContext : DbContext
{
// ...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<ValidateSaleInvoiceEntity>()
.Property(e => e.Content)
.HasColumnType("json"); // <-- this is another way to do it
}
}
这是一个完整的控制台项目:
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IssueConsoleTemplate
{
// EF Core entities:
public class IceCream
{
public int IceCreamId { get; set; }
public string Name { get; set; }
// Either use this data annotation, or the corresponding Fluent API call (see
// OnModelCreating), to explicitly mark the column type as JSON.
[Column(TypeName = "json")]
public IceCreamDetails Details { get; set; }
}
// JSON class:
public class IceCreamDetails
{
public int Kilojoule { get; set; }
public int Rating { get; set; }
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=So68020732";
var serverVersion = ServerVersion.AutoDetect(connectionString);
optionsBuilder.UseMySql(connectionString, serverVersion, options => options
.UseMicrosoftJson(MySqlCommonJsonChangeTrackingOptions.FullHierarchyOptimizedSemantically))
.UseLoggerFactory(
LoggerFactory.Create(
configure => configure
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>(
entity =>
{
// Either use this Fluent API call, or the corresponding data annotation
// (see the IceCreamDetails class), to explicitly mark the column type as JSON.
entity.Property(e => e.Details)
.HasColumnType("json");
entity.HasData(
new IceCream {IceCreamId = 1, Name = "Vanilla", Details = new IceCreamDetails { Kilojoule = 865, Rating = 9 }},
new IceCream {IceCreamId = 2, Name = "Chocolate", Details = new IceCreamDetails { Kilojoule = 903, Rating = 10 }});
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var iceCreams = context.IceCreams
.OrderBy(i => i.IceCreamId)
.ToList();
Trace.Assert(iceCreams.Count == 2);
Trace.Assert(iceCreams[0].Details.Kilojoule == 865);
Trace.Assert(iceCreams[1].Details.Rating == 10);
}
}
}
您可以在我们的存储库中找到最全面的 JSON 示例代码(参见JSON mapping and query scenarios
章节)。
推荐阅读
- python - 使用两个执行命令在表中插入值
- node.js - Next JS express jwt 认证
- android - Android不同scaleType之间的共享元素过渡
- javascript - 如何使用 performance.now()?
- flutter - 检查字符串是否包含以下任何内容
- flutter - 混合多种数据类型时 Dart 模型抛出错误
- swift - SwiftUI @FetchRequest crashes the app and returns error
- visual-studio-code - 如何在 VS Code 终端中启用所有特殊符号?我当前的设置包括 item2 + ohmyzsh + powerlevel10k
- c++ - 需要从 istream 和 ostream 派生 iostream
- c - 有没有办法在 C 中创建一个大小为 530000 的数组?