How to get WebAPI to return JSON for SQL query with varying select clause

Question

My application features a query builder which allows users to create a SQL query with a varying select clause. I need the results to be returned in JSON by WebAPI. There are a huge number of fields that could feature in the select.
The only approach I have found is to use:

var results = db.Database.SqlQuery<SomeObjectWhichDefinesAllPossibleFields>(sql);
return Ok(results);

Which relies on me maintaining a SomeObjectWhichDefinesAllPossibleFields class which does as it name suggests. I would like a more dynamic solution as more fields may be added in the future.

Is there another way to get WebAPI to serialize a SqlQuery with a varying select clause

I am in the same situation. Following this post. – I think I can code Feb 04 '16 at 22:40 — I think I can code, Feb 04 '16 at 22:40

score 2 · Answer 1 · edited Feb 11 '17 at 12:00

Web Api Repository, can use this:

public IList<Models.SalesTargetDetail> getSalesTarget(int SpId){
    System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
    sbSql.Append(@"select Cast(STD.Qty as varchar(10)) as Qty from tblSalesTarget ST inner join tblSalesTargetDetail STD on ST.Id=STD.SalesTargetId where 1=1");
    sbSql.Append(" and STD.SalesTargetId=" + SpId);
    var data = this.UnitOfWork.Context.Database.SqlQuery<Models.SalesTargetDetail>(sbSql.ToString()).ToList<Models.SalesTargetDetail>();
    return data.ToList<Models.SalesTargetDetail>();
}

And the Model is:

using System;
using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;
using Newtonsoft.Json;

namespace NNG.NCPML.Api.Models{
    //[JsonObject(MemberSerialization = MemberSerialization.OptIn)]
    public class SalesTargetDetail{
        public int SalesTargetDetailId{get; set;}
        public string SalesTargetId{get; set;}
        public int ItemInfoId{get; set;}
        public string ItemName{get; set;}
    }
}

Please format your code as code by pasting it into the answer and pressing CTRL+K or the `{}` button. Avoid screenshots of code. — Donald Duck, Feb 11 '17 at 11:30

score 0 · Answer 2 · edited May 23 '17 at 10:29

In WebAPI you can suppress fields that are null from being serialized. So you could start out with the dreaded SomeObjectWhichDefinesAllPossibleFields class, initialize all properties to null, and only set the properties that are requested in the select clause. You still have to maintain the large class, but if you only select/request two fields, only two fields will be serialized and returned in the JSON result.

score 0 · Answer 3 · answered Feb 06 '15 at 17:44

You could also use ExpandoObject, cast as an IDictionary, and dynamically add properties depending on the fields requested. Here is an example:

Example API Url: http://localhost/api/select/id,title,firstName,lastName

(add using System.Dynamic;)

[Route("api/select/{fieldList}")]
[HttpGet]
public object Dynamic(string fieldList)
{
    dynamic expando = new ExpandoObject();
    var dictionary = expando as IDictionary<string, Object>;

    string[] fields = fieldList.Split(',');
    foreach (var field in fields)
    {
        dictionary.Add(field, "code to get the field value goes here");
    }
    return expando;
}

Not clear to me how to retrieve the query results with this method — SteveC, Feb 09 '15 at 11:05

score 0 · Accepted Answer · answered Feb 09 '15 at 11:11

0

In the end I used TypeBuilder to construct a dynamic Type to pass to the SqlQuery method as per this http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery

This dynamic type was serialized to JSON nicely by web-api.

The only limitation is that it is necessary to know the dynamic field types (whether string, int etc) however in my application that was possible.

answered Feb 09 '15 at 11:11

SteveC

153
1
10

This solution seems overly engineered for such a simple task such as querying the database. – TravisO Jun 05 '17 at 14:55

How to get WebAPI to return JSON for SQL query with varying select clause

4 Answers4