C# Entity Framework: Returning EntityObject query result back as JSON for ajax

Question

After I retrieve my query results I want to return it as a json for the ajax call. I can achieve this, but I'm looking for a cleaner way.

I have a table in my SQL-Server and C# Entity Object Model called Sample. The following snippet fetches a row from the Sample table and returns it back to the service, which is suppose to return it as a json to the client.

    public Sample GetRequest(string surveyId)
    {
        AA.Msat.Sample requestQuery = null;
        long surveyIdInteger = Int64.Parse(surveyId);

        using (var db = new MSATEntities())
        {
            requestQuery = (from req in db.Samples
                       where req.SurveyId == surveyIdInteger
                        select req).Single();
        }

        return requestQuery;
    }

The ajax calls this Operatrion contract in my service

    [OperationContract]
    [WebGet(UriTemplate = "request/{surveyId}", ResponseFormat = WebMessageFormat.Json)]
    Sample GetRequest(string surveyId);

In chrome I get an error connection refused, but if I return null it is ok.

I can also get it working if I map the query result values manually to a class object containing nothing but the Sample table columns as class members and return that. Shown below:

    public SampleSheet GetMsatRequest(string surveyId)
    {
        Sample requestQuery = null;
        long surveyIdInteger = Int64.Parse(surveyId);

        using (var db = new MSATEntities())
        {
            requestQuery = (from req in db.Samples
                       where req.SurveyId == surveyIdInteger
                        select req).Single();
        }

        SampleSheet requestJson = new SampleSheet();

        // Copy values
        requestJson.SurveyId = requestQuery.SurveyId;
        requestJson.PanelId = requestQuery.PanelId.Value;
        requestJson.ClientName = requestQuery.ClientName;
        requestJson.PanelName = requestQuery.PanelName;
        requestJson.AcctMgr = requestQuery.AcctMgr;


        return requestJson;
    }
}

Where SampleSheet is

public class SampleSheet
{

    [DataMember]
    public long SurveyId;

    [DataMember]
    public int PanelId;

    [DataMember]
    public string ClientName;

    [DataMember]
    public string PanelName;

    [DataMember]
    public string AcctMgr;

}

My guess is because Sample cannot be converted into a JSON because Sample contains a lot of other values besides the table values like EntityKeys object. Is there a way to return Sample more easily than what I'm doing? It is quite tedious to map all the values of multiple tables.

EDIT: Here is requestQuery (Sample) serialized into a JSON string to show structure.

{
    "$id": "1",
    "SurveyId": 728801,
    "PanelId": 12,
    "ClientName": "hehehe",
    "PanelName": "hehehe",
    "AcctMgr": "hehhe",
    "EntityKey": {
        "$id": "2",
        "EntitySetName": "Samples",
        "EntityContainerName": "MSATEntities",
        "EntityKeyValues": [{
            "Key": "SurveyId",
            "Type": "System.Int64",
            "Value": "728801"
        }]
    }
}

@DavidG I edited it to show the structure of Sample. If you meant the table in sql, it has the same columns as SampleSheet. — roverred, Oct 18 '14 at 04:37

score 0 · Answer 1 · edited May 23 '17 at 11:49

So I found a couple ways to do this.

One is converting the Sample Entity to a JSON string then a stream:

Returning raw json (string) in wcf

Don't get why WCF can't return the Sample entity as JSON normally.

Another way is to write a mapping function for Sample and SampleSheet. Basically what I"m doing but without all the manual typing.

Cast one object to another

I went with the first method as it doesn't require me to make any new classes. Just convert the entity object to a string then stream and return. The downside is you get a bunch of unnecessary values in the JSON such as entity key objects. Not sure how significant the performance hit is, but doesn't seem very noticeable.

C# Entity Framework: Returning EntityObject query result back as JSON for ajax

1 Answers1