Write LINQ subquery C#

Question

I need to write below sql query in linq. i wrote linq query for this with out sub query part. i have no idea about how to write sub query in linq

select * from PART_TYPE Pt
left join 
(select * from PART_AVAILABILITY where DATE_REF = '2013-06-20')pa
on Pt.PART_TYPE_ID = pa.PART_TYPE_ID
where Pt.VEHICLE_ID = 409

How can i do this ?

check this link for how to write subquery in Linq http://stackoverflow.com/questions/418609/how-to-do-subquery-in-linq — Rahul, Jun 26 '13 at 03:28

sgeddes · Answer 1 · 2013-06-26T03:38:31.970

0

This should be pretty close:

var query = from pt in part_type
            join pa in part_availability 
                on new { pt.part_type_id, '2013-06-20' } 
                         equals new { pa.part_type_id, pa.date_ref }
            from x in grp.DefaultIfEmpty()
            select new { part_type = pt, 
                         part_availability = x) };

EDIT: It occurs to me, the date might be an issue -- easy enough to fix that creating a DateTime object and using that instead of the string value.

edited Jun 26 '13 at 03:38

answered Jun 26 '13 at 03:33

sgeddes

62,311
6
61
83

You would nee to specify the parameter name in the first anonymous type on the join, otherwise it wouldn't compile because of the inlined string. – Simon Belanger Jun 26 '13 at 03:51
i need to filter query by `Pt.VEHICLE_ID = 409` as well as `pa.date_ref`. – Bishan Jun 26 '13 at 04:14

Simon Belanger · Answer 2 · 2013-06-26T03:50:05.957

0

Assuming all tables are map to a DbContext context

from pt in context.PART_TYPES
join pa in context.PART_AVAILABILITIES on 
        pt.PART_TYPE_ID equals pa.PART_TYPE_ID
where pt.VEHICLE_ID == 409 && 
      pa.DATE_REF.Any(r => r.DATE_REF == "2013-06-20")
select new { pt, pa };

If there is a FK relationship on PART_TYPE_ID:

from pt in context.PART_TYPES
where pt.VEHICLE_ID == 409 && pt.PART_AVAILABILITY.DATE_REF == "2013-06-20"
select pt;

edited Jun 26 '13 at 03:50

answered Jun 26 '13 at 03:36

Simon Belanger

14,752
3
41
35

got an error as `The name 'r' does not exist in the current context` – Bishan Jun 26 '13 at 04:23

score 0 · Accepted Answer · answered Jun 26 '13 at 05:36

from pt in context.PART_TYPE
join pa in
    (
        (from PART_AVAILABILITY in context.PART_AVAILABILITY
         where
         PART_AVAILABILITY.DATE_REF == dt
         select new
         {
         PART_AVAILABILITY
         }
        )
    ) 
on new { PART_TYPE_ID = pt.PART_TYPE_ID } equals new { PART_TYPE_ID = pa.PART_AVAILABILITY.PART_TYPE_ID } into pa_join
from pa in pa_join.DefaultIfEmpty()
where
    pt.VEHICLE == 409
select new
{
    PART_TYPE = pt,
    PART_AVAILABILITY = pa.PART_AVAILABILITY                                            
};

dt is DateTime object.

Write LINQ subquery C#

3 Answers3