How can I filter parent entity where children entity contains value of another entity selected entities?

Tobias Gustafsson 0
Viewed 56

I have a parent entity with one to many children entity. First get a list of parent - child data from EF core. Then I try to run a Linq query against this list so I don't need to call SQL server on every filter change. This is done in C# and Blazor server.

Childentity :
public int Id { get; set; }
public string Text { get; set; }  // the user could enter this text free, like a tag
public int ParentId { get; set; }
...

FilterEntity : (not in database, this is generated from childentity )
public string Text { get; set; }
public bool Selected { get; set; }
...

I'm trying to make a single line Linq query where I get a list of parent entity that has a children entity property "Text" that match any of Filterentity "Text" where Selected is true. I don't know where to start. Maybe this isn't doable in a single line query?

I tried several similiar to

Parent.Where(i => i.Child.All(w => w.Text.Contains(z => FilterEntity
  .Where(w => w.Selected == true)))).ToList()

without any luck.

[EDIT] Firo's solution with works but not all the way.

Parent.Where(i => i.Childen.All/.Any(w => Filters.Where(f => f.IsSelected).Select(f => f.Text).Contains(w.Text))).ToList()

Lets say I have

Parent1 with childs 
{ Id = 1, Text = "C#", ParentId = 1 }
{ Id = 2, Text = "Pascal", ParentId = 1 }
{ Id = 3, Text = "Cobol", ParentId = 1 }

Parent2 with childs
{ Id = 4, Text = "C#", ParentId = 2 }
{ Id = 5, Text = "Pascal", ParentId = 2 }

Parent3 with child
{ Id = 6, Text = "C#", ParentId = 3 }

And my filterentity "C#" IsSelected I got :

.All :
Only parent3
.Any :
All 3 parents

Here I will have what .Any result in, all 3 parents.

But with Filterentity "C#" and "Pascal" IsSelected I got :

.All :
Only parent2
.Any :
All 3 parent

Here I want to get parent1 and parent2.

linq entity-framework c# filter
1 Answers

one line

var result = Parents
    .Where(i => Filters.All(f => !f.IsSelected || i.Children.Select(c => c.Text).Contains(f.Text)))
    .ToList();

more efficient 2 lines

var activeFilters = Filters.Where(f => f.IsSelected).Select(f => f.Text).ToList();
var result2 = Parents
    .Where(i => activeFilters.All(f => i.Children.Select(c => c.Text).Contains(f)))
    .ToList();
Firo0
Related