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.

(Temporarily) Add a prop to the original object? `public int AppCount => Apps.Count()` ? — Caius Jard, Feb 08 '22 at 18:05
I don't control the original object, it's autogenerated from the SQL db — UnionP, Feb 08 '22 at 23:20
I must be misunderstanding what you're using linqpad for; I thought it was some temporary sounding out tool.. — Caius Jard, Feb 08 '22 at 23:25
In this case I'm using it for LINQ-to-SQL queries, but trying to add dynamic computed properties to the structured table results — UnionP, Feb 08 '22 at 23:27

score 6 · Accepted Answer · answered Feb 09 '22 at 09:05

6

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() }))

answered Feb 09 '22 at 09:05

Joe Albahari

30,118
7
80
91

Is it possible to setup LINQPad to automatically update on the beta "channel"? – NetMage Feb 09 '22 at 19:04
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! – UnionP Feb 09 '22 at 22:03
@MetMage - when you download a LINQPad beta, it automatically updates to the latest beta. – Joe Albahari Feb 10 '22 at 01:10

NetMage · Answer 2 · 2022-02-08T22:51:48.910

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.

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. — UnionP, Feb 08 '22 at 23:14
@UnionP - This is an issue with `Util.ToExpando`. Just call `.ToArray()` firs to get rid of it. — Enigmativity, Feb 09 '22 at 00:47
@UnionP 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`. — NetMage, Feb 09 '22 at 19:04

Enigmativity · Answer 3 · 2022-02-08T22:00:17.087

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);

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. — UnionP, Feb 08 '22 at 23:15
@UnionP - 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. — Enigmativity, Feb 09 '22 at 00:46

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

3 Answers3

Linked