Querying Dataset, nested query
Hi, I wondering if someone could help me out on the syntax of using a nested query to query datatables.
Here is an example provided by LINQ documentation:
DataTable orders= ds.Tables["SalesOrderHeader"];
DataTable orderLines = ds.Tables["SalesOrderDetail"];
var query =from oin ordersQuery
join olin orderLinesQuery
on o.Field<int>("SalesOrderID")equals ol.Field<int>("SalesOrderID")
where o.Field<bool>("OnlineOrderFlag") ==true &&
o.Field<DateTime>("OrderDate").Month == 8
selectnew { SalesOrderID = o.Field<int>("SalesOrderID"),
SalesOrderDetailID = ol.Field<int>("SalesOrderDetailID"),
OrderDate = o.Field<DateTime>("OrderDate"),
ProductID = ol.Field<int>("ProductID") };
I want to create a nested query inside the where clause, how do you do this?
For example, Select * From Orders, OrderLines Where ordersID in ( Select OrdersID in OrderLines Where OrderLines ID > 100)
Can you create nested queries using LINQ? I need an example in order to figure out the syntax. Is there any further documentation on querying dataset/datatables?
[4330 byte] By [
Blast] at [2007-12-23]
Unfortunately, I have found that the TSQL generated from LINQ with joins typically is converted into sub-selects. In part, this is necessary when considering LINQ's deferred execution scheme. However, it can provide performance issues as the application becomes more chatty over the network and query plan optimizations may not perform as well depending on the indexing schemes in place.
One other aspect you may want to consider when doing nested queries is the "into" keyword (see page 26 of the C# 3.0 overview document.)
Jim Wooley
http://devauthority.com/blogs/jwooley
I was just looking through the LINQ Project Overview document and came across another way of performing a nested query. From page 17:
SelectMany is ideal for combining two information sources:
string[] names = { "Burke", "Connor", "Frank", "Everett",
"Albert", "George", "Harris", "David" };
var query = names.SelectMany(n =>
people.Where(p => n.Equals(p.Name))
);
In the lambda expression passed to SelectMany, the nested query applies to a different source, but has in scope the n parameter passed in from the outer source. Thus people.Where is called once for each n, with the resulting sequences flattened by SelectMany for the final output. The result is a sequence of all the people whose name appears in the names array.
Thought I would pass along this information as well.
-Genevieve Orchard
