sql-server - 来自存储过程的 NewtonJson 反序列化 json 的问题，它返回 json 的结果

Question

我在 SQL Server 2016 中有一个存储过程，如下所示：

SELECT 
    a.Id as [ProdList.ProdId],
    @Main1 as [ProdList.Main],
    a.Hit as [ProdList.Hit],
    a.New as [ProdList.New],
    a.Sale as [ProdList.Sale],
    a.Price as [ProdList.Price],
    a.Price_old as [ProdList.OldPrice],
    a.Sort as [ProdList.Sort],
    a.ShName as [ProdList.Name],
    c.Big as [ProdList.ImagePath],
    a.SaleSize  as [ProdList.SaleSize],
    '' as [ProdList.QueryString],
    0 as [ProdList.RANK],
    (SELECT 
         ('/products/product/'+cast(f.Id as nvarchar(255))) as [Link],
         g.Big as [Img]
     FROM
         Products f
     INNER JOIN
         GroupProducts d ON d.ProdId = f.Id
     INNER JOIN
         ProdImages g ON g.ProdId = f.Id
     WHERE  
         d.ParentId = a.Id AND g.Main = 1
     FOR JSON PATH) AS [GroupProduct]
FROM 
    Products a
INNER JOIN 
    ProdInCategory b ON a.Id = b.ProdId
INNER JOIN 
    ProdImages c ON a.Id = c.ProdId
INNER JOIN 
    GroupProducts e ON a.Id = e.ProdId
WHERE
    c.Main = 1 AND b.CatId = @CatId AND e.ParentId = 0

UNION

SELECT 
    a.Id as [ProdList.ProdId],
    @Main1 as [ProdList.Main],
    a.Hit as [ProdList.Hit],
    a.New as [ProdList.New],
    a.Sale as [ProdList.Sale],
    a.Price as [ProdList.Price],
    a.Price_old as [ProdList.OldPrice],
    a.Sort as [ProdList.Sort],
    a.ShName as [ProdList.Name],
    g.Big as [ProdList.ImagePath],
    a.SaleSize  as [ProdList.SaleSize],
    '' as [ProdList.QueryString],
    0 as [ProdList.RANK],
    null as [GroupProduct]
FROM
    Products a
INNER JOIN 
    ProdImages g ON g.ProdId = a.Id
INNER JOIN 
    ProdInCategory pc ON a.Id = pc.ProdId
WHERE
    a.Id NOT IN (SELECT ProdId FROM GroupProducts) 
    AND pc.CatId = @CatId
FOR JSON PAT

在我的 ASP.NET MVC 5 应用程序中，我运行此存储过程并将结果反序列化为对象：

string query = String.Format("EXEC   [dbo].[GetListProdId3] @CatId={0}", incommingModel.id);
        var result = string.Concat(db.Database.SqlQuery<string>(query));

        List<ProdList> tovarListJson1 = JsonConvert.DeserializeObject<List<ProdList>>(result);

联合的第一个选择获取所有用 GroupProduct 填充的行。union 之后的第二个 select 从没有 group 的类别中取出其余的产品（不在 GroupProduct 中）。如果我在 SP 中的 union 语句之前注释掉了一半的选择，那么一切正常（独立地在 union 选择顶部或底部之前）。如果我尝试使用 union 语句运行整个选择，我会收到错误消息：

我的反序列化课程：

public class ProdList
{
    public int ProdId { get; set; }
    public bool Main { get; set; }
    public bool Hit { get; set; }
    public bool New { get; set; }
    public bool Sale { get; set; }
    public decimal Price { get; set; }
    public decimal OldPrice { get; set; }
    public decimal Sort { get; set; }
    public string  Name { get; set; }
    public string ImagePath { get; set; }
    public string SaleSize { get; set; }
    public int Rank { get; set; }
    public string QueryString { get; set; }
    public List<NestedProdList> GroupProduct { get; set; }
}

我来自存储过程的 json（第一个元素和最后一个元素）：

[{"ProdList":{"ProdId":225,"Main":true,"Hit":false,"New":false,"Sale":false,"Price":22.0000,"OldPrice":0.0000,"Sort":23.5200,"Name":"Ручка шариковая \"Tris Lx\", синяя","ImagePath":"\/images2\/44012\/ruchka-sharikovaya-tris-1.jpg","SaleSize":"0%        ","QueryString":"","RANK":0},"GroupProduct":"[{\"Link\":\"\\\/products\\\/product\\\/23091\",\"Img\":\"\\\/images2\\\/40554\\\/ruchka-sharikovaya-tris-1.jpg\"},{\"Link\":\"\\\/products\\\/product\\\/32836\",\"Img\":\"\\\/images2\\\/40555\\\/ruchka-sharikovaya-tris-1.jpg\"},{\"Link\":\"\\\/products\\\/product\\\/32918\",\"Img\":\"\\\/images2\\\/44010\\\/ruchka-sharikovaya-tris-1.jpg\"},{\"Link\":\"\\\/products\\\/product\\\/37093\",\"Img\":\"\\\/images2\\\/44011\\\/ruchka-sharikovaya-tris-1.jpg\"},{\"Link\":\"\\\/products\\\/product\\\/56573\",\"Img\":\"\\\/images2\\\/40553\\\/ruchka-sharikovaya-tris-1.jpg\"}]"},{"ProdList":{"ProdId":68812,"Main":true,"Hit":false,"New":false,"Sale":false,"Price":9.2000,"OldPrice":20.8900,"Sort":21.5600,"Name":"MIR FANTASY, ручка шариковая","ImagePath":"\/images2\/63849\/mir-fantasy-ruchka-1.jpg","SaleSize":"0%        ","QueryString":"","RANK":0},"GroupProduct":null},{"ProdList":{"ProdId":69329,"Main":true,"Hit":false,"New":false,"Sale":false,"Price":27.0000,"OldPrice":0.0000,"Sort":31.3600,"Name":"Ручка шариковая \"Twisty Safe Touch\", светло-голубая","ImagePath":"\/images2\/11763\/ruchka-sharikovaya-twisty-1.jpg","SaleSize":"0%        ","QueryString":"","RANK":0},"GroupProduct":null}]

标签： sql-serverasp.net-mvc-4json.net