How to filter nested entities of the same type?

user_13485806 0
Viewed 315

The following is a simplified version of my actual situation. let's say I have this Person entity:

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    // etc.

    // Some navigation properties
    public virtual ICollection<Thing> Things { get; set; }
    // etc.
}

And I wrote an extension method to filter based on one or more properties:

public static IQueryable<Person> Filter(this IQueryable<Person> query,
                                        string name = null, int? thingId = null, 
                                        Foo etc = null)
{
    if (!string.IsNullOrEmpty(name))
        query = query.Where(p => p.Name.ToLower().Contains(name.ToLower()));

    if (thingId.HasValue)
        query = query.Where(p => p.Things.Count > 0 && 
                                 p.Things.Any(t => t.Id == thingId.Value));
    // etc.

    return query;
}

..which I can use like this:

var query = context.People.Filter(name, thingId);
var filteredPeople = query.Include(p => p.Things).Include(__).OrderBy(__).ToList();

I wanted to make Person a nested entity (i.e., each person has a collection of persons). So, I added the following properties:

public virtual ICollection<Person> Children { get; set; }
[ForeignKey("Parent")]
public int? ParentId { get; set; }
public virtual Person Parent { get; set; }

And now I'm struggling to implement the filtering logic. What I need is:

  • A parent Person is to be included if it matches the filters or if one of its descendants does.
  • A child Person is included only if it meets the above criteria.

For the second problem, I will probably try the solution in this answer but I need to solve the first problem first. I tried to create a recursive expression like below:

private static IQueryable<Person> FilterByName(this IQueryable<Person> query, string name)
{
    if (string.IsNullOrEmpty(name)) return query;

    Expression<Func<Person, bool>> selector = (p) => 
        p.Name.ToLower().Contains(name.ToLower()) 
        || p.Children.AsQueryable().FilterByName(name).Any();

    return query.Where(selector);
}

..but I get an exception saying that it "cannot be translated into a store expression".

The only other solution I could think of is to recursively iterate the children's tree and try to construct the list manually, which is inefficient because it will require too many queries.

How can I efficiently filter the collection of Person and their descendants?

entity-framework-6 entity-framework linq c#
1 Answers

We need to create a query that achieves the following:

  1. A parent Person is to be included if it matches the filters or if one of its descendants does.

  2. A child Person is included only if it meets the above criteria.

If we invert our way of thinking, and realize the fact there are no parent or children entities, only people, we can rewrite our criteria to say the following:

  1. If an person matches the filters, we want to include that person and all of it's ancestors.

This greatly simplifies the query we need to execute and gives identical results.

Now, as some comments have indicated, Entity Framework doesn't have a lot built in as far as hieratical queries are concerned. This doesn't mean that we can't use EF, but we do need to write and execute a raw SQL query.

EF will still take care of the object mapping, and change tracking if you want to save entities to the database.

This doesn't have the ability to be further extended by Linq such as .Where() or .Include() as it is a non-compostable query.

public static IEnumerable<Person> FilterPeople(this DbSet<Person> people, string name)
{
    return people.FromSqlRaw(
        "WITH child AS (" +
        "    SELECT * FROM People" +
        "    WHERE Name LIKE {0}" +
        "    UNION ALL" +
        "    SELECT parent.* FROM People parent" +
        "    INNER JOIN child ON parent.Id = child.ParentId" +
        ") SELECT * FROM child",
        $"%{name}%")
        .AsEnumerable();
}

This translates into the parameterized query:

exec sp_executesql N'WITH child AS (SELECT * FROM People WHERE Name LIKE @p0 UNION ALL SELECT parent.* FROM People parent INNER JOIN child ON parent.Id = child.ParentId) SELECT * FROM child',N'@p0 nvarchar(4000)',
@p0=N'%Dick%'
user_68249550
Related