问题描述
通过调用 DbSet< T> .FromSqlRaw()，我可以在数据库中调用存储过程，该存储过程返回如下结果集:
By calling DbSet<T>.FromSqlRaw() I can call a Stored Procedure in my Database, which returns a result set like this:
Id VARCHAR(36)
FirstName VARCHAR(255)
LastName VARCHAR(255) NULL
Email VARCHAR(255) NULL
Numbers VARCHAR(?) NULL
Numbers 是一个 VARCHAR 字段，其中包含 SearchContactsNumber 的JSON数组:
Numbers is a VARCHAR field which contains JSON array of SearchContactsNumber:
public sealed class SearchContactsNumber
{
public Guid IdNumber { get; set; }
public string Type { get; set; }
public string Number { get; set; }
}
例如，结果集可能是这样的:
So, for instance, a result set could be like this:
"34f8d20f-21da-11eb-a249-de3268ec1e72" | "Paul" | "Newman" | "[email protected]" | "[{"IdNumber":"481d2957-21da-11eb-a249-de3268ec1e72","Type":"Telephone","Number":"+440001122333"},{...},{...}]"
调试TestController.Index端点:
Debugging the TestController.Index endpoint:
public sealed class SearchContacts
{
public Guid IdContact { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Company { get; set; }
public IEnumerable<SearchContactsNumber> Numbers { get; set; }
}
public class TestController : Controller
{
private readonly DbContext _context;
public TestController(DbContext context)
{
_context = context;
}
public IActionResult Index()
{
var set = _context.SearchContacts.FromSqlRaw<SearchContacts>($"CALL `SearchContacts`()");
return Ok(set.ToList());
}
}
返回:
如何归档json字符串的完整绑定?
How can I archieve full binding of the json string?
我正在将 Pomelo.EntityFrameworkCore.MySql(3.2.3)与ASP.NET Core 3.1 MVC项目中的MySQL 8数据库一起使用.
I'm using Pomelo.EntityFrameworkCore.MySql (3.2.3) with a MySQL 8 Database in a ASP.NET Core 3.1 MVC Project.
推荐答案
Pomelo在几周前推出了全栈JSON支持，该支持在最新的Pomelo版本中可用，并将继续使用(以前的方法，例如 JsonObject< T> 现在已被弃用，并且在5.0+中不再受正式支持).
Pomelo introduced full-stack JSON support a couple of weeks ago, which is available in the latest Pomelo versions and will be used moving forward (previous approaches, like JsonObject<T> are now deprecated and are not officially supported in 5.0+ anymore).
要使用它，您需要添加以下软件包之一，具体取决于要在引擎盖下使用的堆栈:
To use it, you need to add one of the following packages, depending on which stack you want to use under the hood:
-
Pomelo.EntityFrameworkCore.MySql.Json.Microsoft -
Pomelo.EntityFrameworkCore.MySql.Json.Newtonsoft
这些软件包支持POCO类，特定于堆栈的DOM API和简单的字符串映射.
These packages support POCO classes, the stack-specific DOM APIs and simple string mapping.
我们还支持从JSON实体的仅顶级(非常快)到完整(较慢)更改跟踪的任何内容(可以通过 UseMicrosoftJson()的和 options 参数进行控制 UseNewtonsoftJson()方法).
We also support anything from top-level-only (very fast) to full (slower) change tracking for JSON entities (can be controlled via the options parameter of the UseMicrosoftJson() and UseNewtonsoftJson() methods).
这里是一个可以正常运行的控制台示例项目，该示例演示了如何针对您的特定情况(此处使用Microsoft堆栈)使用Pomelo的全栈JSON支持:
Here is a fully working console sample project, that demonstrates how to use the full-stack JSON support of Pomelo for your particular case (here using the Microsoft stack):
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;
namespace IssueConsoleTemplate
{
//
// EF Core Entities:
//
public sealed class SearchContact
{
public Guid IdContact { get; set; }
public string FirstName { get; set; }
public IEnumerable<SearchContactsNumber> Numbers { get; set; }
}
//
// JSON Entities:
//
public sealed class SearchContactsNumber
{
public Guid IdNumber { get; set; }
public string Type { get; set; }
public string Number { get; set; }
}
//
// DbContext:
//
public class Context : DbContext
{
public DbSet<SearchContact> SearchContacts { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseMySql(
"server=127.0.0.1;port=3306;user=root;password=;database=So64741089",
b => b.ServerVersion("8.0.21-mysql")
.CharSetBehavior(CharSetBehavior.NeverAppend)
.UseMicrosoftJson()) // <-- needed when using the Microsoft JSON stack (System.Text.Json)
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<SearchContact>(
entity =>
{
entity.HasKey(e => e.IdContact);
entity.Property(e => e.Numbers)
.HasColumnType("json"); // <-- simple way to serialize any property from/to JSON
});
}
}
internal class Program
{
private static void Main()
{
using var context = new Context();
SetupDatabase(context);
var searchContacts = context.SearchContacts
.FromSqlInterpolated($"CALL `SearchContacts`()")
.ToList();
Debug.Assert(searchContacts.Count == 1);
Debug.Assert(searchContacts[0].Numbers.Count() == 1);
Debug.Assert(searchContacts[0].Numbers.First().IdNumber == new Guid("481d2957-21da-11eb-a249-de3268ec1e72"));
}
private static void SetupDatabase(Context context)
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var connection = context.Database.GetDbConnection();
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = @"CREATE PROCEDURE `SearchContacts`()
BEGIN
SELECT '34f8d20f-21da-11eb-a249-de3268ec1e72' as `IdContact`,
'Paul' as `FirstName`,
'[{""IdNumber"":""481d2957-21da-11eb-a249-de3268ec1e72"",""Type"":""Telephone"",""Number"":""+440001122333""}]' as `Numbers`;
END";
command.ExecuteNonQuery();
}
}
}
这篇关于解析JSON数组以建模.NET Core导致空集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！