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](http://stackoverflow.com/questions/5400212/syntax-of-anonymous-type-without-explicit-keys-new-identifier) about the particular syntax. — , Jun 21 '12 at 18:23
Well, you might be able to do something fancy with reflection to build a dynamic `Expression>` and use that expression as your select expression. How exactly you would do that, I don't know. — cadrell0, Jun 21 '12 at 18:27
Oops, I am sleepy, by my own admission, `new { c, .. }` is effectively the same as `new { Customer = c, .. }` — , Jun 21 '12 at 18:33
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, Jun 21 '12 at 21:08
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, Jun 21 '12 at 21:45

score 1 · Answer 1 · answered Jun 24 '12 at 14:54

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

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

I think that's impossible. You can only do this in the way you showed in the question: select new { c, j }; or by listing all the columns.

There're many other threads on this:

edited May 23 '17 at 12:22

Community

1
1

answered Jun 23 '12 at 23:04

Marcin Robaszyński

772
1
11
21

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

2 Answers2

Linked