Building dynamic where query from linq to ef core

Question

My model looks like below

public class Country
{
  public int Id {get; set;}
  public string Name {get; set;}
}

public class User
{
 public int Id{get; set;}
 public string FirstName {get; set;}
 public string LastName {get; set;}
 public string Email {get; set;}
}

public class Company
{
  public int Id{get; set;}
  public string Name {get;set;}
  public string City {get; set;}
  public string Address1 {get; set;}
  public string Address2 {get; set;}
  [ForeignKey("Country")]
  public int CountryId{get; set;}

  public Country Country{get; set;}
  public ICollection<CompanyUsers> CompanyUsers {get; set;}
}

public class CompanyUsers
{
 public int Id{get; set;}
 [ForeignKye("Company")]
 public int CompanyId {get; set;}
 [ForeignKye("User")]
 public int UserId {get; set;}

 public Country Country{get; set;}
 public User User{get; set;}
}

I want to let user to be able to search Company record either by any property defined in Country, Company or User class except Id property

I tried looking at this SO discussion but it doesn't handle navigation property. Could someone help me to build dynamic where clause including properties from navigation property.

With this implementation I can only do as below

myContext.CompanyEntity
.Where(FilterLinq<CompanyEntity>
.GetWherePredicate(searchParams))
.ToList();

public class SearchField
{
  public string Key { get; set; }
  public string Value { get; set; }
}

Answer 1

Expanding on the idea from answer you pointed to is not that complicated.

You need to change assumption so the property name in SerchField can hold definition of property path like Company.City or Company.Country.Name as well as just property FirstName. And you need to handle it:

so instead of just having simple property like so:

Expression columnNameProperty = Expression.Property(pe, fieldItem.Name);

you need to handle property chain:

Expression columnNameProperty = GetPropertyExpression(pe, fieldItem.Name);


Expression GetPropertyExpression(Expression pe, string chain){
    var properties = chain.Split('.');
    foreach(var property in properties)
          pe = Expression.Property(pe, property);

    return pe;
}

Basically what this code does it applies properties over previous modifying pe variable on every loop creating property chain. Entity framework will process it and create appropriate joins. This will work only for single relations and not for collections.

so the modified code from that answer looks like this:

public class FilterLinq<T>
{
    Expression GetPropertyExpression(Expression pe, string chain)
    {
        var properties = chain.Split('.'); 
        foreach(var property in properties)
           pe = Expression.Property(pe, property);

        return pe;
    }

    public static Expression<Func<T, Boolean>> GetWherePredicate(params SearchField[] SearchFieldList)
    {

        //the 'IN' parameter for expression ie T=> condition
        ParameterExpression pe = Expression.Parameter(typeof(T), typeof(T).Name);

        //combine them with and 1=1 Like no expression
        Expression combined = null;

        if (SearchFieldList != null)
        {
            foreach (var fieldItem in SearchFieldList)
            {
                //Expression for accessing Fields name property
                Expression columnNameProperty = GetPropertyExpression(pe, fieldItem.Name);

                //the name constant to match 
                Expression columnValue = Expression.Constant(fieldItem.Value);

                //the first expression: PatientantLastName = ?
                Expression e1 = Expression.Equal(columnNameProperty, columnValue);

                if (combined == null)
                {
                    combined = e1;
                }
                else
                {
                    combined = Expression.And(combined, e1);
                }
            }
        }

        //create and return the predicate
        return Expression.Lambda<Func<T, Boolean>>(combined, new ParameterExpression[] { pe });
    }

}