LINQPad: How do I add a property to each row being dumped?

Question

In LINQPad, I frequently want to Dump some SQL table, but add a property to each row. I usually default to the simple:

Publishers
.Select(p => new { p, AppCount = p.Apps.Count() })

But of course this results in messy output, because each item is formatted as a vertical table, with an extra column containing the added property. I could create a new object, but that's very verbose, and you lose the table traversal links:

Publishers
.Select(p => new { p.PublisherId, p.Name, p.Added, ..., AppCount = p.Apps.Count() })

What I'd love is some sort of merge syntax, which creates a dynamic object to dump with the added property:

Publishers
.Select(p => p.Merge(new { AppCount = p.Apps.Count() })

I've tried a few different ways to accomplish that: using JsonConvert.SerializeObject (this goes haywire trying to serialize all the foreign key references and repeating loops, using ExpandoObject (couldn't come up with a nice clean syntax), and libraries like https://github.com/kfinley/TypeMerger, which I haven't gotten to work either.

Has anyone come up with a clean way to do this? Does LINQPad have some built in synax for this I can use? Thanks!

UPDATE: I uploaded a sample script to demonstrate the issue and let anyone try their solutions against: http://share.linqpad.net/kclxpl.linq

To run, you'll need to populate the Nutshell database, which you can do using the "Populate demo database" sample in LINQPad.

Papoose

(Temporarily) Add a prop to the original object? public int AppCount => Apps.Count() ? – Castleman

I don't control the original object, it's autogenerated from the SQL db – Papoose

I must be misunderstanding what you're using linqpad for; I thought it was some temporary sounding out tool.. – Castleman

In this case I'm using it for LINQ-to-SQL queries, but trying to add dynamic computed properties to the structured table results – Papoose

Answer 1

This is a tricky problem to solve. Part of the problem is that the metadata that allows LINQPad to identity lazily loaded properties is lost when they're taken out of their "entity" home and moved to an ExpandoObject, causing excessive round-tripping.

To avoid the pain, I've added a Util.Merge method to LINQPad 7.2.7. Here's its simplest use:

var o1 = new { A = 1, B = 2 };
var o2 = new { C = 3, D = 4 };

Util.Merge (o1, o2).Dump();

and for the problem at hand:

Publishers
   .Select(p => Util.Merge (p, new { AppCount = p.Apps.Count() }))

Bradfordbradlee

Is it possible to setup LINQPad to automatically update on the beta "channel"? – Carcinogen

I was hoping you'd drop in @Joe Albahari, thanks, this works like a charm! What lucky timing for me too that you built this right before I asked the question too! – Papoose

@MetMage - when you download a LINQPad beta, it automatically updates to the latest beta. – Bradfordbradlee

Answer 2

You can add some extension methods to make this work. I do not recommend adding these to My Extensions because they require importing System.Dynamic which you probably don't want for most queries. Put these in another query which imports System.Dynamic and #load it when desired.

public static class ExpandoObjectExt {
    // convert object to ExpandoObject, add a property and return as dynamic
    public static dynamic With<T, TAdd>(this T obj, string propName, TAdd val) {
        var eo = Util.ToExpando(obj);
        var objDict = (IDictionary<string, object>)eo;
        objDict[propName] = val;
        return eo;
    }
    // convert IEnumerable<T> to IEnumerable<dynamic>, adding a property to each object
    public static IEnumerable<dynamic> With<T, TAdd>(this IEnumerable<T> src, string propName, Func<T, TAdd> valFn) => src.Select(s => s.With(propName, valFn(s)));
}

Now you can use this like:

Publishers
    .With("AppCount", p => p.Apps.Count())

However, conversion to ExpandoObject will eliminate any hyperlinks for collection properties, you can get around this by specifying only one level of output with an explicit .Dump(1). You could also write a custom Util.ToExpando (the code is here) that uses Util.OnDemand to create hyperlinks for collection properties.

Also, if you want to add more than one property, you can write variations of With that take multiple pairs or a version that tests for an ExpandoObject (and IEnumerable<ExpandoObject>) and can be chained fluent style.

Carcinogen

I appreciate the answer! However, it seems to have the significant problem that under the covers every row ends up resulting in its own SQL call. So for example: Publishers .Take(10) .With("AppCount", p => p.Apps.Count()).Dump(1); Results in 11 SQL calls, one for the initial Publishers and then one each to get all the Apps for each Publisher. Contrast that with the naïve approach, which yields just one joined SQL call. – Papoose

@Papoose - This is an issue with Util.ToExpando. Just call .ToArray() firs to get rid of it. – Stinkhorn

@Papoose This is because LINQ to databases is unable to translate the With method and doesn't know what related information may be needed. Unfortunately even if you added Include it would pull a lot of data when you just want Count. Perhaps the best case is to use a flatten method after you do the naive Select. – Carcinogen

Answer 3

I've got a slight variation on NetMage's extension method. Try this instead:

public static object Extendo(this object @object, Action<dynamic> inject)
{
    dynamic e = Util.ToExpando(@object);
    inject(e);
    return (object)e;
}

The Action<dynamic> allows this code to be written:

var people = new[]
{
    new { name = "Fred", age = 41 },
    new { name = "John", age = 67 },
    new { name = "Dave", age = 23 },
};

people
    .Select(p => p.Extendo(d => d.IsOld = p.age >= 55))
    .Dump();

I can extend this slightly again with this method:

public static object Extendo<T>(this IEnumerable<T> source, Action<T, dynamic> inject) =>
    source.Select(x => x.Extendo(d => inject(x, d)));

Now I can write this:

people.Extendo((p, d) => d.IsOld = p.age >= 55).Dump();

I get the same result.

Of course, these methods effectively return object so there's no further computation that can be done. So it's probably worthwhile just going straight for Dump. Try this:

public static void Dump<T>(this IEnumerable<T> source, Action<T, dynamic> inject) =>
    source.Select(x => x.Extendo(d => inject(x, d))).Dump();

Now you just write this:

people.Dump((p, d) => d.IsOld = p.age >= 55);

Stinkhorn

I appreciate the answer! However, it seems to have the significant problem that under the covers every row ends up resulting in its own SQL call. So for example: Publishers .Take(10) .Dump((p, d) => d.AppCount = p.Apps.Count()); Results in 11 SQL calls, one for the initial Publishers and then one each to get all the Apps for each Publisher. Contrast that with the naïve approach, which yields just one joined SQL call. – Papoose

@Papoose - That would be an issue with Util.ToExpando and not my code, as such. Try putting a .ToArray() call in first. That should stop that. It's what you should do before a dump anyway. – Stinkhorn