How can I make a LINQ query with subqueries in the from statement?

Question

Can I make this type of SQL query on LINQ to SQL?

(this query is only a example)

select *
from orders as o
left outer join (select * from ordersdetail where status = 'A') as od
on o.id = od.orderid

What I need is how can I put a subquery inside de "from" statement.

Thanks

Answer 1

Just do the order details condition in the usual way:

from o in orders
join od from orderdetails on o.id = od.orderid
  into details
where details.status == 'A'
select new { Order = o, Details = details}

(NB. Details is a sequence, with each matching details record, LINQ operators like First and FirstOrDefault can be use to extract just one.)

Or use an expression as the data source

from o in orders
join od from orderdetails.Where(d => d.Status == 'A') on o.id = od.orderid
  into details
select new { Order = o, Details = details}

Or even, use another comprehension expression as the source expression:

from o in orders
join od from (from d in orderdetails
              where d.Status == 'A'
              select d)
  on o.id = od.orderid
  into details
select new { Order = o, Details = details}

(Setting you DataContext's Log property allows you to see the SQL so you can compare what SQL is actually generated.)

EDIT: Change to use Group Join (... into var) to get the outer join (rather than an inner join).

Answer 2

You could try this with the Where function:

from o in orders
join od in ordersdetail.Where(x => x.status == 'A') on o.id = od.orderid
select new { Order = o, Details = od}