35

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

Sreedhar
  • 29,307
  • 34
  • 118
  • 188

4 Answers4

54

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.

leppie
  • 115,091
  • 17
  • 196
  • 297
  • 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
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
p.campbell
  • 98,673
  • 67
  • 256
  • 322
gxtaillon
  • 1,016
  • 1
  • 19
  • 33
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();
Ajay2707
  • 5,690
  • 6
  • 40
  • 58
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

Udo E.
  • 2,665
  • 2
  • 21
  • 33
p0ny1nr1v3r
  • 1