CODEWITHSUNDEEP
sqllinqlinq-to-sql
segaco
segaco

Reputation: 1236

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

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

Upvotes: 2

Views: 223

Answers (2)

Richard
Richard

Reputation: 109005

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).

Upvotes: 4

Andomar
Andomar

Reputation: 238086

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}

Upvotes: 0

Related Questions