CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-sql
blgrnboy
blgrnboy

Reputation: 5157

EF LINQ - Select only parent Entity

I'm faced with an interesting issue.

I have a Node entity. Nodes may or may not have Children, and also may or may not have a parent.

For my UI, when a user searches for a Node, I'd like to only return the Parent if the search results contain both the parent and one or many of its children (since the Parent has a Children Navigation Property).

My result when I search for a name that contains "ParentA":

Desired result:

This is my current LINQ Query:

var results = await dbContext.Nodes.
                        Include(c => c.Parent).
                        Where(c => c.NodeName.Contains(query)).
                        OrderBy(n => n.NodeName).
                        ToPagedListAsync(page ?? 1, pageSize);

Node Entity:

public class Node
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public long Id { get; set; }

    [Required, MaxLength(50)]
    [DisplayName("Node Name")]
    public string NodeName { get; set; }

    [ForeignKey("Parent")]
    public Nullable<long> ParentId { get; set; }
    public virtual Node Parent { get; set; }

    public virtual ICollection<Node> Children { get; set; }
}

Upvotes: 1

Views: 1151

Answers (3)

SKG
SKG

Reputation: 152

please try this....

dbContext.Nodes
     .Where(c => c.NodeName.Contains(query))
     .Select(c=>GetParentOrSelf(c,query))
     .OrderBy(n => n.NodeName)
     .ToPagedListAsync(page ?? 1, pageSize);    


private Node GetParentOrSelf(Node n)
{
    if (n.Parent==null)
        return n;
    else
    {
        if (n.Parent.NodeName.Contains(query))
            return GetParentOrSelf(n.Parent,query);
        else
            return n;
    }
}

Upvotes: 0

Akash Kava
Akash Kava

Reputation: 39916

You don't need to include parent, you can search children and return the parent. If you need, you can return children as well.

dbContext.Nodes.Where(
     n =>n.NodeName.Contains(query)) || n.Children.Any(
         c=>c.NodeName.Contains(query))).
                    OrderBy(n => n.NodeName).
                    ToPagedListAsync(page ?? 1, pageSize);

Upvotes: 0

Aducci
Aducci

Reputation: 26634

You can use the Any method like this: c.Children.Any(x => x.NodeName.Contains(query))

var results = await dbContext.Nodes.
                        Include(c => c.Parent).
                        Where(c => c.NodeName.Contains(query)).
                        Where(c => c.Children.Any(x => x.NodeName.Contains(query))).
                        OrderBy(n => n.NodeName).
                        ToPagedListAsync(page ?? 1, pageSize);

Upvotes: 1

Related Questions