How to convert child JSON structure to C# object graph

Question

I'm using SQL Server 2016's FOR JSON to convert a SQL Server SELECT statement into a JSON string. My intention is to have C# convert produced JSON string into an object.

SQL Server Server select statement:

SELECT
    Id AS PersonId, 
    FirstName, 
    LastName, 
    Name, 
    Birthday,
    (
        SELECT
            PhoneNumber.PhoneNumberTypeId, 
            PhoneNumber.PhoneNumber             
        FROM            
            PhoneNumber 
            INNER JOIN PhoneNumberType ON PhoneNumber.PhoneNumberTypeId = PhoneNumberType.Id
        WHERE
            PhoneNumber.PersonId = Person.Id
        FOR JSON PATH,INCLUDE_NULL_VALUES       
    ) AS PhoneNumberList
FROM            
    Person
ORDER BY
    LastName
FOR JSON PATH,INCLUDE_NULL_VALUES

C# Object I'm trying to convert the data to:

public class PersonDataContract : IPersonDataContract
{
    public Int32 PersonId { get; set; }
    public String FirstName { get; set; }
    public String LastName { get; set; }
    public String Name { get; set; }
    public DateTime? Birthday { get; set; }
    public Int32 Age { get; }
    public IList<IPhoneNumberDataContract> PhoneNumberList { get; set; }

    public PersonDataContract()
    {
        PhoneNumberList = new List<IPhoneNumberDataContract>();
    }
}

Raw JSON:

[
    {
        "PersonId": 5,
        "FirstName": "Squire",
        "LastName": "Escrow",
        "Name": "Squire Escrow",
        "Birthday": "2001-12-12",
        "PhoneNumberList": "[{\"PhoneNumberTypeId\":1,\"PhoneNumber\":\"8129091423\"}]"
    }, 
    {
        "PersonId": 6,
        "FirstName": "Vilhelm",
        "LastName": "Huldsputh",
        "Name": "Vilhelm Huldsputh",
        "Birthday": "1953-11-13",
        "PhoneNumberList": "[{\"PhoneNumberTypeId\":1,\"PhoneNumber\":\"9871237171\"},{\"PhoneNumberTypeId\":2,\"PhoneNumber\":\"8189991212\"}]"
    }
]

Code converting raw JSON to Object:

...
using (DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{

    while (reader.Read())
    {
        var jsonString = reader.GetValue(0).ToString();

        var list = JsonConvert.DeserializeObject<List<PersonDataContract>>(jsonString);
    }

    reader.Close();
}

This process works as long as I remove PhoneNumberList from the raw JSON. I think the problem is I'm using 2 'FOR JSON PATH's in my SQL Statement (one for the PhoneNumberList itself and one for the overall SELECT Statement) which is causing the PhoneNumberList to be converted into a String which is not getting converted into an array.

How do you convert the child string representing a collection of phone numbers into an array of IPhoneNumberDataContract?

Answer 1

This is sort of covered in the FAQ:

Question. I have JSON text stored in a table column. I want to include it in the output of FOR JSON. But FOR JSON escapes all characters in the JSON, so I'm getting a JSON string instead of a nested object [..]

Answer. JSON stored in a text column or a literal is treated like any text. That is, it's surrounded with double quotes and escaped. If you want to return an unescaped JSON object, pass the JSON column as an argument to the JSON_QUERY function [..]

It doesn't mention explicitly that any non-trivial subquery that uses FOR JSON will also produce an escaped value, and requires JSON_QUERY in this way. So use

SELECT
    Id AS PersonId, 
    FirstName, 
    LastName, 
    Name, 
    Birthday,
    JSON_QUERY(
        SELECT
            ...
        FOR JSON PATH,INCLUDE_NULL_VALUES       
    ) AS PhoneNumberList

By "non-trivial", I mean that a query like this does not require JSON_QUERY:

SELECT 
    'Foo' AS Foo, (
        SELECT 1 AS A 
        FOR JSON PATH
    ) AS Bar
FOR JSON PATH

Bar will not be escaped here, since the optimizer apparently remembers it's dealing with JSON. I haven't yet been able to find a consistent set of rules that governs this (different question, same issue), and it's not even clear if this is intended behavior or a bug, but JSON_QUERY will bail you out regardless. It should probably always be used, to prevent surprises with a query going from "simple" to "complex" and suddenly needing it.