Exclude duplicate results from self related table using entity framework core

user_5204828 0
Viewed 276

I need to create hierarchical tree from accounts for a financial accounting web-api software I created a table with self relationship but get duplicate records.

Data model:

public class Account
{
    public int AccountId { get; set; }

    public int AccountCode { get; set; }

    [Required]
    public string AccountName { get; set; }
    
    public int AccountParentCode { get; set; }
    public int? AccountParentId { get; set; }

    [IgnoreDataMember]
    public Account AccountParent { get; set; }
            
    public ICollection<Account> AccountChields { get; set; }
}

return the value to api:

return _context.Accounts.ToListAsync();

My output:

    [  {
    "accountId": 147,
    "accountCode": 1,
    "accountName": "Test1",
    "accountParentCode": 0,
    "accountParentId": null,
    "accountChields": [
      {
        "accountId": 149,
        "accountCode": 11,
        "accountName": "Test11",
        "accountParentCode": 1,
        "accountParentId": 147,
        "accountChields": [
          {
            "accountId": 152,
            "accountCode": 113,
            "accountName": "Test113",
            "accountParentCode": 11,
            "accountParentId": 149,
            "accountChields": null
          },
          {
            "accountId": 153,
            "accountCode": 114,
            "accountName": "Test114",
            "accountParentCode": 11,
            "accountParentId": 149,
            "accountChields": null
          },
          {
            "accountId": 154,
            "accountCode": 115,
            "accountName": "Test115",
            "accountParentCode": 11,
            "accountParentId": 149,
            "accountChields": null
          }
        ]
      },
      {
        "accountId": 150,
        "accountCode": 16,
        "accountName": "Test16",
        "accountParentCode": 1,
        "accountParentId": 147,
        "accountChields": null
      },
      {
        "accountId": 151,
        "accountCode": 18,
        "accountName": "Test18",
        "accountParentCode": 1,
        "accountParentId": 147,
        "accountChields": null
      }
    ]
  },
  {
    "accountId": 148,
    "accountCode": 2,
    "accountName": "Test2",
    "accountParentCode": 0,
    "accountParentId": null,
    "accountChields": null
  },
  {
    "accountId": 149,
    "accountCode": 11,
    "accountName": "Test11",
    "accountParentCode": 1,
    "accountParentId": 147,
    "accountChields": [
      {
        "accountId": 152,
        "accountCode": 113,
        "accountName": "Test113",
        "accountParentCode": 11,
        "accountParentId": 149,
        "accountChields": null
      },
      {
        "accountId": 153,
        "accountCode": 114,
        "accountName": "Test114",
        "accountParentCode": 11,
        "accountParentId": 149,
        "accountChields": null
      },
      {
        "accountId": 154,
        "accountCode": 115,
        "accountName": "Test115",
        "accountParentCode": 11,
        "accountParentId": 149,
        "accountChields": null
      }
    ]
  },
  {
    "accountId": 150,
    "accountCode": 16,
    "accountName": "Test16",
    "accountParentCode": 1,
    "accountParentId": 147,
    "accountChields": null
  },
  {
    "accountId": 151,
    "accountCode": 18,
    "accountName": "Test18",
    "accountParentCode": 1,
    "accountParentId": 147,
    "accountChields": null
  },
  {
    "accountId": 152,
    "accountCode": 113,
    "accountName": "Test113",
    "accountParentCode": 11,
    "accountParentId": 149,
    "accountChields": null
  },
  {
    "accountId": 153,
    "accountCode": 114,
    "accountName": "Test114",
    "accountParentCode": 11,
    "accountParentId": 149,
    "accountChields": null
  },
  {
    "accountId": 154,
    "accountCode": 115,
    "accountName": "Test115",
    "accountParentCode": 11,
    "accountParentId": 149,
    "accountChields": null
  }
]

I'm new to using .Net technology, is there a way to accomplish this task or do I have to change the way I think about setting up the database?

Thak you;

self-reference entity-framework-core asp.net-core-webapi c#
2 Answers

a table with self relationship but get duplicate records

You can find all the root elements first, and then find the corresponding child elements in turn.

In addition, you do not need the AccountParentCode property, you can get the ParentCode of the parent element through AccountParent.

Below is an example, you can refer to it.

Controller

[Route("api/[controller]")]
[ApiController]
public class ValuesController : ControllerBase
{
    public DailyCoreMVCDemoContext db;
    public ValuesController(DailyCoreMVCDemoContext _db)
    {
        db = _db;
    }
    [HttpGet]
    public async Task<string> test()
    {
        var allrootAccount = await db.Accounts.Where(m => m.AccountParentId == null).ToListAsync();
        var test = await db.Accounts.ToListAsync();
        allrootAccount.ForEach(t =>
        {
            t.AccountChields = GetSubordinateAccounts(test, t);
        });
        string result = JsonConvert.SerializeObject(allrootAccount, Formatting.None,
                    new JsonSerializerSettings()
                    {
                        ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                    });
        return result;
    }
    //a recursive method to get all subordinate Accounts
    public static List<Account> GetSubordinateAccounts(List<Account> allAccounts, Account superordinateAccount)
    {
        var result = new List<Account>();
        var subordinate = allAccounts.Where(a => a.AccountParentId == superordinateAccount.AccountId & a.AccountId != a.AccountParentId).ToList();
        if (subordinate.Count != 0)
        {
            result.AddRange(subordinate);
            foreach (var subo in subordinate)
            {
                result.AddRange(GetSubordinateAccounts(subordinate, subo));
            }
        }
        return result;
    }
}

Result enter image description here

user_161115300

It's better to clean the data in database but you can use this code to exclude duplicate data :

 _context.Accounts.Distinct().ToListAsync();
user_160835920
Related