anitha anitha - 5 months ago 30
SQL Question

How to get list of data from foreign key associated table is json format in mvc

I have created two tables(question,answer) with foreign key association,I need list of answers based on questionID column for that I need linq query. I am beginner to mvc,Can anyone help to me


Controller code:


public JsonResult displayQuestion()
{
var result = from q in Db.questions
join a in Db.answers on q.Qid equals a.questionID
select new { q.QText, q.Qid, a.answer1 };
return Json(result, JsonRequestBehavior.AllowGet);
}


json Result:

[
{"QText":"result of 2+2","Qid":2,"answer1":"2"},
{"QText":"result of 2+2","Qid":2,"answer1":"4"},
{"QText":"result of 2+2","Qid":2,"answer1":"6"},
{"QText":"result of 2+2","Qid":2,"answer1":"8"}
]


But i need like below:

{
   "QText": "result of 2+2",
   "Qid": 2,
   "answer1": [
      { "option1": "2" },
      { "option1": "4" },
      { "option1": "6" },
      { "option‌​1": "8" }
   ]
}

Answer

Don't do a join, instead, get the question information you want and then do a subquery to get the answers for the specific question:

public JsonResult displayQuestion()
{
     var result = from q in Db.questions
                  select new { 
                    q.QText, 
                    q.Qid, 
                    answer1 = (from a in Db.answers 
                               where a.questionID == q.Qid
                               select new { option1 = a }).ToList()
                  };

     return Json(result, JsonRequestBehavior.AllowGet);
}
Comments