Find entry in one table to search in another

Question

I'm trying to make a web API using Entity Framework Core, and ASP.NET Core

I have a table of user profiles (called featUsers), and a table of scenarios (called scenarios). First, I want to find a user based on a field, then return the list of scenarios associated with that user.

Returning all the scenarios works fine:

// GET: api/Scenarios
[HttpGet]
public async Task<ActionResult<IEnumerable<Scenario>>> Getscenarios()
{
    return await _context.scenarios.ToListAsync();
}

Modifying that to test searching for and returning scenarios that have a specific foreign key, also works fine:

// GET: api/Scenarios
[HttpGet]
public async Task<ActionResult<IEnumerable<Scenario>>> Getscenarios()
{
    return await _context.scenarios.Where(scenario => scenario.FeatUserId == 1).ToListAsync();
}

But when I modify that further to find the user profile first, then use that profile to find the scenarios, things fall over. This is what the function looks like:

// GET: api/Scenarios
[HttpGet]
public async Task<ActionResult<IEnumerable<Scenario>>> Getscenarios()
{
    //Find the profile, with specific Identity ID
    var featProfile = _context.featUsers.FirstOrDefault(u => u.IdentityId == "44fc0698-9f99-46dd-bfac-db1781fd8b01");

    //Debug
    Console.WriteLine(featProfile.FeatUserId);

    //Return all the scenarios which are related to that profile above
    return await _context.scenarios.Where(scenario => scenario.FeatUserId == featProfile.FeatUserId).ToListAsync();
}

When I query this endpoint with Postman (The Console.WriteLine correctly writes a '1' to the terminal, as expected, but) this is the response I get in Postman:

Newtonsoft.Json.JsonSerializationException: Self referencing loop detected with type 'DAF_FEAT.Models.Scenario'. Path '[0].featUser.scenarios'.
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.CheckForCircularReference(JsonWriter writer, Object value, JsonProperty property, JsonContract contract, JsonContainerContract containerContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject(JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject(JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(JsonWriter writer, Object value, JsonContract valueContract, JsonProperty member, JsonContainerContract containerContract, JsonProperty containerProperty)
   at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)
   at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)
   at Newtonsoft.Json.JsonSerializer.Serialize(JsonWriter jsonWriter, Object value)
   at Microsoft.AspNetCore.Mvc.Formatters.NewtonsoftJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
   at Microsoft.AspNetCore.Mvc.Formatters.NewtonsoftJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
   at Microsoft.AspNetCore.Mvc.Formatters.NewtonsoftJsonOutputFormatter.WriteResponseBodyAsync(OutputFormatterWriteContext context, Encoding selectedEncoding)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|29_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
   at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events)
   at IdentityServer4.Hosting.MutualTlsTokenEndpointMiddleware.Invoke(HttpContext context, IAuthenticationSchemeProvider schemes)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at IdentityServer4.Hosting.BaseUrlMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

HEADERS
=======
Cache-Control: no-cache
Connection: keep-alive
Accept: */*
Accept-Encoding: gzip, deflate
Host: localhost:5001
User-Agent: PostmanRuntime/7.15.0
Postman-Token: c3ba03bb-8f2f-433a-998a-e13cd361f888

Just for completeness, the models for look like this:

    public class FeatUser
    {
        public int FeatUserId { get; set; }
        public string Role { get; set; }
        public string OrganisationName { get; set; }
        public string PhoneNumber { get; set; }

        public string IdentityId { get; set; }

        public List<Scenario> Scenarios { get; set; }
    }

    public class Scenario
    {
        public int ScenarioId { get; set; }
        public string Name { get; set; }
        public DateTime DateCreated { get; set; }
        public DateTime DateUpdated { get; set; }
        public bool Active { get; set; }

        public int FeatUserId { get; set; }
        public FeatUser FeatUser { get; set; }
    }

Maybe I'm going about this all wrong, and should be doing things taking advantage of Navigation properties by doing something more like this:

return (await _context.featUsers.Include(u => u.Scenarios).FirstOrDefaultAsync(u => u.IdentityId == userId)).Scenarios.ToList();

`FeatUser` contains multiple `Scenario` objects and `Scenario` links to a `FeatUser` which contains multiple `Scenario` objects. etc. etc. You've hit a good reason why you should never return EF models directly to your clients. — DavidG, Jul 05 '19 at 11:40
Ah, of course: `services.AddMvc() .AddNewtonsoftJson(options => options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore);` will fix this. I feel silly for not realising. — marno11, Jul 05 '19 at 12:30
Don't feel silly for not realising, it's not that obvious. But maybe feel a little silly for not Googling :) — DavidG, Jul 05 '19 at 12:31

score 0 · Answer 1 · answered Jul 05 '19 at 12:31

As DavidG suggested in his comment, you should not return EF models in your apis. You should create DTOs (data transfer objects) for your models and return those instead. Your DTOs should not have self referencing loops (FeatUser contains Scenario, then Scenario contains FeatUser). So in your example, your DTOs would look like this:

public class FeatUserDto
{
    public int FeatUserId { get; set; }
    public string Role { get; set; }
    public string OrganisationName { get; set; }
    public string PhoneNumber { get; set; }

    public string IdentityId { get; set; }

    public List<ScenarioDto> Scenarios { get; set; }
}

public class ScenarioDto
{
    public int ScenarioId { get; set; }
    public string Name { get; set; }
    public DateTime DateCreated { get; set; }
    public DateTime DateUpdated { get; set; }
    public bool Active { get; set; }
}

Notice how ScenarioDto does not contain a link back to the FeatUserDto in order to avoid a self referencing loop.

You are now left with mapping your models to your DTOs. Automapper can help with that.

You saw the duplicate I linked, why add an answer? – DavidG Jul 05 '19 at 12:46 — DavidG, Jul 05 '19 at 12:46
I saw the duplicate link after adding the answer. – Elias N Jul 05 '19 at 13:38 — Elias N, Jul 05 '19 at 13:38

Find entry in one table to search in another

1 Answers1