Web API system out of memory exception

Question

I know there are a lot of questions regarding this issue. But I am unable to solve my problem.

API Flow

1. It accepts two parameters meter serial number and date time.
2. After the parameters are passed the API call is made
3. The API will search for the sent meter serial number in two databases.
4. After the record is fetched it should give the output.

Code

Below is my code

public HttpResponseMessage GetDetails(string msn, DateTime dt)
{
    try
    {
        var prodDetails = mdcEntitites.tj_xhqd.Where(m => m.sjsj >= dt)
                            .Select(x => new { MSN = x.zdjh, PingDateTime = x.sjsj, PingValue = x.xhqd })
                            .ToList();

        var mainDetails = kesc.tj_xhqd.Where(m => m.sjsj >= dt)
                            .Select(x => new { MSN = x.zdjh,PingDateTime= x.sjsj,PingValue = x.xhqd })
                            .ToList();

        var res = prodDetails.Concat(mainDetails).ToList();
        return Request.CreateResponse(HttpStatusCode.OK, new {details = res });

    }
    catch (Exception ex)
    {
        return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
    }
}

In above call, I am accepting a date time. When a meter is dispatched to field staff the date time is marked in the system, so the above date is that date time.

It will search all the records of that serial number after this date time.

Error

Using Postman when I try to run the API with current date time it gives me the following result

{
"details": [
    {
        "MSN": "002998002523",
        "PingDateTime": "2018-06-21T08:38:12",
        "PingValue": "26"
    },
    {
        "MSN": "002998001286",
        "PingDateTime": "2018-06-21T08:38:13",
        "PingValue": "18"
    },
    .
    .
    .
    .
    .
  ]
}

But when I try to run the API with date time less than current date time it gives me below exception

Exception of type 'System.OutOfMemoryException' was thrown.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.] System.IO.MemoryStream.set_Capacity(Int32 value) +89 System.IO.MemoryStream.EnsureCapacity(Int32 value) +90 System.IO.MemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count) +326 Microsoft.VisualStudio.Web.PageInspector.Runtime.Tracing.ArteryFilter.Write(Byte[] buffer, Int32 offset, Int32 count) +62 System.Web.HttpWriter.FilterIntegrated(Boolean finalFiltering, IIS7WorkerRequest wr) +9746340 System.Web.HttpResponse.FilterOutput() +104 System.Web.CallFilterExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +58 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +48 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +71

How can I get rid of this issue?

Any help would be highly appreciated.

Answer 1

As dlxeon points out, the PageInspector might be the cause of your exception. However you have a potential problem anyway: you are not limiting the number of search results that you are returning, and that might be an issue in the future when your database grows. You could do something like that:

1. Add an optional page parameter to your API call, and add something like .Skip((page-1)*PageSize).Take(PageSize) to your database query, right after the .Where. This is assuming that pages start at 1 and you have a PageSize constant defined.¹

2. Include paging information in your response as needed by the client, e.g:

{
  "pageSize": 10,
  "currentPage": 1,
  "details: "[
    ...
  ]
}

---

¹In your case it will be a bit more complex since you are doing two database queries, but you get the idea.