Entity Framework code-first working with local DB, but errors when deployed to Azure

Question

I have developed a project usein Asp.Net Web API, followint the Entity Framework code-first approach.

I have tested it in local machine with a local DB in Visual Studio 2013 and it works correctly.

The problem comes when I replace the connection string and deploy the DB to Azure. I'm able to perform some queries, but not other.

For example if I enter in the browser http://my-site/api/users/1 I get the expected result. However the query http://my-site/api/users/ return me an error related to Json serialization:

{ "$id" : "1",
  "ExceptionMessage" : "The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; charset=utf-8'.",
  "ExceptionType" : "System.InvalidOperationException",
  "InnerException" : { "$id" : "2",
      "ExceptionMessage" : "Error getting value from 'Devices' on 'System.Data.Entity.DynamicProxies.User_50420314CCDC05AAF3288A574C1CBB8436C09BAEF7539C2795445FCBA161AC99'.",
      "ExceptionType" : "Newtonsoft.Json.JsonSerializationException",
      "InnerException" : { "$id" : "3",
          "ExceptionMessage" : "An error occurred while executing the command definition. See the inner exception for details.",
          "ExceptionType" : "System.Data.Entity.Core.EntityCommandExecutionException",
          "InnerException" : { "$id" : "4",
              "ExceptionMessage" : "There is already an open DataReader associated with this Command which must be closed first.",
              "ExceptionType" : "System.InvalidOperationException",
              "Message" : "An error has occurred.",
              "StackTrace" : "   at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)\r\n   at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)\r\n   at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<>c__DisplayClassb.<Reader>b__8()\r\n   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TInterceptionContext,TResult](Func`1 operation, TInterceptionContext interceptionContext, Action`1 executing, Action`1 executed)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)\r\n   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"
            },
          "Message" : "An error has occurred.",
          "StackTrace" : "   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)\r\n   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.<GetResults>b__a()\r\n   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.<GetResults>b__9()\r\n   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)\r\n   at System.Data.Entity.Core.Objects.ObjectQuery`1.Execute(MergeOption mergeOption)\r\n   at System.Data.Entity.Core.Objects.DataClasses.EntityCollection`1.Load(List`1 collection, MergeOption mergeOption)\r\n   at System.Data.Entity.Core.Objects.DataClasses.EntityCollection`1.Load(MergeOption mergeOption)\r\n   at System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.Load()\r\n   at System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.DeferredLoad()\r\n   at System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.LoadProperty[TItem](TItem propertyValue, String relationshipName, String targetRoleName, Boolean mustBeNull, Object wrapperObject)\r\n   at System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.<>c__DisplayClass7`2.<GetInterceptorDelegate>b__1(TProxy proxy, TItem item)\r\n   at System.Data.Entity.DynamicProxies.User_50420314CCDC05AAF3288A574C1CBB8436C09BAEF7539C2795445FCBA161AC99.get_Devices()\r\n   at GetDevices(Object )\r\n   at Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(Object target)"
        },
      "Message" : "An error has occurred.",
      "StackTrace" : "   at Newtonsoft.Json.Serialization.DynamicValueProvider.GetValue(Object target)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.CalculatePropertyValues(JsonWriter writer, Object value, JsonContainerContract contract, JsonProperty member, JsonProperty property, JsonContract& memberContract, Object& memberValue)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject(JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)\r\n   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)\r\n   at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)\r\n   at Newtonsoft.Json.JsonSerializer.Serialize(JsonWriter jsonWriter, Object value)\r\n   at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n   at System.Net.Http.Formatting.JsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, Encoding effectiveEncoding)\r\n   at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStream(Type type, Object value, Stream writeStream, HttpContent content)\r\n   at System.Net.Http.Formatting.BaseJsonMediaTypeFormatter.WriteToStreamAsync(Type type, Object value, Stream writeStream, HttpContent content, TransportContext transportContext, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.GetResult()\r\n   at System.Web.Http.WebHost.HttpControllerHandler.<WriteBufferedResponseContentAsync>d__19.MoveNext()"
    },
  "Message" : "An error has occurred.",
  "StackTrace" : null
}

This problem is really annoying me, because as I mentioned, with the local DB works fine. The databases generated locally and in Azure seems equal. So, I can't guess where the problem could be.

Answer 1

Adding .ToArray() to the result I was returning from my GetAll() method solved the problem:

    public IEnumerable<TEntity> GetAll()
    {
        return _dbContext.Set<TEntity>().ToArray();
    }

But, I'm still wondering why it was working with the local DB and not with the DB deployed to Azure.

Adding MultipleActiveResultSets=True also solved my issue. But since I don't really know what does enabling that feature entail, I opted for the first option.

Answer 2

The answer is what the error message tells you. It cannot serialize Entity Framework Proxy to JSON. You should map to POCO classes and return them:

//poco
    public class PersonPoco
    {
       public string FirstName {get;set;}
       public string LastName {get;set;}
    }

//controller

public IEnumerable<PersonPoco> Get()
{
    var person = _personRepository.FindAll();

    person.Select(x=> new PersonPoco() {
     FirstName = x.f_name,
     LastName = x.l_name
    }).ToList();
}

you can use AutoMapper to do this conversion, you can create Extension methods to do this conversion. Anyway, it's just a sample.