Using system.linq.dynamic to join tables

Question

Is there a way to use system.linq.dynamic to join tables using a string for the tablename ?

It seems like this would be basic but all of googling points to some extremely complicated answers extending the functionality of linq which seems like I am just missing something simple.

My application allows people to build filters on data by adding criteria and fields dynamically from multiple tables.

I have an ArrayList of fields in the format "TableName.FieldName" as strings. The dynamic query has to be created using this list of strings.

I have a main table called Table1 as the base starting point. So while building a query they always start there but can add fields from other tables after. if any of the other tables are included it must do a join back to Table1 using the primary key.

Using system.linq.dynamic building the where clause appears to be very easy.

var query = dbcontext.Table1;
Dictionary<string, ArrayList> reportTables; //store table and its respective field names as an array
query.Select(string.Join(",", reportTables["Table1"].ToArray()));

But now how can I easily join the child tables?

I started with looping through the tables and if its not the main table I want to add a join like this:

  if(reportTables.Keys.Count > 1){
                    // add joins
                    foreach(string tblName in reportTables.Keys)
                    {
                        if(tblName != "Table1")
                        {
                            query.Join(tblName, "Table1.IDField", tblName + ".Table1IDField")
                        }
                    }
                }

UPDATE:

---

Thanks to @NetMage I was able to get the app to compile and working by doing

var query = (IQueryable)db.Table1;

Dictionary<string, IQueryable> tableTypeDictIQ = new Dictionary<string, IQueryable>()
                {
                    { "Table2", db.Table2},
                    { "Table3", db.Table3 }
}

if (reportTables.Keys.Count > 1)
            {
                // add joins
                var joinCount = 0;
                foreach (string tblName in reportTables.Keys)
                {
                    if (tblName != "Table1")
                    {
                        if (joinCount == 0)
                            query = query.Join(tableTypeDictIQ[tblName], "RECDNO", "RECDNO", "new(outer as Table1,inner as Table2)");
                        else
                            query = query.Join(tableTypeDictIQ[tblName], "Table1.RECDNO", tblName + ".RECDNO", $"new({string.Join(",", Enumerable.Range(1, joinCount + 1).Select(n => $"outer.Table{n} as Table{n}"))}, inner as Table{joinCount + 2})");
                        ++joinCount;
                    }
                }
            }

Answer 1

You can use Reflection to retrieve the tables from the DataContext.

With these extension methods defined:

public static class ObjectExt {
    public static object GetValue(this object obj, string memberName) =>
        obj.GetType().GetPropertyOrField(memberName).GetValue(obj);

    public static TRes GetValue<TRes>(this object obj, string memberName) =>
        obj.GetType().GetPropertyOrField(memberName).GetValue<TRes>(obj);
}

public static class MemberInfoExt {
    public static object GetValue(this MemberInfo member, object srcObject) {
        switch (member) {
            case FieldInfo mfi:
                return mfi.GetValue(srcObject);
            case PropertyInfo mpi:
                return mpi.GetValue(srcObject);
            case MethodInfo mi:
                return mi.Invoke(srcObject, null);
            default:
                throw new ArgumentException("MemberInfo must be of type FieldInfo, PropertyInfo or MethodInfo", nameof(member));
        }
    }
    public static T GetValue<T>(this MemberInfo member, object srcObject) => (T)member.GetValue(srcObject);
}

public static class TypeExt {
    public static MemberInfo GetPropertyOrField(this Type t, string memberName, BindingFlags bf = BindingFlags.Public | BindingFlags.Instance) =>
        t.GetMember(memberName, bf).Where(mi => mi.MemberType == MemberTypes.Field || mi.MemberType == MemberTypes.Property).Single();
}

You can do:

var query = (IQueryable)dbcontext.Table1;
if (reportTables.Keys.Count > 1) {
    // add joins
    var joinCount = 0;
    foreach (string tblName in reportTables.Keys) {
        if (tblName != "Table1") {
            if (joinCount == 0)
                query = query.Join(dbcontext.GetValue<IQueryable>(tblName.Dump("Join tblName")), "IDField", "Table1IDField", "new(outer as Table1,inner as Table2)");
            else
                query = query.Join(dbcontext.GetValue<IQueryable>(tblName), "Table1.IDField", tblName + ".Table1IDField", $"new({String.Join(",", Enumerable.Range(1, joinCount + 1).Select(n => $"outer.Table{n} as Table{n}"))}, inner as Table{joinCount+2})".Dump("Select"));
            ++joinCount;
        }
    }
}

var ans = query.Select(("new("+string.Join(",", reportTables.Keys.SelectMany(t => reportTables[t].Cast<string>().Select(f => $"{t}.{f}")).ToArray().Dump("fields"))+")").Dump("Select"));

This will create a flattened anonymous object containing all the join tables as new { T1, T2, T3, ... } when done, for further querying.

If performance is a consideration, instead of using my extension method, you could also create a Dictionary to map strings to tables, or use one of the high speed Reflection property libraries.

Create a map with something like:

var tableMap = new Dictionary<string, IQueryable>() {
                    { "Table2", Table2 },
                    { "Table3", Table3 }
                };