JSON Circular Reference Loop Error running Raw SQL in Web API using EF Core

I had a complex stored procedure that I needed to run using EF core in a Web API project. I cannot use LINQ to represent this query. I have followed all best practices outlined here;

Everything worked great except raw sql endpoint. It started giving me “Circular Reference loop error”. To keep thing simple, I am returning DTO from endpoint not the entity.

Newtonsoft.Json.JsonSerializationException: Self referencing loop detected for property 'task' with type 'System.Runtime.CompilerServices.AsyncTaskMethodBuilder`1+AsyncStateMachineBox`1[System.Collections.Generic.IEnumerable`1[FM.Service.Shared.DTO.TranMasterViewDto],FM.Service.LedgerTranViewService+<GetTransactionForProjectActionAsync>d__4]'. Path 'stateMachine.<>t__builder'
......

Here is my configuration before and after the fix;

Newtonsoft Configuration in program.cs file before the fix;
}).AddNewtonsoftJson()
Newtonsoft Configuration in program.cs file after the fix;
}).AddNewtonsoftJson(
    options => 
    options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore)

The error is gone and I am getting this results.

{
    "stateMachine": {
        "<>1__state": 0,
        "<>t__builder": {},
        "projectActionID": "82ee5154-971a-ed11-90e6-00155d717606",
        "trackChanges": false,
        "<>4__this": {}
    },
    "context": {},
    "moveNextAction": {
        "method": {
            "name": "MoveNext",

Wait…This is not what I expected. Why I am getting Tasks result where I need to see my Dto results.

It turns out that I am not returning results from my Async method but the task;

public async Task<IActionResult> GetTransactionForProjectActionAsync(Guid projectActionId)
{
   return Ok(_service.LedgerTranViewService.GetTransactionForProjectActionAsync(projectActionId, trackChanges: false));
        }

Fixing the return type has solved all of my problems;

return Ok(_service.LedgerTranViewService.GetTransactionForProjectActionAsync(projectActionId, trackChanges: false).Result);
        }
[
    {
        "id": 20221,
        "projectActionID": "82ee5154-971a-ed11-90e6-00155d717606",        
        "tranMasterDetail": [
            {
                "tranMasterId": "9358b0c5-6d30-ed11-90e8-00155d717606",
                "ledgerID": "63302216-0946-ea11-ac82-f81654967f7a",
                "tranDescription": "Rent paid",
                "tranDate": "2022-01-03T00:00:00",
                "tranFlag": 0,
                "tranAmount": 1300.00
            }
        ]
    }
]

Remove Newtonsoft ReferenceLoopHandler from program.cs file.

}).AddNewtonsoftJson()

Hope this will help someone.

Reference

https://stackoverflow.com/questions/62985907/avoid-or-control-circular-references-in-entity-framework-core

Resolve the issue of request matched multiple endpoints in .NET Core Web API

If there are two endpoint with same route, .NET Core Web API will throw request matched multiple endpoints error. Here is an example;

// api/menus/{menuId}/menuitems
[HttpGet("{menuId}/menuitems")]
public IActionResult GetAllMenuItemsByMenuId(int menuId)
{            
    ....
}

// api/menus/{menuId}/menuitems?userId={userId}
[HttpGet("{menuId}/menuitems")]
public IActionResult GetMenuItemsByMenuAndUser(int menuId, int userId)
{
    ...
}

This is impossible because the actions are dynamically activated. The request data (such as a query string) cannot be bound until the framework knows the action signature. It can’t know the action signature until it follows the route. Therefore, we can’t make routing dependent on things the framework doesn’t even know yet.

Long and short, we need to differentiate the routes in some way: either some other static path or making the userId a route param. However, we don’t actually need separate actions here. All action params are optional by default. Therefore, we can just have:

[HttpGet("{menuId}/menuitems")]
public IActionResult GetMenuItemsByMenu(int menuId, int userId)

And then we can branch on whether userId == 0 (the default). That should be fine here, because there will never be a user with an id of 0, but we may also consider making the param nullable and then branching on userId.HasValue instead, which is a bit more explicit.

We can also continue to keep the logic separate, if we prefer, by utilizing private methods. For example:

[HttpGet("{menuId}/menuitems")]
public IActionResult GetMenuItems(int menuId, int userId) =>
    userId == 0 ? GetMenuItemsByMenuId(menuId) : GetMenuItemsByUserId(menuId, userId);

private IActionResult GetMenuItemsByMenuId(int menuId)
{
    ...
}

private IActionResult GetMenuItemsByUserId(int menuId, int userId)
{
    ...
}

Have fun.

Read more here.

Protected web api configuration

Like web apps, the ASP.NET and ASP.NET Core web APIs are protected because their controller actions are prefixed with the [Authorize] attribute. The controller actions can be called only if the API is called with an authorized identity.

Consider the following questions:

  • Only an app can call a web API. How does the API know the identity of the app that calls it?
  • If the app calls the API on behalf of a user, what’s the user’s identity?

Read more here;

https://docs.microsoft.com/en-us/azure/active-directory/develop/scenario-protected-web-api-app-configuration

Microservice and Idempotent method

Idempotent method is the one that results in same behavior either in a single or mutiple calls. For example a delete method will delete a resource
and return 204 first time. on subsequent calls it will return 404. return codes has nothting to do with idempotent behavior but the fact is that
behavior of call will always be the same.

+———+——+————+
| Method | Safe | Idempotent |
+———+——+————+
| CONNECT | no | no |
| DELETE | no | yes |
| GET | yes | yes |
| HEAD | yes | yes |
| OPTIONS | yes | yes |
| POST | no | no |
| PUT | no | yes |
| TRACE | yes | yes |
+———+——+————+