Parse JSON array to model .NET Core causes empty set

Question

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 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"},{...},{...}]"

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());
    }
}

returns:

How can I archieve full binding of the json string?

I'm using Pomelo.EntityFrameworkCore.MySql (3.2.3) with a MySQL 8 Database in a ASP.NET Core 3.1 MVC Project.

Answer 1

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

These packages support POCO classes, the stack-specific DOM APIs and simple string mapping.

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).

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();
        }
    }
}