I wrote a simple recursive function to climb up the tree of a table that has ID and PARENTID.

But when I do that I get this error

System.InvalidOperationException: 'The instance of entity type 'InternalOrg' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.

Is there another way to do this or maybe done in one LINQ expression ?

private InternalOrgDto GetInternalOrgDto(DepartmentChildDto dcDto)
{
    if (dcDto.InternalOrgId != null)
    {
        InternalOrg io = _internalOrgRepo.Get(Convert.ToInt32(dcDto.InternalOrgId));
        InternalOrgDto ioDto = new InternalOrgDto
        {
            Id = io.Id,
            Abbreviation = io.Abbreviation,
            Code = io.Code,
            Description = io.Description
        };

        return ioDto;
    }
    else
    {
        //manually get parent department
        Department parentDepartment = _departmentRepo.Get(Convert.ToInt32(dcDto.ParentDepartmentId));
        DepartmentChildDto parentDepartmenDto = ObjectMapper.Map<DepartmentChildDto>(parentDepartment);

        return GetInternalOrgDto(parentDepartmenDto);
    }
}

CodePudding user response：

Is there a way to get a top-level parent from a given child via Linq? Not that I am aware of. You can do it recursively similar to what you have done, though I would recommend simplifying the query to avoid loading entire entities until you get what you want. I'm guessing from your code that only top level parent departments would have an InternalOrg? Otherwise this method would recurse up the parents until it found one. This could be sped up a bit like:

private InternalOrgDto GetInternalOrgDto(DepartmentChildDto dcDto)
{
    var internalOrgid = dcDto.InternalOrgId 
        ?? FindInternalOrgid(dcDto.ParentDepartmentId) 
        ?? throw new InternalOrgNotFoundException();

    InternalOrgDto ioDto = _context.InternalOrganizations
        .Where(x => x.InternalOrgId == internalOrgId.Value)
        .Select(x => new InternalOrgDto
        {
            Id = x.Id,
            Abbreviation = x.Abbreviation,
            Code = x.Code,
            Description = x.Description
        }).Single();
    return ioDto;
}

private int? FindInternalOrgid(int? departmentId)
{
    if (!departmentId.HasValue)
       return (int?) null;

    var details = _context.Departments
        .Where(x => x.DepartmentId == departmentId.Value)
        .Select(x => new 
        { 
            x.InternalOrgId,
            x.ParentDepartmentId
        }).Single();
     if (details.InternalOrgId.HasValue)
         return details.InternalOrgId;

     return findInternalOrgId(details.parentDepartmentId);
}

The key considerations here are to avoid repository methods that return entities or sets of entities, especially where you don't need everything about an entity. By leveraging the IQueryable provided by EF through Linq we can project down to just the data we need rather than returning every field. The database server can accommodate this better via indexing and help avoid things like locks. If you are using repositories to enforce low level domain rules or to enable unit testing then the repositories can expose IQueryable<TEntity> rather than IEnumerable<TEntity> or even TEntity to enable projection and other EF Linq goodness.

Another option to consider where I have hierarchal data where the relationships are important and I want to quickly find all related entities to a parent, or get to a specific level, one option is to store a breadcrumb with each record which is updated if that item is ever moved. The benefit is that these kinds of checks become very trivial to do, the risk is that anywhere/anything that can modify data relationships could leave the breadcrumb trail in an invalid state.

For example, if I have a Department ID 22 which belongs to Department 8 which belongs to Department 2 which is a top-level department, 22's breadcrumb trail would be: "2,8". If the breadcrumbs are empty we have a top-level entity. (and no parent Id) We can parse the breadcrumbs using a simple string.Split() operation. This avoids the recursive trips to the DB entirely. Though you may want a maintenance job running behind the scenes to periodically inspect recently modified data to ensure their breadcrumb trails are accurate and alerting you if any get broken. (Either by faulty code or such)