WCF REST WebServiceHostFactory using linq to sql

Question

I'm trying to implement a REST service using WCF which will take the parameters P1, P2, P3, P4 and pass them to a stored procedure which will perform a query and return the results.

I have this code in C# for the service:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.ServiceModel.Activation;
using System.Data;

namespace RestServicePublishing
{
  [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
  [ServiceContract]
  public class RestService
  {
    [OperationContract]
    [WebGet]
    public List<Simulated_service_State> GetState(decimal P1, decimal P2, decimal P3,  decimal P4)
    {
       using (ServiceDataContext db = new ServiceDataContext())
       {
           List<Simulated_service_State> states = 
              db.GetStateByLongLat(P1, P2, P3, P4).ToList();
           db.SubmitChanges();
           return states;
       }
    }
  }
}

Simulated_Service_State is a temp table to which I'm storing results from the stored procedure call. When I call the web service through browser I get the following error:

Request Error

The server encountered an error processing the request. The exception message is 'Object reference not set to an instance of an object.'. See server logs for more details. The exception stack trace is:

at RestServicePublishing.ServiceDataContext..ctor() in C:....\RestServicePublishing\Service.designer.cs:line 39 at RestServicePublishing.RestService.GetState(Decimal P1, Decimal P2, Decimal P3, Decimal P4) in C:....\RestServicePublishing\RestService.svc.cs:line 42 at SyncInvokeGetState(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

!!!

The URL which I call is the following:

http://localhost/RestServicePublishing/RestService.svc/GetState?P1=14&P2=13&P3=22&P4=55

Can anybody help me to understand how to work with stored procedures and REST WCF service? How to return values from the temp table to the client?

Update

I was unable to post all this data into the comments section so I'm posting it here. This is the code I'm using now, but it's giving me the 0 as result which means that my query doesn't pick anything from the table:

public class RestService
{        
    [OperationContract]
    [WebGet (ResponseFormat = WebMessageFormat.Xml)]
    public string GetState(decimal P1, decimal P2, decimal P3, decimal P4)
    {
        IList<Simulated_service_State> query = new List<Simulated_service_State>();
        IList<string> Mac = new List<string>();
        int j;
        int jj;

        using (ServiceDataContext db = new ServiceDataContext())
        {
            query = db.GetStateByLongLat1(P1, P2, P3, P4).ToList();
            db.SubmitChanges();
            var query2 = from Simulated_service_State state in db.Simulated_service_States
                         select state.MAC;
            Mac = query2.ToList();
            jj = Mac.Count;
            j = query.Count;
        }
        return j.ToString() + "," + jj.ToString();
    }
}

If I use this code (not a stored procedure) I'm getting the result from the table so I assume that the linq to sql works fine (ServiceDataContext):

public class RestService
{

    [OperationContract]
    [WebGet (ResponseFormat = WebMessageFormat.Xml)]
    public string GetState(decimal P1, decimal P2, decimal P3, decimal P4)
    {
        IList<Simulated_service_State> query = new List<Simulated_service_State>();
        IList<string> Mac = new List<string>();
        int j;
        int jj;
        using (ServiceDataContext db = new ServiceDataContext())
        {
            var query3 = from SimulatedNode node in db.SimulatedNodes
                        select node.MAC;
            Mac = query3.ToList();
            j = (Mac.Count);
        }
        return j.ToString();

The result I get is 4 which represents the number of entries in the list.

Any idea how to proceed?

Answer 1

I'm thinking GetStateByLongLat() is returning null. Then the ToList() would throw the 'Object reference not set to an instance of an object.' error.

Answer 2

I gave up doing the query with stored procedure, so I used the standard sql query function which works and is providing results. Here is the final code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.ServiceModel.Activation;
using System.Data;

namespace RestServicePublishing
{
[AspNetCompatibilityRequirements(RequirementsMode =     AspNetCompatibilityRequirementsMode.Allowed)]
[System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
[ServiceContract]

public class RestService
{

    [OperationContract]
    [WebGet (ResponseFormat = WebMessageFormat.Json)]
    public List<Last_status_by_LongLat> GetState(decimal P1, decimal P2, decimal P3, decimal P4)
    {
        List<Last_status_by_LongLat> Mac = new List<Last_status_by_LongLat>();

        using (ServiceDataContext db = new ServiceDataContext())
        {
            var query = from view node in db.views
                         where table.param > P1 && table.param2 < P2 && table.param2 > P3 && table.param < P4
                         select table;
            Mac = query.ToList();


        }
        return Mac;

    }
}
}

I'm passing the P1 - P4 parameters to the "where" statement of the sql call and I then pass the variable query to the "List Mac " which represents the SQL view as linq to sql. At the end I'm returing the list content to the GetState WebGet call.