Return json in specific format Web API, Linq

Question

I need help with creating json. I am having two models i.e, QUESTIONBANK and QUESTIONOPTIONS. QUESTIONOPTIONS contains the multiple options for Question in QUESTIONBANK

public partial class QUESTIONBANK
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public QUESTIONBANK()
        {
            this.QUESTIONOPTIONS = new HashSet<QUESTIONOPTION>();
        }

        public int QID { get; set; }
        public string QUESTION { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<QUESTIONOPTION> QUESTIONOPTIONS { get; set; }
    }

  public partial class QUESTIONOPTION
    {
        public int OPTIONID { get; set; }
        public Nullable<int> QID { get; set; }
        public string OPTIONTEXT { get; set; }
        public Nullable<bool> ISANSWER { get; set; }

        public virtual QUESTIONBANK QUESTIONBANK { get; set; }
    }

I have written code in my controller as below

  public HttpResponseMessage GetQUESTIONBANKs()
        {
            var qSet = (from q in db.QUESTIONBANKs
                        join o in db.QUESTIONOPTIONS
                        on q.QID equals o.QID
                        select new questions
                        {
                            q = q.QUESTION,
                            a = o.ISANSWER.ToString(),
                            options = o.OPTIONTEXT
                        }).ToList();


            return Request.CreateResponse(HttpStatusCode.OK, qSet);
          }

The json which I am getting is as below

[
  {
    "q": "Accounting provides information on",
    "a": "False",
    "options": "Cost and income for managers"
  },
  {
    "q": "Accounting provides information on",
    "a": "False",
    "options": " Companys tax liability for a particular year"
  },
  {
    "q": "Accounting provides information on",
    "a": "False",
    "options": "Financial conditions of an institution"
  },
  {
    "q": "Accounting provides information on",
    "a": "True",
    "options": " All of the above"
  },
  {
    "q": "The long term assets that have no physical existence but are rights that have value is known as",
    "a": "False",
    "options": "Current assets"
  },
  {
    "q": "The long term assets that have no physical existence but are rights that have value is known as",
    "a": "False",
    "options": "Fixed assets"
  },
  {
    "q": "The long term assets that have no physical existence but are rights that have value is known as",
    "a": "True",
    "options": "Intangible assets"
  },
  {
    "q": "The long term assets that have no physical existence but are rights that have value is known as",
    "a": "False",
    "options": "Investments"
  },
  {
    "q": "The assets that can be converted into cash within a short period (i.e. 1 year or less) are known as",
    "a": "True",
    "options": "Current assets"
  },
  {
    "q": "The assets that can be converted into cash within a short period (i.e. 1 year or less) are known as",
    "a": "False",
    "options": " Fixed assets"
  },
  {
    "q": "The assets that can be converted into cash within a short period (i.e. 1 year or less) are known as",
    "a": "False",
    "options": "Intangible assets"
  },
  {
    "q": "The assets that can be converted into cash within a short period (i.e. 1 year or less) are known as",
    "a": "False",
    "options": "Investments"
  },
  {
    "q": "Patents, Copyrights and Trademarks are",
    "a": "False",
    "options": " Current assets"
  },
  {
    "q": "Patents, Copyrights and Trademarks are",
    "a": "False",
    "options": " Fixed assets"
  },
  {
    "q": "Patents, Copyrights and Trademarks are",
    "a": "True",
    "options": "Intangible assets"
  },
  {
    "q": "Patents, Copyrights and Trademarks are",
    "a": "False",
    "options": "Investments"
  },
  {
    "q": "The following is not a type of liability",
    "a": "True",
    "options": "Short term"
  },
  {
    "q": "The following is not a type of liability",
    "a": "False",
    "options": "Current"
  },
  {
    "q": "The following is not a type of liability",
    "a": "False",
    "options": "Fixed"
  },
  {
    "q": "The following is not a type of liability",
    "a": "False",
    "options": "Contingent"
  }
]

Whereas I need json in below format, where question options needs to be retrieved as comma separated and the option which has flag true for ISANSWER should come in "a".

quizData: {
        "questions": [{
            "q": "Look at the following selector: $(\"div\")<br/> What does it select?",
            "a": "All div elements",
            "options": [
                "All div elements",
                "The first div element",
                "All elements with the class \"div\""
            ]
        }, {
            "q": "Which of the following is correct",
            "a": "jQuery is a JavaScript Library",
            "options": [
                "jQuery is a JSON Library",
                "jQuery is a JavaScript Library"
            ]
        }, {
            "q": "jQuery uses CSS selectors to select elements?",
            "a": "True",
            "options": [
                "True",
                "False"
            ]
        }, {
            "q": "Which sign does jQuery use as a shortcut for jQuery?",
            "a": "the $ sign",
            "options": [
                "the % sign",
                "the $ sign",
                "the ? Sign"
            ]
        }, {
            "q": "Is jQuery a library for client scripting or server scripting?",
            "a": "Client scripting",
            "options": [
                "Client scripting",
                "Server scripting",
            ]
        }]
    }

Your help is really appreciated. Thanks in advance

Answer 1

Firstly, this has nothing to do with Web API, JSON, or even EntityFramwork.

You have a logic error that would manifest in the exact same manner in a query against an in-memory collection and never serialized.

The output is exactly what would be expected.

However the correction is easily made.

The following will do the trick

public IEnumerable<QuestionViewModel> GetQUESTIONBANKs()
{
    return from question in db.QUESTIONBANKs
                join option in db.QUESTIONOPTIONS
                on question.ID equals option.QID
                into questonOptions
                select new QuestionViewModel
                {
                    Q = question.QUESTION,
                    A = questionOptions.First(o => o.ISANSWER).OPTIONTEXT,
                    Options = from o in questionOptions select o.OPTIONTEXT
                };
      }

Note this exhibits one of the most powerful aspects of LINQ - the ability to project hierarchical results.

However if your EntityFramwork and Database are setup properly, you don't need to write the join yourself

from question in db.QUESTIONBANKs
select new QuestionViewModel
{
    Q = question.QUESTION,
    A =questionOptions.First(o => o.ISANSWER).OPTIONTEXT,    
    Options = from o in questionOptions select o.OPTIONTEXT
}

Answer 2

You can aggregate the result from data base and wrap it in Anonymous Type

var aggr = qSet.GroupBy(x => x.q)
               .Select(t => new { 
                              q = t.Key, 
                              a = t.FirstOrDefault(ans => ans.a == "True").options, 
                              options = t.Select(ans => ans.options).ToList() });

var result = new { quizData = new { questions = aggr }};
return Request.CreateResponse(HttpStatusCode.OK, result);