I have a query to search in single table where records are at Parent, Child Relationship like below

so my query is like

var projects = from p in _projectSetupRepository.Table
                           from all in _projectSetupRepository.Table
                           where p.Status == status && p.ParentId == null &&
                                 all.Status == status && ((all.ParentId == null && all.Id == p.Id) || all.ParentId == p.Id)
                           select p;

if (!string.IsNullOrEmpty(search))
                projects = projects.Where(c => c.Name.Contains(search)).OrderBy(c => c.Name);

but I don't get actual results of parents if search with the child's name. What was the issue in the query?

PS

table contains thousands of data and performance is very important

PS

public class ProjectSetup 
    {

        public int Id { get; set; }
        public int? ParentId { get; set; }
        public string Name { get; set; }
        public bool Status { get; set; }
        public ProjectSetup Project{ get; set; }
        public virtual ICollection<ProjectSetup> SubProjects { get; set; }
   
    }

CodePudding user response：

You can find all parents with specific child name(this query searches Level1 childs only):

var status = true;
var search = "T";
var projects = (from parent in context.Projects
                join child in context.Projects on parent.Id equals child.ParentId into joinedT
                from pd in joinedT.DefaultIfEmpty()
                where parent.Status == status 
                && parent.ParentId == null //filter top level parents only?
                && pd.Name.Contains(search)
                select parent).Distinct().OrderBy(c => c.Name);
foreach(var p in projects)
{
    Console.WriteLine(p.Id ":" p.Name);
}

Console.WriteLine("Found results:"  projects.Count());

here's fiddle: https://dotnetfiddle.net/PaCidA

If you are looking for multi-level solution I suggest you to take a look at hierarchyid data type and its usage in EF LINQ
https://softwarehut.com/blog/tech/hierarchyid-entity-framework
https://kariera.future-processing.pl/blog/hierarchy-in-the-entity-framework-6-with-the-hierarchyid-type/

CodePudding user response：

Check for performance the folloging algorithm. Idea that we can Include several Project and check on the client what to load again. Algorithm uses EF Core navigation properties fixup.

var query = in_projectSetupRepository.Table
    .Include(p => p.Project.Project.Project) // you can increase count  of loaded parents
    .Where(p => p.Status == status)
    .AsQueryable();

var loadedDictionary = new Dictionary<int, ProjectSetup>>();

var projects = query;

if (!string.IsNullOrEmpty(search))
    projects = projects.Where(c => c.Name.Contains(search));

while (true)
{
    var loaded = projects.ToList();

    // collect loaded with parents
    foreach(var p in loaded)
    {
        var current = p;
        do 
        {
            if (!loadedDictionary.ContainsKey(current.Id))
                loadedDictionary.Add(current.Id, current);

            current = current.Project;
        }
        while (current != null)
    }

    // collect Ids of incomplete
    var idsToLoad = loadedDictionary.Values
        .Where(x => x.ParentId != null && x.Project == null)
        .Select(x => x.ParentId.Value)
        .ToList();

    //  everything is ok
    if (idsToLoad.Count == 0)
        break;

    // make query to load another portion of objects
    projects = query.Where(p => idsToLoad.Contains(p.Id));
}

var result = loadedDictionary.Values
    .OrderBy(c => c.Name)
    .ToList();