Expression to get LINQ with Contains to EF for SQL IN() where on entities child's property equals value

Question

I have a simple need to filter all parents out of the returned collection where there is no match on a field, that is called by name from a string, doesn't match a value presented. What I am after is if parent object has child object, and that child objects property "foo"(called by string) doesn't or does equal a value bar, the parent object is filtered from the collection appropriately.

Here is my linq ef call

var field = "bar";
var values = new List<string>{"foo","fuYu"};
var dataPage = _aim_context.ae_s_bld_c.AsNoTracking();
var result = dataPage.Where(x => 
                              DbHelper.byPropertyContains(x.udfs, field, values)
                           );
// NOTE `udfs` is a ONE-to-ONE with `ae_s_bld_c`

What I am looking to see is something like the SQL of

SELECT [m].[id],[m.udfs].[bar],
FROM [dbo].[ae_s_bld_c] AS [m]
    INNER JOIN [dbo].[ae_s_bld_c_udf] AS [m.udfs]
        ON ([m].[multitenant_id] = [m.udfs].[multitenant_id])
WHERE ([m].[multitenant_id] = 1.0)
    AND ([m.udfs].[bar] IN ('foo','fuYu')) --< Goal line

The way I have approached this was to get an expression set up to take the List<string> and make the SQL. I have read near 50 articles and SO posts, but have not figured out exactly why I am not getting this just yet as everyone seems to have different ideas, and most are not in line with dotnet core 2.1+ it seems.

Here is what I am sitting at currently after many many iterations. NOTE: it is a little different from what I am after as I am giving my current trail.

My current context linq try

//...
dataPage = dataPage.Where(DbHelper.byPropertyContains<ae_s_bld_c>("udfs", field, values));
//...

I think it would be better if it was like the first example I put up, but that was what I have landed on since I have had a time lining it up with x=>x.udfs, both as x=> funName(x.udfs) and x=> x.udfs.funName()

My static method to build the expression

public static class DbHelper
{
    public static Expression<Func<T, bool>> byPropertyContains<T>(string node, string field, List<string> value) {
//trying to take parent item and get it's property by string name because
// doing the function in linq like x=>x.udfs was not working right
// but that is the prefered I think
        var property_parameter = Expression.Parameter(typeof(T), "x");
        var property = Expression.PropertyOrField(property_parameter, node);
        var selector_parameter = Expression.Parameter(property.Type, "y");
        var selector = Expression.PropertyOrField(selector_parameter, field);
        var methodInfo = typeof(List<string>).GetMethod("Contains", new Type[] {
            typeof(string)
        });
        var list = Expression.Constant(value, typeof(List<string>));
        var body = Expression.Call(methodInfo, list, selector);
        return Expression.Lambda<Func<T, bool>>(body, selector_parameter);
    }
}

Update

Per the request of @NetMage I have tried to work backwards with LINQpad. I think I am close but it is hard to tell with teh output. I am putting it up here for reference. To be clear, the property name of the child will be a string of the name. The best outcome is I could have a name like udfs.foo where I can test on any level if the values contain by string name, but really ok with it starting here,

var result = dataPage.Where(x => 
                              DbHelper.byPropertyContains(x.udfs, field, values)
                           );

Answer 1

Let start from here. You need an equivalent of something like this

var result = dataPage.Where(x => values.Contains(x.udfs.{field}));

where field is a string returning property dynamically specified by name.

In EF Core you don't even need to deal with building expresions by hand, because EF Core provides a special SQL translatable function for accessing simple properties by name called EF.Property.

With that method the solution is simple as that:

var result = dataPage
   .Where(x => values.Contains(EF.Property<string>(x.udfs, field)));