How to flatten tuples in LinqPad grid result?

Question

Frequently one wants to display the complete result of simple joins of database tables:

from x in tablex
join y in tabley on x.id equals y.id
select new { x, y }

The result of that, however, is not what you get with the equivalent SQL statement:

SELECT * FROM tablex x JOIN tabley y ON x.id = y.id

The SQL gives me one result table with all the columns from both tables, while LinqPad will return a table with only two columns, a click to each is needed to expand the respective objects.

Is there a simple way to get the same output I get from the analogous SQL in LinqPad?

Answer 1

You can get all object fields/properties using Reflection and fill them into one System.Dynamic.ExpandoObject

Here is simple extension method doing this: LINQPad script

or

void Main()
{
    new {x=1, 
        y = new { a = "a", b = "b" },
        DateTime.Now
    }
    .Dump("Original")
    .Flatten(1,false)
    .Dump("Flattened")
    ;
}

public static class Flattener {
    public static object Flatten(this object o, int level = 1, bool includeParent = false){
        if( level == 0 ) return o;
        var members = o.GetMemberValues(level, includeParent);
        var d = new System.Dynamic.ExpandoObject() as IDictionary<string,object>;
        foreach(var p in members) d.Add( p.Key, p.Value );
        return d;
    }

    private static IEnumerable<KeyValuePair<string,object>> GetMemberValues(this object o, int level = 1, bool includeParent = true, string keyPrefix = null){
        if( level < 0 ) yield break;

        var properties = 
            from p in o.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance)
            let get = p.GetGetMethod()
            where get != null && get.GetParameters().Length == 0
            select new { p.Name, Value = get.Invoke(o, null) } 
        ;
        var fields = 
            from p in o.GetType().GetFields(BindingFlags.Public | BindingFlags.Instance)
            select new { p.Name, Value = p.GetValue(o) } 
        ;

        foreach(var m in new []{ properties, fields }.SelectMany(x=>x)) {
            string mkey = keyPrefix == null ? m.Name : keyPrefix + "." + m.Name;
            bool empty = true;
            foreach(var m2 in m.Value.GetMemberValues( level-1, includeParent, mkey)){
                yield return new KeyValuePair<string,object>( m2.Key, m2.Value);
                empty = false;
            }
            if(includeParent || empty || level == 0) yield return new KeyValuePair<string,object>( mkey, m.Value);
        }       
    }
}