Join in LINQ that avoids explicitly naming properties in "new {}"?

Question

I would like to do something like this:

    DataTable q = from c in customers
            join o in orders on c.Key equals o.Key
            into outer
            from j in outer.DefaultIfEmpty()
            select new { c.*, j.* };

The closest I currently got is the following:

    var q = from c in customers
            join o in orders on c.Key equals o.Key
            into outer
            from j in outer.DefaultIfEmpty()
            select new { c, j };

I'd like my result (q) to have all the columns from c and j. both c and j contain a lot of columns, so I'd rather not list them as such:

            select new { c.col1, c.col2, etc. }

But I basically want the final DataTable to be made up of c.* and j.*.

This answer (not the accepted answer, but the one below it) works if I specify all the c and j columns inside 'select new': How to Convert a LINQ result to DATATABLE? But I'd like to avoid listing them all.

This isn't related to datasets. It is just how LINQ works (blasted strong typing with no support for this particular static analysis case!). One option might be: new { Customer = c, Order = j }. Note that new { c.col1 } is effectively new { col1 = c.col1 } by language-magic. Here is a question I asked about the particular syntax. — user166390
Well, you might be able to do something fancy with reflection to build a dynamic Expression<Func<T1, T2, T3>> and use that expression as your select expression. How exactly you would do that, I don't know. — cadrell0
Oops, I am sleepy, by my own admission, new { c, .. } is effectively the same as new { Customer = c, .. } — user166390
With all due respect, every time I read "avoid listing them all" in a question about LINQ or SQL, I get "lazy programmer" vibes. Sometimes you just need to sit down and crank out a few hundred lines of code, brute force style. — Nick Vaccaro
Norla, if I list all the columns, it means when either of original data-tables gets a new column, I'd have to add it here. It's the same issue as code duplication - a fix made to one function, won't exist in the other. And I'm not the one who would be changing the input DataTables, so that just complicates things. — Alex

Gert Arnold Gert Arnold · Accepted Answer · 2012-06-24T14:54:40

First, I would prefer to specify the columns manually, because I would like to minimize the impact of underlying layers - database schema, query provider - on my application. But if you really want to do this there is a bit of a hacky way to accomplish it.

When your are using entity framework (database first):

var qs = ((ObjectQuery)q).ToTraceString();
var ds = new DataSet();
var da = new SqlDataAdapter(qs, new SqlConnection(((EntityConnection)context.Connection).StoreConnection.ConnectionString));
da.Fill(ds, "Result");

The idea is to catch the emitted SQL before it is actually executed and use it to fill a DataTable (in a DataSet).

With Linq-to-sql is is basically the same, just the way to get the command string and connection string is different:

context.GetCommand(q).CommandText
context.Connection.ConnectionString

With EF Code-first:

q.ToString()
context.Database.Connection.ConnectionString

Join in LINQ that avoids explicitly naming properties in "new {}"?

2 Answers