Q:

Build an lambda Expression tree with a specific field in a linked entity

Data MODEL
public class TABLE
    {
        [Key]
        public int ID { get; set; }
        public int ONEFIELD { get; set; }

        // -------------------- ForeignKey --------------------
        [ForeignKey("People")]
        public long PeopleID { get; set; }
        public virtual People People { get; set; }
    }

    public class People
    {
        [Key]
        public long PeopleID { get; set; }
        public int CountryID { get; set; }
    }

I need to build a lambda to query this MODEL :

Get TABLE.ONEFIELD = 1 AND TABLE.PEOPLE.COUNTRYID = 6

LINQ equivalent
_context.TABLEs
  .Where(e => e.ONEFIELD == 1)
  .Include(e => e.People)
  .Where(i=>i.People.CountryID == 6);

My try
  public static Expression<Func<TEntity, bool>> BuildLambda<TEntity>(OBJTYPE obj)
        {

        var item = Expression.Parameter(typeof(TEntity), "table");
        Expression query = null;

        // 1
        var prop1 = Expression.Property(item, "ONEFIELD");
        var value1 = Expression.Constant(1);
        var equal1 = Expression.Equal(prop1, value1);
        var lambdaFIELDONE = Expression.Lambda<Func<TEntity, bool>>(equal1, item);
        query = lambdaFIELDONE.Body;

        // 2
        var prop2 = Expression.Property(item, typeof(People).Name + ".CountryID");
        var value2 = Expression.Constant(6);
        var equal2 = Expression.Equal(prop2, value2);
        var lambdaCOUNTRYID = Expression.Lambda<Func<TEntity, bool>>(equal2, item);
        query = Expression.And(query, lambdaCOUNTRYID);

    }

but I receive this error

System.ArgumentException: Instance property 'People.CountryID' is not defined for type 'SOLUTION.Models.TABLE'

I don't need Generic, just a fixed lambda (and I couldn't use LINQ).
I tried several things to catch People.CountryID like
Expression.Property(item1, typeof(People).GetProperty("CountryID"));
Expression.Property(item, typeof(People).Name+"." + typeof(People).GetProperty("CountryID"));
Expression.Property(item, typeof(People).Name + "." + typeof(People).GetProperties().Where(x => x.Name == "CountryID").FirstOrDefault().Name);

no success
Any ideas ? thanks

A:

So, to build a nested property access, you must nest the Expressions that access each level. Then you can combine the tests into a body and finally create the lambda for the result:
public static Expression<Func<TEntity, bool>> BuildLambda<TEntity>(OBJTYPE obj) {
    // (TEntity table)
    var parmTable = Expression.Parameter(typeof(TEntity), "table");

    // table.ONEFIELD
    var prop1 = Expression.Property(parmTable, "ONEFIELD");
    // table.ONEFIELD == 1
    var equal1 = Expression.Equal(prop1, Expression.Constant(1));

    // table.People
    var prop2_1 = Expression.Property(parmTable, nameof(People));
    // table.People.CountryID
    var prop2_2 = Expression.Property(prop2_1, "CountryID");
    // table.People.CountryID == 6
    var equal2 = Expression.Equal(prop2_2, Expression.Constant(6));

    // table.ONEFIELD == 1 && table.People.CountryID == 6
    var finalBody = Expression.AndAlso(equal1, equal2);

    // table => table.ONEFIELD == 1 && table.People.CountryID == 6
    return Expression.Lambda<Func<TEntity, bool>>(finalBody, parmTable);
}

Using LINQPad, you could create a sample lambda and then use the Dump method and you would see that a nested FieldExpression is created, which is what gets created when you call Expression.Property:
Expression<Func<TEntity, int>> f = t => t.People.CountryID;

f.Dump();

