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.

Answer 1

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