none
Table Join and output to json ? RRS feed

  • Question

  • Hi

    I have to table Issue (IssueId,IssueName,publishDate,cover )and MagInfo (magazineId,magazineName......)

    and magazineId reference to IssueId

    so my T-SQL looks like

    select  m.magazineName,m.publisher,i.issueId,i.issueName,i.publishDate,i.Cover 
         from MagInfo m inner join Issue I on 
    	 I.IssueID=M.magazineID
        

    var joinResult = from maginfo in context.MagInfo join issue in context.Issue on maginfo.MagazineId equals issue.IssueId select new { MagazineName = maginfo.MagazineName, Publisher = maginfo.Publisher, Issues = new[] { new{ IssueId= issue.IssueId, IssueName=issue.IssueName, PublishDate=issue.PublishDate, Cover=issue.Cover } } };


    and the output like this (5 record)

    [
      {
        "MagazineName": "凡一‧一凡",
        "Publisher": "商周出版",
        "Issues": [
          {
            "IssueId": 1,
            "IssueName": "Issue1",
            "PublishDate": "2018-08-05T00:00:00",
            "Cover": "zzzzzzz.jpg"
          }
        ]
      },
      {
        "MagazineName": "量子轉念的效應2:翱翔於量子心靈、多維時空、全息意識場",
        "Publisher": "商周出版",
        "Issues": [
          {
            "IssueId": 2,
            "IssueName": "Issue2",
            "PublishDate": "2016-01-18T00:00:00",
            "Cover": "aaaaaaa.png"
          }
        ]
      },
      {
        "MagazineName": "凡一‧一凡",
        "Publisher": "商周出版",
        "Issues": [
          {
            "IssueId": 1,
            "IssueName": "Issue1-1",
            "PublishDate": "2018-08-05T00:00:00",
            "Cover": "sxsxsxsxs"
          }
        ]
      },
      {
        "MagazineName": "凡一‧一凡",
        "Publisher": "商周出版",
        "Issues": [
          {
            "IssueId": 1,
            "IssueName": "Issue1-2",
            "PublishDate": "2018-08-05T00:00:00",
            "Cover": "edededede"
          }
        ]
      },
      {
        "MagazineName": "量子轉念的效應2:翱翔於量子心靈、多維時空、全息意識場",
        "Publisher": "商周出版",
        "Issues": [
          {
            "IssueId": 2,
            "IssueName": "Issue2-1",
            "PublishDate": "2016-01-08T00:00:00",
            "Cover": "ededdedede"
          }
        ]
      }
    ]

    In fact , I know I was wrong, I want my json like this (only 2 record , but first have 3 sub record, second have2 sub record)

    [
      {
        "MagazineName": "凡一‧一凡",
        "Publisher": "商周出版",
        "Issues": [
          {
            "IssueId": 1,
            "IssueName": "Issue1",
            "PublishDate": "2018-08-05T00:00:00",
            "Cover": "zzzzzzz.jpg"
          },
          {
            "IssueId": 1,
            "IssueName": "Issue1-1",
            "PublishDate": "2018-08-05T00:00:00",
            "Cover": "sxsxsxsxs"
          },
          {
            "IssueId": 1,
            "IssueName": "Issue1-2",
            "PublishDate": "2018-08-05T00:00:00",
            "Cover": "edededede"
          }
        ]
      },
      {
        "MagazineName": "量子轉念的效應2:翱翔於量子心靈、多維時空、全息意識場",
        "Publisher": "商周出版",
        "Issues": [
          {
            "IssueId": 2,
            "IssueName": "Issue2",
            "PublishDate": "2016-01-18T00:00:00",
            "Cover": "aaaaaaa.png"
          },
          {
            "IssueId": 2,
            "IssueName": "Issue2-1",
            "PublishDate": "2016-01-08T00:00:00",
            "Cover": "ededdedede"
          }
        ]
      }
    ]

    How can I change my T-SQL and C# code to finish goal ? thanks !!

    Wednesday, July 4, 2018 12:22 PM

All replies