Select All columns for all tables in join + linq join

Question

How to select all columns from tables in join using linq

Sql:

select CTRL_RUN_JOB.*, CTRL_DATA_STREAM.*
 from CTRL_RUN_JOB inner join CTRL_DATA_STREAM
      on CTRL_RUN_JOB.DATA_STREAM_ID= CTRL_DATA_STREAM.DATA_STREAM_ID

Linq:

from CTLJCRJOB in CTRL_RUN_JOBs 
join CTLRFDSTM in CTRL_DATA_STREAMs 
on CTLJCRJOB.DATA_STREAM_ID equals CTLRFDSTM.DATA_STREAM_ID
select  new {
         CTLJCRJOB.*  // ???
        ,CTLRFDSTM.*  // ???
}

Thanks

score 50 · Accepted Answer · answered Nov 04 '09 at 05:08

50

While you cant expand them to columns, you can simply return the entities. Eg:

select new { CTLJCRJOB, CTLRFDSTM }

If you need it flattened, then you will have to write out the mapping yourself, but will still be very trivial.

answered Nov 04 '09 at 05:08

leppie

112,162
17
191
293

2

what will be return type of a method who return this type of query result ? and how can i count the number of records of this query results retun – rahularyansharma Jan 16 '12 at 12:42
@rahularyansharma: The return type depends. In your case, it is probably best that you store the results into a list `toList()` and then you can use the `Count` property. Or if you call `Count()` on the query, it will do a `Select Count`. – surfasb Feb 05 '12 at 14:31
You can make a custom class to hold CTLJCRJob and CTLRFDSTM so you do not have to deal with returning an anonymous type. – ruffrey Jun 03 '13 at 17:43

score 10 · Answer 2 · edited Apr 22 '13 at 21:33

10

You could use the into clause, but it will not flatten it for you.

from CTLJCRJOB in CTRL_RUN_JOBs 
join CTLRFDSTM in CTRL_DATA_STREAMs 
on CTLJCRJOB.DATA_STREAM_ID equals CTLRFDSTM.DATA_STREAM_ID into ALLCOLUMNS
from entry in ALLCOLUMNS
select entry

edited Apr 22 '13 at 21:33

p.campbell

95,348
63
249
319

answered Mar 01 '13 at 16:24

gxtaillon

980
1
20
32

score 1 · Answer 3 · answered Aug 02 '19 at 13:52

1

Another twist is

OutPutList = (from CTLJCRJOB in CTRL_RUN_JOBs 
              join CTLRFDSTM in CTRL_DATA_STREAMs 
                on CTLJCRJOB.DATA_STREAM_ID equals CTLRFDSTM.DATA_STREAM_ID
              select CTLJCRJOB).ToList();

answered Aug 02 '19 at 13:52

Ajay2707

5,624
6
38
56

score 0 · Answer 4 · edited Aug 19 '19 at 06:00

0

You could use the into clause, but it will not flatten it for you.

from CTLJCRJOB in CTRL_RUN_JOBs 
join CTLRFDSTM in CTRL_DATA_STREAMs 
on CTLJCRJOB.DATA_STREAM_ID equals 
CTLRFDSTM.DATA_STREAM_ID into ALLCOLUMNS
from entry in ALLCOLUMNS
select entry

in this way we can only get CTLJCRJOB columns result, there was no CTLRFDSTM table columns through my test

edited Aug 19 '19 at 06:00

Udo E.

2,381
2
18
28

answered Aug 19 '19 at 02:24

p0ny1nr1v3r

1

Select All columns for all tables in join + linq join

4 Answers4

Linked